Need an alternative to replication
-
I have inherited a business unit that is currently using replication. They have regular schema changes and data updates that are pushed out to 5 read only servers. This push is basically on demand on does not occur daily. They are asking for an alternative to replication since the changes only need to be propagated intermittenly. I've thought about log shipping but I don't believe schema changes are included in the tlogs. I am not familiar enough with DTS to know if that would be a reasonable alternative. Any suggestions will be happily accepted.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
I have a database on an MSDE server that can't be moved onto a real server. I'm using a scheduled DTS package to read from the MSDE and then write it to our real SQL Server on a dialy basis.
I scheduled it daily, but you can build a DTS package and run as needed. As for the schema changes, just select the "Drop destination objects first" option.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
MG: If you are not familiar with DTS, a quick way to set this up is to do the following:
Go into EM and select your source database
Select Tools/Data Transformation Services/Export Data
Click Next
Select Source, then Destination server/databases
Under 'Specify table copy or query', select 'Copy objects and data between SQL server databases'
On the next screen, tick 'create destination objects', UNTICK 'include all dependent objects and tick Ddrop destination objects first.
MAKE SURE 'include all dependent objects' IS UNTICKED !!!!! else all objects in your destination could be dropped!
Click Replace existing data
Untick 'Copy all objects', click 'Select Objects' and select your tables
Untick 'Use default options' and click 'Options '- unselect all except for 'Copy Indexes' and 'Copy PRIMARY and FOREIGN keys'
Click NEXT
Untick 'Run Immediately'
Tick 'Save DTS Package', select 'SQL Server'
Tick 'Schedule DTS package for later execution' and change the frequency, etc.
Backup your destination database, and test.
You may need to script the permissions on the destination database and run this script in the DTS package after the data has been copied over.
-
Thanks - DTS certainly sounds like a reasonable alternative. I'll set it up and test.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
Hello,
for an alternative if DTS doesn't work for you because of the myriad of dependancy problems you might encounter, you might use a product to synchronize the databases. DB Ghost claim a 100% success rate when synchronizing two databases as all dependancy problems are accounted for. It can be run from the command line so you could schedule it. Check it out http://www.dbghost.com
-
what about using sp_repladdcolumn and sp_repldropcolumn? This is MS preferred way of making changes to the schema of replicated tables.
-
I looked at BOL for the sp_reladdcolumn and sp_repldropcolumn. They both have a schema_change_script parameter. Would that be the alter table script?
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply