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 |
|
MadhivananFAN
Starting Member
2 Posts |
 Posted - 2009-10-20 : 08:58:34
|
Hi all,First of all I'd really like to appreciate those all specially Mady for his sincere efforts throughout the beginning.This forum has been a benchmark for me to go deeply into SQL. The company which I work for asked me two synchronise two database servers one of which is a test server (replica of the prod server and is used for training purpose). I have to sync those tables which are used by frontend app.I did it simple way.. I created Temp table for respective main tables in test server using DTS I pulled all live data into temp tables and then ran some SPROC to sync those data(based on simple update,delete statements).Now my TL has some questions.He saysIt is not flexible to sync new tables. If we need to sync another new table from live to test, we need to change DTS again.- If we add/modify columns in a table, we will need to change structure of respective temp table, DTS and USP being used again.So, think of a design which can handle these. Also would it possible to create a design in such a way that if process allows us to configure list of tables, primary key and columns to sync from server to server( I mean generic way)If you suggest DDL trigger to create an Audit,it m8t not be accepted.Please suggest me all opssible ways.Regards, |
|
|
SreenivasBora
Posting Yak Master
164 Posts |
Posted - 2009-10-20 : 10:45:49
|
| Why you are pulling the data into Temp database? What you will do If TempDB is full or SQL Server restarted ?My best suggestion is use a separate reporting database (SIMPLE Recovery) to keep for reporting purpose.In order to sync, why can you go for snapshop / Trasactional Replication to sync data nightly basis? ORcreate a snapshot database on same server and use it for reporting users which does not cause any blocking or DLocking issues.With RegardsBSR |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
MadhivananFAN
Starting Member
2 Posts |
Posted - 2009-10-21 : 00:54:59
|
| Sorry for the ambiguity of my question. I didn't mean to copy to the tempDB. I meant UserDB with temp tables.I dnt think replication will handle change of structure of the table.Will it?Thanks in advance for helping... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-21 : 11:39:37
|
| it can handle change of structure too. |
|
|
|
|
|
|
Sync data between two servers