July 31, 2007 at 12:35 pm
One of the items that my client wants is to have data sucked out of the as400 into sql server. The data in the 400 is really bad and does not contain a lot of primary keys and there are other problems so some form of replication is out. One of the ideas is to have a ssis package truncate and then restore the data. While this data restore is happening the user could hit a restored version of the live database. So that we in essence have a "hot" database and a "live" database. In the database can there be a stored procedure that looks at a table and decides which db to hit? For example
if the live database tables are being truncated and reloaded
select * from hot.tables
else
select * from live.tables
Will there be a problem with the having a stored proc run against a database even if it is not selecting against it? Using the example above if the hot database is being restored will the stored procedure fail because the hot database is in restored mode?
TIA
July 31, 2007 at 1:02 pm
In one of my previous lives we had a similar problem. We'd start with 2 databases both containing the same data from the source. The users only had rights to database A. When the source data was ready, we'd truncate all the tables in database B and reload it from the source. Once the data was loaded into B and some QC was completed, we'd kick everyone off for 5 minutes, rename A to 'tempstuff', rename B to A, then rename 'tempstuff' to B, and start all over the next morning. The users always hit the database known to them as A, and the downtime was minimal just to do a rename.
August 1, 2007 at 5:41 am
Synonyms give you an interesting option for this one. You could create database A and B with actual data and a third database C with synonyms to all of the tables in database A. You could then truncate and load database B and change all of your synonyms to hit database B. When the next load comes around, load database A and then switch your synonyms over.
Users would only see database C and the fact that database A or B would actually be the source of the information would not matter. Your changeover time woud be very low in this scenario.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply