December 19, 2013 at 7:04 am
For those who read this,thankyou for sharing in me cursing an application team.
OK
The situation we have a system that keeps tracks of a lot of machines,how they perform,oil checks ect...
The data is being collected in a series of databases called CDC_Country on a server cluster called the Central Landing Zone or CLZ for short.
The CLZ also contains databases for all applications,these only have views on the CDC tables.
A simple system makes it so that once a day the content of the views is copied over to tables on 2 server clusters which are called 24 & 14 (1 is a server cluster where the application devs work on,the other is the test enviroment)
It takes 5-6 hours to copy this over,now they want that the data available twice per day (so it needs to be updated twice per day),both copies must remain available to the users of the 24 & 14 but by default their only allowed to see the lastest data.
So the following is being proposed: instead of pumping over the data,but up snapshot database's on the CLZ (1 for the morning update,1 for the evening) & replace the tables on the 24 & 14 with views to the CLZ
Now when I was told the marvellous concept 2 ideas came to mind
1)Views can't work with a dynamic source
2)Dynamic sql
So I'm at a point where I'm getting a little stir crazy about what to do and if it's even possible and what would the potential solution be.
I thank you in reading this crazied situation (Not to mention that 2 days before the normal go-live of the project this bomb shell was dropped upon us)
December 19, 2013 at 8:05 am
You can't create a view to a "dynamic source." You have to be able define a specific table or set of tables for a view. I'm not sure what you mean by a dynamic source though. If the database and table names are always the same, then the view should work. Otherwise, you're correct. It's time for ad hoc T-SQL. Be careful of SQL Injection.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2013 at 8:12 am
From what you've described so far I think it is possible. I've done something similar in the past, but you do need to build some mechanism to update the source of the views into the snapshot creation and deletion process. One way I've done this is to create a new empty database on the server where you are going to be creating the snapshot and then create synonyms for the tables that your users want to access that point to the database snapshot. You can then create the views and have them use the synonyms as their table source. When you come to create a new database snapshot you will need to update the synonyms to use the new database snapshot. You might also want to remove older snapshots as part of this process.
Another way I've seen this thing of done is to use database mirroring and then create a database snapshot on the mirror. You would need Enterprise Edition for this. You could also do this with transaction log shipping too depending upon how far behind your secondary database could be, but the database would become temporarily unavailable whilst the transaction logs files were restored periodically.
December 20, 2013 at 1:11 am
Grant Fritchey (12/19/2013)
You can't create a view to a "dynamic source." You have to be able define a specific table or set of tables for a view. I'm not sure what you mean by a dynamic source though. If the database and table names are always the same, then the view should work. Otherwise, you're correct. It's time for ad hoc T-SQL. Be careful of SQL Injection.
OK,what they want to do is like this:
CLZ.Source_Database
CLZ.Source_Database_Snapshot_06:00
CLZ.Source_Database_Snapshot_14:00
24/14.Source_Database which only contains views to either of the snapshot databases but by default only shows data of the most recent one
So if its 7AM the default should be CLZ.Source_Database_Snapshot_06:00,but if its between 14:00:00-6:59:59AM it should be CLZ.Source_Database_Snapshot_14:00.
However they must be able if needed be access the data in the CLZ.Source_Database_Snapshot_06:00 in the period
December 20, 2013 at 4:23 am
What about having a script that recreates the view when it creates the snapshot? That would ensure that the snapshot is in place and you'd always have the right view. It's just a view, so updating it should be relatively painless.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 20, 2013 at 6:36 am
indeed,well our team boss is quite angry today because of this 1 application team the go live of this project has been postponed by several weeks if not months
So I've been given the task & I just completed it to make the views,we're dropping the concept of dual snapshots until this team knows what it wants cause this was the 4th complete 180 turn of this team in less then 14 days and normally today was the go live
December 20, 2013 at 6:47 am
Good luck!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply