Is the following even possible -View with dynamic source-

  • Resender

    SSCertifiable

    Points: 7285

    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)

  • Grant Fritchey

    SSC Guru

    Points: 396709

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • tripleAxe

    SSCertifiable

    Points: 5605

    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.

  • Resender

    SSCertifiable

    Points: 7285

    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

  • Grant Fritchey

    SSC Guru

    Points: 396709

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Resender

    SSCertifiable

    Points: 7285

    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

  • Grant Fritchey

    SSC Guru

    Points: 396709

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 7 posts - 1 through 7 (of 7 total)

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