Whether to use replication?

Question

Whether to use replication?

Rob Harlow-408053

SSC Rookie

Points: 36
More actions
September 18, 2007 at 2:36 pm

#181094

Hi,
I currently maintain a fairly busy database running on SQL 2k5 and need to implement an archiving solution. I would like to replicate the structure of the database each day (a lot of development goes on so there are numerous changes), and also the data in a separate database.
I would also like to keep the main 'live' database only containing the most recent 3 months worth of data which will help keep it fast and manageable - is it possible using replication to append all data to the destination DB?
I have looked at a number of programmatical approaches and am actually playing around with writing my own system in .net to do this, but thought I should explore if there is a better and more correct way to do it first.
Thanks in advance
Rob

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

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Replication - by default - really wants to replicate deletes. So, it is often a poor choice for an archiving solution. That being said, you could use transactional replication.

Transactional replication uses stored procedures in the subscriber to do the inserts, updates, ad deletes. If you simply took the guts out of the delete procedures, it would only insert an update and you could delete in your publisher all you want without them ever getting to your destination.

Here is your problem - if you have constant schema changes replication is a really bad choice. It is thrilled to replicate data changes and does it fast and easily. When you make schema changes, you have to change publications and subscriptions. Many schema changes are logged operations, but some are not. If they are not, you will need to make the schema changes in the publisher, subscriber, and in the replication. To add to that, you have to make schema modifications differently - you cannot just right-click on a table and add a column if it is a published article.

My advise on an archiving solution is to step back a bit and make sure your scope is right. Only archive tables that need archiving. Keep the processes as simple as possible. Only archive tables that have a stable schema. Finally, don't try to make something too generic - if you want this to end up an archiving plug-in that will work with any database, buy one. Companies that have built these types of solutions spend millions on development and as smart as you may be, re-inventing the wheel is an expensive process that typically yeilds something egg-shaped.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

best way to do this is to write a script to manually move data to your archive database everyday or on whatever schedule

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

I think you have conflicting goals. If you track daily schema changes how are you going to keep old archive data in anything resembling its original form?

Maybe you should write a program using SMO to script out all database objects, and save the script files. Or even use Visual Studio Team System Database Professional Edition (Data Dude) to manage the schema changes and track them in source control.

How you maintain a data archive in the face of constant development is another question. Maybe you can archive the weekly full backup files to tape or DVD.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

If you wait a few months, SQL 2008 has some system stored procedures designed for change tracking that include both schema and data changes.

You could try pulling the procedures from the CTP and see if they will work with a SQL 2005 database. If you try it, I would be curious as to the results.

Rob Harlow-408053 SSC Rookie Points: 36 More actions · Answer 5

Hi, thanks for the reponses. I have written an app using SMO to handle the schema changes which works pretty well for all DB objects, and am now working on SPs to handle the data transfer. This has thrown up another small issue in that I am using the information schema to build my insert / update statements, however I can't find anyway of determining whether or not a column is computed. In a couple of tables I use computed columns and obviously if I try to insert into them it will error. Am I missing something here?

I realise that this method of archiving whilst there is constant development is not ideal but got to just grin and bear it 🙂 The core of the database structure will not change so the data will always be archived correctly, if not always completely normalised

noeld SSC Guru Points: 96590 More actions · Answer 6

I can't find anyway of determining whether or not a column is computed. In a couple of tables I use computed columns and obviously if I try to insert into them it will error. Am I missing something here?

SELECT COLUMNPROPERTY( OBJECT_ID('YourTableName'),'YourColumnName','IsComputed') AS IsComputed;

* Noel