Hot answers tagged

19

You could use file-system level snapshots, but that is often pretty cumbersome, needs special file systems, and is not always available, especially on aging laptops. ;-) How about you create your base state as a database, and then create a new database from it for your test run, using the CREATE DATABASE ... TEMPLATE functionality. After the test, you ...


14

is because it has to write to cache and then to the TempDb (row version) and then it can return to the caller. No, this is incorrect. It somehow implies that writes in the presence of versioning have higher latency as each write has to touch the disk (for tempdb) which is not true. The write into the tempdb is also a write into 'cache'. The only ...


12

There's a few questions you need to ask first. Are you using SQL Server Enterprise Edition? Mirroring can be done with either Standard or Enterprise, but snapshots can only be done with Enterprise. Are you already paying for licensing on the mirror? There's a lot of fine print, but you basically get the mirror for free - only as long as you're not ...


11

See the fine manual. If my advice conflicts with its' in any way, it's right. A sync isn't a bad idea, unless your copy tool fsync()s each WAL file it writes and the directory it's in before copying the next one. An incomplete last WAL file doesn't matter much; at worst, you just delete it. Pg will generally choke on an incomplete WAL - though there's no ...


10

I need to backup up 10-20 SQL Server dbs used simultaneously by a single enterprise app, while they are online, in such a way as to restore them to a state that is largely synchronized across all dbs What you are looking for is a consistent backup across all your customer databases, you should use FULL backups along with Marked Transactions (emphasis in ...


7

By default, your usage of mysqldump will lock each table as it dumps it and there will not be any consistency among tables. You will also not get any triggers, stored procedures, stored functions, or events backed up, and if any of your view definitions are invalid (referencing nonexistent tables or columns that you may have dropped or renamed since the ...


7

A snapshot database will have an entry in sys.databases.source_database_id, so you can use something like the following to check for a current snapshot. Similarly, you could use the same check to DROP an existing snapshot before creating new. IF NOT EXISTS( SELECT * FROM sys.databases WHERE name = ...


6

The underlying mechanics is pretty much the same. However, in snapshot isolation the versions are potentially kept significantly longer causing a higher strain on the resources overall.


6

If you abort a RESTORE mid-way the database is in an unusable state. This makes sense: Some pages are old, some are new. Stopping the debugger kills the client process causing SQL Server to kill the connection and all associated sessions and requests. To get it working, restart the last restore step that was interrupted. In your case, restore from snapshot ...


6

Note that Character representation of any data will almost always exceed the original size. Depending on type, it can exceed by a large margin. Eg. an int column takes 4 bytes, but the character representation of 1000000 takes ~16 bytes as Unicode, including delimiters. That is a x4 increase right there. Dates, floats, numerics all will usually increase, and ...


6

If with "export/import" you mean the "traditional" exp and imp commands, than I'd say there is a better solution: expdp and impdp are much faster and much more flexible when copying a snapshot of a database (or single schema). If the database is not huge (i.e. Terabytes) then expdp and impdp is the easiest way to achieve this. They are easily scriptable ...


6

Using a database snapshot located on your production OLTP server will, in all likelihood, make performance worse. There are two main reasons: Extra write overhead. Snapshots are copy-on-write, so this will obviously impact performance. A snapshot will share pages from the old database on disk, but it uses its own memory in the buffer pool. If you have a ...


6

From Books On Line: Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers. Using snapshot replication by itself is most appropriate when one or more of the following is true: ...


6

If you are running full backups as well as transaction log backups (and you should if you consider this data important) you could just copy over the backups and transaction log backups to the test system and perform a point in time restore to restore the databases to +- the same time. Depending on whether all databases reside on the same SQL Server machine ...


5

Your scenario is interesting : Small databases approx 1GB in size. Number of databases = 20 Refresh of the data occurs on nightly basis, so the data can be stale by one day. Initial thought reveals that snapshot replication should be OK as you thought, but I would highly recommend to go for backup/restore method - safe, reliable and less overhead of ...


5

One way you could approach this, is to create a "base" test database (including all test data) once. Then before each test suite, create a new database using the "base" database as the template. create database test_db with template = base_test_db; Using this, everything that is in the template will be copied to the new database (including data, ...


5

Database snapshots are used in SQL Server 2005 or later every time you do a DBCC CHECKDB - so they are widely used, even if it is behind the scenes. Perhaps the biggest hindrance to adoption is that Management Studio didn't offer support. It sounds like an ideal use of snapshots in a dev environment just for quick script deployments and quick roll backs. ...


5

MSSnapshot_History has snapshot information for replication. You can join to MSSnapshot_Agents to narrow it down to your specific agent, publication, and job. These tables are found in the [distribution] database on your distribution instance. Here's a basic query to get the last start time of your snapshot agents. select a.name, max(start_time) ...


5

If this is just for development/testing, you can buy a license for Developer Edition (~$50) and use snapshots, since Developer Edition supports all of the features supported in Enterprise Edition. The only difference is licensing - you cannot use Developer Edition in production. You're not going to be able to cook something up that's much simpler or faster ...


5

I'd recommend looking into DB Snapshots. They are (in theory) the fastest way to undo changes unless you're wrapping each change in its own transaction with a rollback at the end (after you've written the results of the test to a database). If you're willing to work it into the framework of your testing, encapsulating each individual test in a rollback ...


4

I would suggest turning on READ_COMMITTED_SNAPSHOT at the database level - that will get rid of a large amount of the concurrency issues you are seeing with the Access front end. Having TIMESTAMP fields in the tables that you are having issues with will help Access, but not with the ASYNC_NETWORK_IO waits. Those waits are showing up from the various ...


4

Yes, that is a good and popular approach to offloading reporting, especially since you already have database mirroring in place. Please see this BOL reference for more specifics: http://msdn.microsoft.com/en-us/library/ms175511.aspx As for when you should create the snapshots, that is completely up to you and your business requirements, depending on how ...


4

If you dig into the internals of how snapshot isolation is implemented in SQL Server, the reason why remote access is not supported becomes clear. When snapshot isolation is enabled, up to 14 bytes of versioning information are appended to each row that is modified. The additional bytes contain the transaction sequence number of the transaction that ...


4

Yes, it is safe. There is a tool http://www.lenzg.net/mylvmbackup/ that uses LVM snapshots to take backups of MySQL. From InnoDB's standpoint a backup copy taken with LVM snapshots looks like as InnoDB has suddenly crashed. When you start MySQL from the backup copy it will start crash recovery. To save some time it's better to do the crash recovery ...


3

My understanding of a rebuild index online operation is that the index will have a snapshot taken and the rebuild is started on the snapshot index. Incorrect. An unfortunate overload of the term 'snapshot'... A snapshot read of the index is used, which means row-versioning see How Online Index Operations Work: A snapshot of the table is defined. ...


3

Mysql 5.6 introduced FLUSH TABLES FOR EXPORT which might be a better option for your use case if you use InnoDB exclusively (apart from the internal mysql database which is always MyISAM): http://dev.mysql.com/doc/refman/5.6/en/flush.html Unfortunately I cannot comment here (yet), but I did quite some research on the answer of Shlomi Noach above. I hope he ...


3

I'd use either of pg_dump or create table foo_log as select * from foo Added: Actually I have one idea to add insert/update trigger for each table that should be shapshoted and write all changes to another table that is partitioned on daily basis. Please tell me if it is a god idea or not? Sounds good too. That works with rules too, e.g.: ...


3

If you are running a mixed workload (OLTP on the table and analysis on the snapshot), you could consider replication and a hot-standby This reduces the impact on the heavily loaded server to 0 - you need to be on 9.0 though...


3

Probably not the answer you are hoping for, but have you considered some lower level of snapshotting - LVM for example?


3

In addition to the impdp/expdb option already mentioned, you could restore/recover the database from your backups to a new location and then enable flashback database to revert the restored database anytime you want. The advantages of this method are that it will make sure your backups are good, verify and improve your recovery procedures, and allow for ...



Only top voted, non community-wiki answers of a minimum length are eligible