February 19, 2004 at 5:59 pm
Hello and thanks in advance for any help. I apologize if this has been hashed out in previous posts, but I queried for a while and didn't exactly find my answer.
I am currently running a production database on SQL 2000 Std and I need a live backup in case my primary goes down. This summer I am going to setup clustering using 2003 and Ent. SQL, but I need something live in the meantime.
From what I've learned so far, I think Transactional Replication would be my best option. My database is still small, but growing fast, the file backup grows about 200megs/month. From my quering and searching, I still have the following questions to answer before I implement this:
Using Trans Rep, in the event of a failure, can the replicated database be used as the primary database? It's no trouble for me to point the application to a different server, but I don't know if it will work the same as the primary. Will all the data be present?
When the primary is brought back online, what will be involved in syncing up the data?
Thanks again for your help.
February 19, 2004 at 7:53 pm
My view on the case is log shipping. Although it is an enterprise option, it can be done manually (script to auto) in other version.
Transaction log replication is more on table focused.
February 19, 2004 at 7:57 pm
I have used transactional replication to create a copy of a server. I don't know if this is the best way. I have heard about log shipping and it could be better. Since we have clustered servers we don't use log shipping.
If you use transactional replication you will have all the data from all tables included in replication. Indexes including the index for Primary keys will go to the subscriber when the snapshot is initialized.
However the primary key do not get placed on the subscriber and columns that are identity columns are not identity columns on the subscriber.
This causes two major problems. In order to run a script to create the primary keys you must first drop all the indexes with names that start with PK_ (at least that is what we did, perhaps someone else knows another way?). The identity column is harder. You'll either have to add them manually (in Enterprise Manager) or design a script to create a new table with the identity column, move all the data to the new table, drop the original table, and then rename the new table to the original name.
You'll probably have to go through the same thing to switch back to the primary.
If others know of a better way please post here. This is the best way I know if you are going to use transactional replication to maintain a standby server.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 20, 2004 at 9:38 am
So if Transactional Replication is table focused, is there a way to replicate the entire database live? I've read that log shipping is not actually live. Thanks
February 20, 2004 at 9:45 am
Merge replication might be what you want then. I just looked at ours and the tables we have merg replication on have the primary key on the publisher and subscriber as well as the identity column. Test that and see how it works for you.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 22, 2004 at 6:36 am
Log shipping is live, or call hot standby. The standby server can be accessed although it is in read only mode. It is whole database level.
Snapshot replication is for one way small database.
Transaction/Merge replication have certain conditions. One case I can think of as database backup is a big fact table with some dimension tables (almost static).
Cluster as you mentioned would not help if database itself get some kind of trouble.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply