July 20, 2009 at 9:13 pm
We want to move from the old clustered environment to a new one. We have about 10 databases, so we will need to copy all these over to the new environment. 8 of the 10 databases are static and data does not change in them, so only two databases have data that changes through the application.
What we are planning to do is to cut over to the new sql server cluster on a weekend, but we also want to have a rollback plan in place; so we want to set up replication between the new sql Server 2005 cluster that we are in the process of building and the existing sql server 2000 cluster.
I have read basic steps on 'how to replicate' but have not seen much that is specifically targeted for replication between two clustered environments. Is there any good write up that addresses this? Also, are there specific things that one has to do when replicating between clustered environments?
I was thinking of doing the following roughly speaking, if something does not look right or there is a better way to do it, please do let me know. Will be much appreciated:
1. Create a cluster with Active/Passive configuration with the new set of servers.
2. Install Sql Server 2005 on this cluster.
3. Create all 10 databases and jobs in the new environment by taking backup files from the existing clustered environment.
4.We have already made changes to stored procedures etc to take care of left and right outer joins, query hints etc so we will just need to recompile all stored procedures after restoring databases in the new environmrnt.
5.Set up transactional replication. Only two databases will need to be replicated. One is under 5gig and the other about 30. Is it ok to create a snapshot or is it preferrable to do it without creating a snapshot in theinterest of time?
6.Create distributor on a different Sql serbver 2005 box.
7. Create publications that will cover all tables in the two databases we need to replicate with trasactional replication.
8.We would not need to replicate the master and msdb databases, I am assuming. Is that right?
Is there anything like where to phsically create the snapshot when dealing with clustered environments or anything else that we need to know before we start creating our new cluster environment. Do we need to install Sql Server 2005 with any specific options because we will be replicating from it to a Sql server 2000 environment?
Hoping to get suggestions from people who may have done this kind of replication or are atleast familiar with it.
Thanks,
vip.
July 21, 2009 at 4:50 am
There is nothing specific that you need to do in a clustered environment that you wouldn't do in a non-clustered environment as far as replication is concerned.
First things first, you'll need to make sure that you include the replication components of SQL Server when you install SQL Server 2005 - sounds obvious I know! IIRC they are included by default, but worth checking.
You will either need to create a snapshot or restore a recent backup of each database on the Subscriber(s).
There is a difference in the 'sync_method' between 2000 and 2005 so you might want to configure replication via SSMS rather than using scripts, but do use the option to script the replication from SSMS so that you can reuse the scripts in future.
You can't replicate system databases so yes, your point 8 is correct.
The snapshot location must be on a disk that is available to the cluster, i.e. not a local disk on a node.
Depending on the authentication methods and security you configure for the various replication agents you may need to create the login used by the Publisher/Distributor on the Subscriber.
An alternative to replication, assuming that your SQL 2000 databases will not be used during this migration phase is to use Log Shipping.
Regards
Lempster
July 21, 2009 at 12:20 pm
Thanks Lempster, for you valuable suggestions. There are just a couple of othere things that I wanted to find out.
1. We want to keep the replication going for about 4 weeks after we have upgraded to Sql 2005 and moved to our new cluster environment. We are not planning to make any stored procedure changes or changes to views etc, so it should be ok to just replicate the two databases where we have data changes and so just publish tables, is that what is fairly standard thing to do.
2. One of the two databases X2 that has all transactional changes is backed up every night and it is restored into another database Z2 that is used for some reporting. I am thinking as both the old and new cluster environments will have a job to backup the database X2 and then restore into a new database Z2 every night, I do not need to replicate X2. Someone suggested that I should replicate it and disable the job doing the restore on the subscriber side (target), but I don't think that is efficient as setting up replication for something that changes only at night when you restore it does not make sense. Also, the jobs running in the two environments have no clashes or dependancies form what I understand about replication. What do you think?
Thanks & Regards
Vip
July 22, 2009 at 3:30 am
Hi Vip,
I'm not sure that I fully understand what you are asking so if my reply does not address the questions you are asking, please shout!
1. Yes, you should be okay to just replicate the tables in the two databases that are likely to change.
2. From what you describe it would not make sense to replicate database X2 to database Z2 as all changes will be captured by the nightly backup/restore operation. I am assuming that the Z2 database will not be recreated every night, i.e. once it exists it will be restored from a backup of X2 (not using the FOR ATTACH option of RESTORE)?
However, if only two tables are going to change then creating your Z2 database from a full backup of X2 once only and then setting up replication would be another option as the majority of your database is not changing - it seems overkill to restore a database of which only a small percentage has changed.
Hope that answers your questions. 🙂
Lempster
July 22, 2009 at 9:48 am
You may run into an issue with Identity columns. We had an issue with this a few years ago when we stopped replication and tried to use the subscriber databases in place of our production databases. I know that Identity columns can be rebuilt, but this is something to keep in mind.
Good luck!
July 22, 2009 at 11:43 am
Hi Lempster,
We have 11 databases and only 2 databases (not tables) that have data changing in them (get hit by the application). One of these two databases X (has about 300 tables) is backed up every night and restored into another database Z, Z database is used by a couple of users to run reports.
We have a restore job that restores Z every night from the backup of X. I am going to be replicating X database, but don't see any point in replicating Z, so I wanted to see if you agree with that.
Thanks & Regards,
Vip.
July 22, 2009 at 12:04 pm
Thanks for letting me know that there may be issues with Identity columns. We have a lot of tables with identity columns, so we will watch for their replicated values. Is there anything specific that you had to do in this regard?
Vip.
July 22, 2009 at 12:19 pm
We ended up getting our Production (the publisher) instance back, so we never had to follow through on the Identity column issue. I just know that we couldn't just start using the subscriber databases for production.
Sorry I don't have more concise info for you, but this was 3 or 4 years ago.
This might help explain what you need to know...
http://www.sqlservercentral.com/articles/SS2K5+-+Replication/2907/
July 22, 2009 at 4:39 pm
Thanks for guiding me to that article. It looks helpful for dealing with identity column replication.
Vip.
July 23, 2009 at 4:01 am
Bharatvip (7/22/2009)
We have 11 databases and only 2 databases (not tables) that have data changing in them (get hit by the application). One of these two databases X (has about 300 tables) is backed up every night and restored into another database Z, Z database is used by a couple of users to run reports.
We have a restore job that restores Z every night from the backup of X. I am going to be replicating X database, but don't see any point in replicating Z, so I wanted to see if you agree with that.
Thanks & Regards,
Vip.
Yes, I agree with that as you are only using database Z for reporting.
July 24, 2009 at 6:05 pm
Just need to confirm one other thing. We are going to create a new cluster environment with sql 2005 having two nodes, let us call it C1. We already have an existing cluster environment with sql 2000, lets call it C2. We are going to replicate from C1 to C2.
Question: It should not matter that the drive names and sizes on C1 and C2 are different. C1 may have drives G,H,I while C2 has drives G,K and L and the disk sizes are different too.
Is it a fair assumption that Sql replication looks at only the names of the databases published and subscribed and the physical strcuture of the publishing and subscribing servers does not come into play at all. I believe there are server replicating tools like double take where the servers need to look the same as far as drives and sizes are concerned.
This should not be the case with Sql Server replication.
Thanks,
Vip.
have that the two cluster environments that we are trying to replicate from and to do not have to be identical as far as drive names and sizes are concerned.
July 27, 2009 at 1:44 am
Correct. The drive letters and sizes can be different on each cluster.
Make sure that if you have a firewall between your two clusters you have set the appropriate firewall rules to allow Publishers/distributor to connect to Subscribers.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply