SQL Server Performance

SQL mirroring question

Discussion in 'ALL SQL SERVER QUESTIONS' started by tarekj, Nov 13, 2012.

  1. tarekj Member

    hi,

    I'm planning to make mirroring to my SQL Server, but I have two questions:

    1- My DB is involved in replication, so my question is the mirrored database will have the same data and when it comes up, the distribution database will have the current replicated records? are there any extra steps should I take to configure the mirror for a replicated database?

    2- When the server is down I will use the mirrored server, but this will affect my application as it calls the main server my name, so I have to change the server name for each client, is there a better idea?

    Thanks
  2. Shehap MVP, MCTS, MCITP SQL Server

    We are usually talking about replication , mirroring , cluster and transaction log shipping as kinds of high availability solutions that can be used for different purposes like DR sites ( Disaster recovery sites) and server availability ..

    But for establishing replication over a mirrored DB, it isn’t recommended as they will contradict together and in addition it will be meaningless as sources of data will be duplicated and in addition replication will not be able to syndicate data from publisher to subscriber (Mirrored DB) coz it is passive so what is the point of configuring a replication ..?

    However for the other aspect of DB mirroring issue , May I understand it like that if a failover case happened for DBs from the 1st server (principal server) to the 2nd server (Mirror server) , then all applications servers connecting the 1st server will get down …If so , it can be addressed by add a registry value at each APP Server to alias the 1st server (principal server) to refer to the IP of the 2nd server (Mirror) as follow:

    1.Copy the attached folder to c:\ of each APP server.

    2. Configure a simple job at the witness server of your mirror solution by an appropriate frequency like 10-30 sec to check the status of principal DB

    if exists( select 1 from sys.databases where name='workshops' and state_desc='offline')

    begin
    exec xp_cmdshell'Regedit.EXE /D /S C:\Mirrorconfig\SQLLive1principal.reg /S C:\Mirrorconfig\SQLLive1mirror.reg'
    end

    ELSE

    begin
    exec xp_cmdshell'Regedit.EXE /D /S C:\Mirrorconfig\SQLLive1mirror.reg /S C:\Mirrorconfig\SQLLive1principal.reg'
    end

    3. But take with consider editing the values exists per these 2 files SQLLive1principal.reg and SQLLive1mirror.reg according to your environment as follow:

    · SQLPrincipal\SQLLive1 = your prinicpal servername
    · 10.0.0.13,1433=IP and TCP port of your prinicpal server
    · 10.0.0.15,1433 = IP and TCP port of your mirror server

    Kindly try it and let me know your feedback

    Attached Files:

  3. tarekj Member

    I want to make the mirroring for one of the replicated servers not making replication to a mirrored server.

    I have SERVER1 which is publisher to a server in different city SERVER2, I want to make a mirror for SERVER1 in different server, say SERVER1-A, for DR as you say, is it possible?
  4. Shehap MVP, MCTS, MCITP SQL Server

    Sure It is applicable to configure publisher DB in a Mirroring solution but you have to do 2 things mainly:

    · Configure replication agents for failover.

    · Configure the mirror Server to use the same Distributor server as the principal.

    Kindly try it and let me know your feedback

Share This Page