Replicate ALL databases in SQL SERVER 2005

Question

We have a SQL SERVER 2005 with 700 databases running.

Our idea is to replicate all the databases on the server with the SQL SERVER Replication Services (http://msdn.microsoft.com/en-gb/library/ms151314(SQL.90).aspx) to get two identical data servers running at the same time.

We've achieved to publicate a single database and subscribe this database in other SQL SERVER but our problem resides in that we can't publicate all the databases at the same time in a single publication (we can register any database in a single publication and subscribe this publication in the other server manually but we don't want to do every time which a database is created or eliminated).

How can i publicate and subscribe all the databases automatically between the two servers?

Answer 1

You can setup replication using code, this was done as a part of a project I was on years ago so I can't remember the detail. But basicly we setup the publisher and subscriber using code from C#, I think for the most part it was calling SP's in the server to do it.

So whatever code you are using to setup the database, you should then be able to add in code after that to setup the replication. But it will need to be done by creating your own stored procedures to basicly stitch together what you need to do.

Answer 2

There's no way to automatically setup replication when you create new databases. Or to have replication remove the replication when you drop the databases.

To have something automatic you'll need something that handles the replication at the disk level.