Tag Info

Hot answers tagged

16

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 ...


12

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 ...


11

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 ...


9

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 ...


7

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 ...


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

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

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 ...


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

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

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 ...


5

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 ...


5

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 ...


5

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.


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

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, 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

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

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 ...


4

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, ...


3

@RemusRusanu has best explained the native and character mode for bcp. +1 for that from me. To deal with your situation, you can take either of the 2 different approaches. The first one is to Initialize the subscription from a backup. Refer to How to: Initialize a Transactional Subscriber from a Backup (Replication Transact-SQL Programming). This is self ...


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 ...


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

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

When you set the database to single user, it allows a single user into the database. If there's someone else trying to get into the database, and you don't have a connection to the database someone else could get into the database.


3

Yes from that link it supports it. A Database Snapshot is a read only "frozen in time" (when the snapshot was taken) view of data. This is a tool useful in some recovery, reporting and rollback preparation scenarios. The isolation levels you are asking about are all covered by the line talking about Snapshot Isolation Level (Row-level Versioning). I would ...


2

SINGLE_USER will not give you exclusive access per say. It will only allow one connection to the DB, and that could very well be another of your own session. I have seen another scenario where the SQL agent connects to the DB and that takes up the single_user connection. When I want to prevent user connections to a DB that is being restored over (with ...


2

Probably not the issue, but double check this: On the publication, go to properties Go to Subscription Options make sure Replicate schema changes is set to true.



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