SQL Server Performance Forum – Threads Archive
Dr Plan for Publisher and Distributior
Hi Group, We have sql server 2005 server which is distributor and sql server which is publisher (Also in sql server 2005). We are taking backups of all databases on both these servers including system databases. I have also scripted out logins on both servers. We want to make a efficient plan for DR so that we dont lose any setting or data if server goes down. Also we are thinking of opting ways to bring up server with minimal down time. Now we have more than 100 publications and 400 articles. If at any time we lose Distributor or Publisher server, can we come back to original state by just restoring backups. I am concerned more on replication jobs and replication setup. Will all the setting be restored if we restore system databases?? Also how can I make sure that all linked servers, credentials, Tsql end points settings etc are all restored back to its original settings. Will restoring system databases bring back all server and replication setting?? Is there anything else I can do proactively like scripting all logins etc to avoid losing any settings. whats the best approach to bring up server in minimal time…you can take scripts of repliation as well bu right clicking the publishers and subscribers. If you are taking backups of all db (including system dbs ) then you don’t have to worry —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/
You can also take help of this KBAhttp://support.microsoft.com/kb/886839 in this case for a strategy on the replication setup you have.
For a plan template:http://www.sql-server-performance.com/disaster_recover_examples.asp We follow:
Procedure
The backup location is important and do not allow any access to that server. Indentifiable Risks:
hardware failure.
power failure.
flooding. Resoluton:
implent recovery and get replacement. By all means you have test the backup and restore in recovery aspect and do not assume by compiling the backups itself will have the DR capability. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
We are actually planning to upgrade sql server 2005 with SP1 for all servers. This include 3 servers which are part of single cluster. Within this cluster we have 2 production server and 1 common failover node for both production servers. One of this production server is publisher and other production server is Subscriber. We have another server which is outside this cluster and acts as a distributor.
—————- I am thinking is there any risks involved if we upgrade to SQL server 2005 SP1 and we are also planning to include a hotfix " http://support.microsoft.com/kb/918222 – Cumulative hotfix package (build 2153) for SQL Server 2005)"…. This is based on recomendation from microsoft I will take backup of all databases including system databases, but is there any risk that this might affect replication in any way… or affect any operation. Will installing SP1 affect normal SQL server processes and connection or stop replication. Do I need to resetup any setting after installing SP1 or is it going to be un affected….
Make sure you upgrade in the following order…
1. Distributor
2. Publisher
3. Subscriber Why don’t you apply SP2 instead of SP1+Pach? MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
I would also suggest to have a read thru on SP2 readme.html for the changes in replication & cluster for more information. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
We are in process of applying SP2 later as after SP2 there has been around 10 hotfixes on top. This is still in testing stage at our end so want to proceed with SP1 at this stage. Can you please outline if there is any risk to cluster or publication, distributor or subscriber if we update SQL 2005 to sp1. Also can you please update me how can I upgrade cluster environment for upgrading to SP1. if I upgrade 1 node, will that also upgrade failover node..??
I have applied one of the cluster environment with sp1+patch without any issues..
Make sure you read ReadMe file before applying it…and make sure you follow the order while applying replication servers…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Thanks Mohammed, Also can you please update me how can I upgrade cluster environment for upgrading to SP1. if I upgrade 1 node, will that also upgrade failover node..??
When upgrade it will also upgrade failover node but patch you have to run on both servers… Read the readme file which has good info..
Failover Cluster Installation
The following information applies only to SQL Server 2005 components that are part of a failover cluster. To install the service pack on a failover cluster:
If any resources have been added that have dependencies on SQL Server resources, those dependencies must either be removed or taken offline before you install SP1. If you do not remove the dependencies, the installation of SP1 will take those resources offline. Note:
When a clustered resource is taken offline, all dependent resources are also taken offline by the cluster service. Run the SP1 executable package file from the node (active node) that owns the group containing the virtual server that you plan to upgrade. This installs the service pack files on all nodes in the failover cluster. You cannot install SP1 on any other nodes (passive nodes) in the cluster. In the Feature Selection page, select the virtual server that you plan to upgrade. Setup will prompt you for login credentials used to connect to other nodes in the cluster. Note:
Keep all nodes of the cluster online during Setup. This ensures that the upgrade is applied to each cluster node. If you removed dependencies or took resources offline in Step 1, restore the dependencies or bring the resources online. Note:
Setup might require restarting of the failover cluster nodes. This restart replaces the files that were in use during Setup. For an example of how to install SP1 in unattended mode with remote authentication for failover clusters, see Section 4.3 Unattended Installations. Rebuild a SQL Server 2005 SP1 Failover Cluster Node
If you must rebuild a node in the failover cluster after SP1 has been applied, perform the following steps To rebuild a node in the failover cluster:
Rebuild the node in the failover cluster. For more information about rebuilding a node, see "How to: Recover from Failover Cluster Failure in Scenario 1" in SQL Server 2005 Books Online. Run the original SQL Server 2005 Setup program to restore the node to the failover cluster. Run SP1 Setup on the active cluster node. 3.3 Restart Services and Applications
When Setup completes, it may prompt you to restart the computer. Section 3.1.5 Stop Services and Applications provides guidelines on when a restart is required. After the system restarts, or after Setup completes without requesting a restart, use the Services application in Control Panel to make sure that any services you stopped before applying SP1 are now running. This includes services like DTC and the Microsoft Search services, or instance-specific equivalents. Restart the applications you closed before running the SP1 Setup program. You may wish to make another backup of the upgraded master, msdb, and model databases immediately after successful installation. 3.4 Setup Issues
This section details Setup issues for SP1. 3.4.1 Considerations for Upgrading Servers in a Replication Topology
When upgrading instances of SQL Server 2005 in a merge replication topology or in a transactional replication topology with updating Subscribers, you must upgrade the instances in the following order: Distributor
Publisher
Subscribers
http://support.microsoft.com/kb/917410
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
I agree with Mohammed that they haven’t had any issues with the upgrade, but here the environment is different what they had as compared to your (say). So the best option is to test the implications for SP2 on Cluster and refer through README documentation as well to get an understanding on the implications.
quote:Originally posted by viksar
We are in process of applying SP2 later as after SP2 there has been around 10 hotfixes on top. This is still in testing stage at our end so want to proceed with SP1 at this stage.
Can you please outline if there is any risk to cluster or publication, distributor or subscriber if we update SQL 2005 to sp1.
Also can you please update me how can I upgrade cluster environment for upgrading to SP1. if I upgrade 1 node, will that also upgrade failover node..??
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thanks a lot Satya and Mohammed. I just need to check one thing, As updated above, we have 3 nodes in cluster with 2 nodes as production servers and 1 node as common failover for both the servers. Now when I’ll update 1 production server node, it will also update failover node, which is common failover node for 2nd server as well. What will happen if I will upgrade 2nd server node. will it through up error message as failover node is already upgraded before??
No, it will not thourgh any error like that…
But better to failover to the over nodes and check the version to make sure all nodes are upgraded correctly. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Thanks Mohammed for your support.
Fyi, when upgrading SQL instances within a replication with updating Subscribers, you must upgrade the instances in the following order: Distributor
Publisher
Subscribers
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>