data synch
-
I have three databases DEV, TST and STG.
I have the same schema in all of these.
I have a nightly process to populate DEV with data. I then need this same data in TST and STG on a daily basis. I was going to create a DTS package to drop all foreign keys, pump data in, put foreign keys back. But if there is a data issue then a foreign key may fail, thus failing the DTS package.
Whats the best solution for me?
-
Why not do a backup of your DEV and restore on TST and STG. This would be much faster then DTS and assure the absolute equivalence of the databases including the data.
-
i would have to so this every morning.
not a good option.
-
If your nightly process corerctly loads your DEV environment there should be no issue loading the others.
Instead of dropping your FKs why don't you load your DEV and then load the others (From your DEV machine) in the proper order to avoid FK issues?
OR (I know this is for PK but may work for FK) set the FK WITH NOCHECK option...
AJ Ahrens
Good Hunting!AJ Ahrens
webmaster@kritter.net -
no check is not a good thing to use. Optimizer can use the default constraint in its execution decisions but if data is inputted with no check then it cannot.
DTS seems like the best choice.
-
quote:
Why not do a backup of your DEV and restore on TST and STG. This would be much faster then DTS and assure the absolute equivalence of the databases including the data.quote:
i would have to so this every morning.not a good option.
Easily accomplished with DTS workflow.
1. ActiveXScript task to create unique filename.
2. ExecuteSQL Task to issue BACKUP DATABASE to filename created in step 1.
3. Optional ActiveXScript task to copy backup file to other server.
4. ExecuteSQL task to issue RESTORE DATABASE from filename created in step 1.
I do have a few questions,
a) what happens to the objects that are created in DEV but aren't ready for production?
b) how do you maintain the privacy/security of the data your developers are using?
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
I agree with Michael Romm about using backup restore for this. If your restores complete fast enough. This can all be automated in a nightly job using TSQL statements. I do this with production database hat needs a copy of the previous day’s data available at all times.
BACKUP DATABASE DEV
TO DISK = 'C:\MSSQL\BACKUP\export.bak'
WITH INIT
RESTORE DATABASE TST
FROM DISK = 'C:\MSSQL\BACKUP\export.bak'
WITH REPLACE, RECOVERY,
MOVE 'DEV_Data' TO 'C:\MSSQL\Data\TST_Data.mdf',
MOVE 'DEV_Log' TO 'C:\MSSQL\Data\TST_Log.LDF'
ALTER DATABASE dblaCopy MODIFY FILE (NAME ='DEV_Data', NEWNAME ='TST_Data');
ALTER DATABASE dblaCopy MODIFY FILE (NAME ='DEV_Log', NEWNAME ='TST_Log');
If TST and STG on on a different server than DEV, you will also have to fix up the logins.
-
I am in total agreement with Phil, If u dont wish to use a DTS then u may simply use jobs to take backup and restore and use a NT batch file to move the file to a different server or maybe u can use the UNC path and take backup on the other server with a shared drive. Use any of the options that suit u.
Cheers!
Arvind
Arvind -
If you sync all databases once, then you can use log shipping if your trxs are loged. if your trxs are not loged, you can look into a thrid party tool that does data sync (cumlative)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply