Tag Archives: Mirroring

Automating your snapshots

A little while ago I was talking to someone about possibilities of reporting from SQL Server to avoid impacting the production server and the very first thing that sprang to mind was for them to use snapshotting from a mirror. They had already considered this option but it was not really viable because an almost real-time reporting solution was required. It wasn’t until later that I wondered whether I had not really been precise enough in my explanation.

Since creating a snapshot can be pretty much instantaneous due to the way in which their mechanics work, as long as “real-time” means the time at any one instant, then there is no reason why snapshotting cannot be the solution for you. The only problem then that remains is how you are going to manage your snapshots, namely creation and destruction. Since the initial overhead of setting up (and then removing) a snapshot is relatively low, it makes perfect sense in my opinion to generate snapshots for reporting and then tear them down post report creation.

When you are creating a snapshot, since you must specify all datafile locations, auto-generation of them may at first seem a little more complicated than it really is. In truth, it is not particularly difficult and I have written some fairly useful code which can create a snapshot for you easily. Obviously you can customise the naming conventions of the snapshotted database to your requirements. Your reporting solution then could call a procedure to create the report snapshot (using the code below) on your mirrored database. All that would remain is to tear the snapshot down once the report has completed. I shall leave it to your imagination how you want to tear down your snapshots since depending upon your naming convention your method might differ slightly, but querying snapshots in existence is very simple as follows :-

SELECT d.name,f.physical_name FROM sys.databases d JOIN
sys.master_files f ON d.database_id=f.database_id
WHERE d.source_database_id=

The procedure does however have an output parameter that returns the created snapshot name and you can use it in your reporting TSQL code to tear the snapshot down post report generation. Its up to you, but it is there if you want it!

We then move on to the main code itself to auto-generate your database mirror snapshot. The first thing you need to do is to decide which database you are going to deploy this stored procedure to. I personally prefer to have a dedicated database administration database that has all the custom written procedures, functions and the like housed in there. Once you have changed context into the database of choice your then need to execute the usp_createsnapshot creation script which can be found here to deploy.

Once this procedure is deployed, all you need to do from now on in order to create a quick snapshot on any of your databases or mirrors located on this server is simply call the proc and pass in the name of the database or mirrored database and execute and hey presto a database snapshot has been created for you 🙂
Below I have entered a few examples of how the procedure works and it’s error handling and feedback. The final example is valid input.

EXEC dbo.usp_createsnapshot @help=1
GO
EXEC dbo.usp_createsnapshot master
GO
EXEC dbo.usp_createsnapshot invaliddbname
GO
DECLARE @ss sysname
EXEC dbo.usp_createsnapshot msdb,@ss OUT
PRINT 'Snapshot ' + @ss + ' created.'

Within this following screenshot are the results, and in particular the output within the red box demonstrates the correct use of the stored procedure.

Well that’s the end of this post ladies and gentlemen. I hope you find this stored procedure as useful as I do, and remember ….Happy Reporting!

Back to front mirror

SQL Server database mirroring was obviously a great feature first introduced in SQL 2005 and will improve even further in the next big release (Denali) but quite surprising to me is how drastically the order in which the transactions are committed differ between both modes of operation.

To recap, there are two types of mirroring (High Performance and High Safety) so lets look at exactly how these two types work.

High Performance

In order for database mirroring to be achieved, high performance sends transactions asynchronously and this is the order of events.

  1. The client application sends a transaction to the principal server which then writes it to the transaction log and commits it.
  2. The principal sends an acknowledgement back to the client application.
  3. The principal then sends the transaction to the mirror who writes an entry to the transaction log.
  4. The mirror then contacts the principal to acknowledge the transaction and then commits it.

Note that since these first two steps are identical on a database with or without mirroring, you can appreciate why this operation is fast and has little impact to the speed of transactions on the principal.

High Safety

  1. The client application sends a transaction to the principal server and then writes it to the transaction log.
  2. The principal then sends the transaction to the mirror who writes and entry to the transaction log.
  3. The mirror then contacts the principal to acknowledge the transaction, the principal subsequently sends an acknowledgement back to the client application.
  4. The principal commits the transaction and then the mirror commits the transaction.

I think the biggest surprise to me really is that in High Safety mode, because the transaction on the primary is committed only after the mirror initiated handshake you are actually reducing the probability of a successful commit for any given transaction (due to the dependency on the mirror). So although in High Safety the principal and mirror would be transactionally in sync, I believe this is another reason why High Performance should be preferred in nearly all situations.

I should also add that for the different modes of operation (especially High Safety), depending upon the information source you refer to, the exact mechanism and in particular (for High Safety step 4) the order each server’s transaction is committed is a little ambiguous to say the least. The following url “Synchronous Database Mirroring (High-Safety Mode)” from Books Online also leaves a little too much open to interpretation.

For further information on Database Mirroring best practices, please refer to this rather good white paper “Database Mirroring Best Practices and Performance Considerations” from the SQL CAT team.