Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Gvarol
Starting Member
3 Posts |
 Posted - 2009-06-22 : 16:13:12
|
| Our production database consists of partitioned tables which are constantly being modified by partition switching. Our users are running adhoc queries which sometimes may make couple hours to complete. We created a snapshot of the database and through views we pointed the users to the tables in the snapshot, which solves the blocking issues we get in the production database. We would like to be able to generate another snapshot of the database and point the users to the new one every 12 hours or so. I thought I could manage that by using alter view statements to the views. Unfortunately the alter view statement has to wait till it is not being used anymore to complete, which in our case might take hours at some point, not to mentioned I need to alter about 30 views to point all data useage to the snapshot. I was hoping that I could alter the view even though it was used so that next time the view is used it will point to the right data. I tried using the functionality of the synonyms and I get similar results of not being able to drop and create it as it's being used.Is there any way I can point our ad-hoc queries to a different database or tables from the back end every so often schedule?Our sql server version is 2008 enterprise |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-22 : 16:21:31
|
No.See Books Online about CREATE SYNONYM and what types of objects that are supported.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-06-22 : 16:33:22
|
| Maybe using linked servers?________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
Gvarol
Starting Member
3 Posts |
Posted - 2009-06-22 : 16:44:27
|
| CREATE SYNONYM works for objects stored under a schem (tables, views, functions, procedures). But if an object in the cache is referencing the synonym it cannot be dropped and created (there is no alter option for synonym). In our case the adhoc queries might be running for hours and we need to switch the new adhoc queries to the new snapshot right away but we cannot switch new adhoc queries till the old ones complete. |
|
|
|
|
|
|
Is Database alias possible?