One of Our production database is used by lots of Reporting users. Mirroring is enabled on this database. But no one is using the high performance mirroring/reporting server.
I thought of moving Reporting users to make use of Mirroring server as a Reporting platform by taking Database Snapshots and making them to use Snapshot database.
If you are new to database snapshots, here is the link to learn
How snapshot works
http://msdn.microsoft.com/en-us/library/ms187054.aspx
Pros and Cons of Database Snapshots:
http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/
Creating Snapsots:
Here is sample script to create database snapsots in mirrored server.
CREATE DATABASE ssAdventureWorks_dbss2230 ON
( NAME = AdventureWorks_Data, FILENAME =
‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_data_2230.ss’ )
AS SNAPSHOT OF AdventureWorks;
GO
Database health:
You can create snapshots only when Mirroring database is in Synchronized state.
here is the DMV query to check Mirroring database status.
select DB_NAME(database_id),mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc
from sys.database_mirroring
where DB_NAME(database_id) =’DB_Name’
Login Issue & Fix:
Issue: Once you create database snapshot, users can not connect to mirroring server immediately because scope of Mirroring is database level. Meaning when new login is created in principal server, that is not propogated to Mirroring server. Only database level credentials are updated in Mirroring server.
Fix: scripts of principal server login’s (with password) can be generated by using sp_help_revlogin.
Here is two good links on this.
http://support.microsoft.com/kb/246133
http://social.msdn.microsoft.com/Forums/pl-PL/sqldatabasemirroring/thread/2fcd230f-dab8-4af7-b9b3-a7dd1b2924a4
Time taken to create snapshot of 100 GB size database:
In my case, my job to refresh snapshot takes 11 seconds to drop and recreate database size 100 GB.
Cross Database reference issue:
View in a database snapshot has cross db reference (which is also in restoring state) will fail with below error.
Database 'Another_DB' cannot be opened. It is in the middle of a restore.
Update: We have to keep all tables available in single snapshot database. In my case, cross db references was less. It works.
Data mismatches between Principal Database and Database Snapshots in Mirroring Server:
Data in principal server can have more updates even after taking database snapshots. Initially there will be some queries from reporting users
on data mismatch between principal and snapshots. We have to educate them that Snapshot db will reflect data of one particular point of time
where in production there can be some updates even after taking database snapshots. We may have to refresh snapshots based on user needs.
Database Snapshot naming conventions and Synonyms:
Its suggested that a database snapshot name should have date & time and with .snap extention.
For example:
salesDb_21Sep2011_1130.snap
In this case, Reporting user has to change the database reference everytime new snapshot is created. this will not be an feasible idea if
Snapshot name is refered in front-end or in ETL tool.
Workaround: We can create a database only with synonyms. Synonyms has to point snapshots. Whenever snapshot is refreshed,
actual database synonyms has to refer new database snapshots.
here is a good link on how to make use of synonym for this case.
