Snapshot database

Question

Snapshot database

Loner

SSC-Insane

Points: 21279
More actions
February 7, 2008 at 12:45 pm

#119897

Can you create the snapshot database on the same server where the original database is?
If you want the users/other developers to query using the snapshot database, do you have to tell them the name? What if they already create a query against the original database, then it defeats the purpose of creating a snapshot ? Is there anyway to steer the users to use the snapshot database without they even know it is the snapshot? Am I make sense or clear as mud? 😉

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

Loner (2/7/2008)
Can you create the snapshot database on the same server where the original database is?
If you want the users/other developers to query using the snapshot database, do you have to tell them the name? What if they already create a query against the original database, then it defeats the purpose of creating a snapshot ? Is there anyway to steer the users to use the snapshot database without they even know it is the snapshot? Am I make sense or clear as mud? 😉

You can only create a database snapshot on the same server where the original database is. You can not move this snapshot to any other server.

The same users that were in the database at time of snapshot, would have access to the snapshot after it is created. If you wanted a new set of people to have access to the snapshot after it has been created, I don't believe you can give out any permissions besides sa as the database is in a read only state.

They would also need to know the name of the snapshot to query it appropriately.

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 2

Another option which is very usefull for reporting (i.e. SSRS, etc.) is to use dynamic SQL to manage the snapshot as a part of your ETL process and link to the snapshot using synonyms in a reporting db. I'm actually working on an article for SSC on this topic. I've employed this method for a large data wharehouse and have had great success.

Tommy

Follow @sqlscribe

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

Synonym eh? Not a bad idea, hadn't thought about that ... We're getting ready to implement mirroring on our Warehouse for DR and reporting. I was planning to snapshot the mirror once a day to provide a reporting environment. I created a procedure to simply drop the current snapshot and create a new one with the same name.

Loner SSC-Insane Points: 21279 More actions · Answer 4

Can you explain a little more how to use dynamic SQL to manage the snapshot and use synonyms to do that? I am new to SQL Server 2005.

Thanks a lot. When you finish the article, please share 🙂

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 5

As soon as I finish up the article, I'll update this thread :). In the interim, if you guys want a copy of the procedure, e-mail me at tbollhofer2@gmail.com and I'll be more than happy to share 😀

Using AdventureWorksDW as an example. I've created a new empty database called AdventureWorks_RO. I then created a procedure using dynamic SQL to take a snapshot of the AdventureWorksDW database and create synonyms in the AdventureWorks_RO database to all of the tables, views, stored procedures, etc. within the newly generated snapshot and drop the previous snapshot. It's a pretty straightforward concept really, perfect reporting environments. You can incorporate this at the end of your ETL process and create your datasources in SSRS/SSAS against the RO database.

Tommy

Follow @sqlscribe

Adam Bean One Orange Chip Points: 26631 More actions · Answer 6

So I added a small step into my snapshot procedure, and am I missing something here? Can you not query a synonym for a snapshot database name?

CREATE SYNONYM synname FOR snapshotdbname

I can query the snapshot just fine. I can query a synonym created for a regular database just fine. But when I try to query a synonym for a snapshot database, it says the snapshot name is an invalid object?

Thanks

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 7

Create synonyms for the objects (i.e. tables, procedures, views) within the snapshot.

CREATE SYNONYM Test FOR SnapShotName.SchemaName.ObjectName

Tommy

Follow @sqlscribe

Adam Bean One Orange Chip Points: 26631 More actions · Answer 8

I just want to create a synonym name for the database name ... which works on the master database.

But it looks like it doesn't work on any other database?

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 9

It doesn't - please also note that you can't create synonyms for synonyms 🙂 Another thing to be aware of - dropping a database snapshot clears the plan cache. Keeping too many will slow down your ETL process. You will want to find the happy medium in your environment 🙂

Tommy

Follow @sqlscribe