I'm setting up Transactional Replication on several database and in doing so I've come across some issues that have raised several questions.
Basically, here's what we're wanting to do: We have a new SQL Server (2000) that we want to use as a "backup" server. We would like to have all of our other SQL Servers replicate (transactional) their databases over to it, and then have the "backup" server handle most of the backups on the databases and transaction logs frequently throughout the day. The idea behind it is to allow the "real" databases to just interact with their programs, while allowing the "backup" server to handle most of the backups.
The first database I tried this with seemed to work nicely, I had no problems setting up the replication and everything appears to be replicating correctly. But when I got to looking a little closer at the replicated database, I discovered that there are only 210 tables in it and there are 284 in the real database. I found out that some of the tables were not published because they didn't have a primary key specified (why, I don't know).
Is there any way to make the Snapshot Generator push a copy of the ENTIRE database for replication? Should I create my replicated database from a backup of the original instead of using a Snapshot, and even then, will all tables replicate properly? Basically, the way I see it is, if we're not replicating/backing up the entire database, then all of the backups are useless.
Can anyone tell me if there's something I'm missing on replication or a better way to set this up? Any suggestions on how to accomplish what we're trying to do would be appreciated.
Thanks,
Eric
Basically, here's what we're wanting to do: We have a new SQL Server (2000) that we want to use as a "backup" server. We would like to have all of our other SQL Servers replicate (transactional) their databases over to it, and then have the "backup" server handle most of the backups on the databases and transaction logs frequently throughout the day. The idea behind it is to allow the "real" databases to just interact with their programs, while allowing the "backup" server to handle most of the backups.
The first database I tried this with seemed to work nicely, I had no problems setting up the replication and everything appears to be replicating correctly. But when I got to looking a little closer at the replicated database, I discovered that there are only 210 tables in it and there are 284 in the real database. I found out that some of the tables were not published because they didn't have a primary key specified (why, I don't know).
Is there any way to make the Snapshot Generator push a copy of the ENTIRE database for replication? Should I create my replicated database from a backup of the original instead of using a Snapshot, and even then, will all tables replicate properly? Basically, the way I see it is, if we're not replicating/backing up the entire database, then all of the backups are useless.
Can anyone tell me if there's something I'm missing on replication or a better way to set this up? Any suggestions on how to accomplish what we're trying to do would be appreciated.
Thanks,
Eric