How To: Migrate to AlwaysOn AlwaysOn from Prior Deployments Combining Database Mirroring and Log Shipping – Part 1

Published Mar 23 2019 12:57 PM 396 Views
Microsoft
First published on MSDN on Oct 16, 2012

Writer: Cephas Lin (Microsoft)


Contributors: Oleg Bulanyy (Microsoft), Jimmy May (Microsoft)


This blog series is a follow-up to my whitepaper Migration Guide: Migrating to AlwaysOn Availability Groups from Prior Deployments Combining Dat... . In this series, I will walk you through the following end-to-end migration scenario inside a test environment:



  • From a SQL Server 2008 R2 solution: Database mirroring for high availability and log shipping for disaster recovery

  • To a SQL Server 2012 solution: Availability Group for both high availability and disaster recovery


Before I go further, l want to refer you to a whitepaper AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Usi... . This is a great architectural guide for anyone who is interested about the end-to-end deployment of the AlwaysOn scenario I'm discussing in this blog.


How to Use This Blog Series


This is a disclaimer similar to the one I have in How To: Migrate from Failover Cluster Instances and Database Mirroring to SQL Server AlwaysOn ... . My walkthrough steps are to help people get started on implementing a end-to-end migration scenario in a test environment. I have already published prescriptive guidance on the migration scenario in Migration Guide: Migrating to AlwaysOn Availability Groups from Prior Deployments Combining Dat... . If you are a database architect seeking guidance on migrating your production system to SQL Server AlwaysOn, I advise you to look there first. If you are a database administrator who needs help to get started on migrating your company's database system to SQL Server AlwaysOn, then my blog is for you. To expedite the testing phase of you migration scenario, I recommend that you walk through the entirety of this blog series, which can help you recognize and avoid many issues that can trip you up along the way. Once you have successfully completed the walkthrough in your test environment, you will know what success looks for a generalized scenario. The next natural step continue the testing phase with the specification of your SQL Server deployment.


I cannot emphasize enough: DO NOT apply these migration steps blindly to your production system. To minimize the risks of your actual migration to SQL Server AlwaysOn, test these steps thoroughly against every aspect of your SQL Server deployment (I repeat, in a test environment ) and incorporate all the necessary tweaks to accommodate your specific scenario.


Migration Walkthrough Overview


You will walk through the migration procedures as covered in Migration Guide: Migrating to AlwaysOn Availability Groups from Prior Deployments Combining Dat... .


The following table outlines the starting point and the end point of the walkthrough.















Start



End




  • PRIMARY1, PRIMARY2, and WITNESS in Subnet1, implementing database mirroring for local high availability

  • REMOTE1 in Subnet2, shipping logs from PRIMARY1 and PRIMARY2 for remote disaster recovery




  • One availability group on PRIMARY1, PRIMARY2, and REMOTE1

  • PRIMARY1 as primary replica

  • PRIMARY2 as synchronous secondary replica

  • REMOTE1 as asynchronous secondary replica




The four stages of migration as covered in the whitepaper are as follows:



  1. Online upgrade of the log shipping secondary database server (REMOTE1).

  2. Online upgrade of the mirror database server (PRIMARY2).

  3. Online upgrade of the principal database server (PRIMARY1).

  4. Configuration of an availability group.


If you build the base configuration I prescribe here, you will find a clear path to success in the migration procedure. You can instead use your existing HADR solution as the base configuration, but just realize that I cannot guarantee that my migration steps will work as-is with your HADR solution. In all likelihood, you will need to tweaks as you follow along. Make sure that you test the migration steps thoroughly before attempting the same steps on your production environment.


As client connectivity is an important part of the migration scenario, you will simulate a client workload on the database system to test the HADR capabilities of the system before, during, and after the migration process. In addition, the client workload is used to demonstrate how to configure client applications to work with AlwaysOn Availability Groups.


Industry Best Practices


You should observe all industry best practices in the implementation of your HADR system in general. For example, network redundancy is crucial for a production environment to circumvent outages due to network anomalies. You may notice that I simplify such elements especially in some screenshots, so that they don't become a distraction from the migration steps themselves.


Success Criteria


This walkthrough attempts to balance between a real-life migration environment and requirements and the generalized use case. While it does not possess the complexity of a real-life scenario, a few common migration criteria are included.



  • Maintain separation of roles, where applicable (domain administrator, database administrator, application developer).

  • Minimize downtime during migration.

  • Approximate the high availability and disaster recovery capabilities during migration.


Prerequisites


To prepare the base configuration, you need the following hardware and/or software:



To complete the migration process successfully, you need the following additional software:



NOTE : For more information on all supported version upgrades, see Supported Version and Edition Upgrades . The in-place upgrade described in this blog series does not support upgrade paths from or to Evaluation Editions of SQL Server. Furthermore, you must use an edition of SQL Server that supports database mirroring, log shipping, and AlwaysOn Availability Groups where applicable. The minimum license requirement for completing the walkthrough is summarized below.















SQL Server Developer



PRIMARY1, PRIMARY2, and REMOTE1



SQL Server Express



WITNESS




For more information, see Features Supported by the Editions of SQL Server 2012 .


Step 0: Setup Base Configuration


So, let's start!


First, you implement the network topology shown in the diagram below. The two subnets simulate the two sites in Contoso Corporation. The client machine simulates a write workload on the SQL Server instances during the migration process.



The walkthrough steps focus on the software configuration on the server nodes and not on the implementation of the network topology.


Set up the Domain Controller and Accounts


Role: Domain Administrator




  1. Set up the domain controller (DC) for DNS service with the domain specifications below.














    Domain Name



    CORP



    FQDN



    corp.contoso.com





  2. Create the domain user accounts show below:


























    Domain Administrator



    CORP\Administrator



    SQL Installation



    CORP\Install



    Database Administrator



    CORP\DBAdmin



    Client Application



    CORP\SQLClient



    SQL Service Accounts



    CORP\SQLSvc1


    CORP\SQLSvc2


    CORP\SQLSvc3


    CORP\SQLSvc4


    CORP\SQLAgent1


    CORP\SQLAgent2


    CORP\SQLAgent3


    CORP\SQLAgent4





Deploy SQL Server 2008 R2 Solution


Role: Database Administrator


Perform the following steps on PRIMARY1 , PRIMARY2 , WITNESS , and REMOTE1 :



  1. Install Windows Server 2008 R2 SP1, and then join the machine to the domain.

  2. Give CORP\Install administrative privileges on the local machine.


  3. Install the default SQL Server 2008 R2 instance with the respective service accounts shown below.































    Machine



    SQL Service Account



    SQL Agent Account



    PRIMARY1



    SQLSvc1



    SQLAgent1



    PRIMARY2



    SQLSvc2



    SQLAgent2



    WITNESS



    SQLSvc3



    SQLAgent3



    REMOTE1



    SQLSvc4



    SQLAgent4





  4. Open the firewall for remote access of the default SQL Server instance with the following command:


    netsh advfirewall firewall add rule name='SQL Server (TCP-In)' dir=in action=allow protocol=TCP localport=1433 profile=domain



  5. In SQL Server Configuration Manager , under the SQL Server Network Configuration node, enable the TCP/IP protocol.

  6. Restart the database instance.


Create Databases and Configure Database Mirroring


Role: Database Administrator


Perform the following steps to create the databases and configure database mirroring sessions.




  1. On both PRIMARY1 and PRIMARY2 , open port 5022, the default port SQL Server uses for database mirroring with the following command:

    netsh advfirewall firewall add rule name='SQL Server Mirroring (TCP-In)' dir=in action=allow protocol=TCP localport=5022 profile=domain



  2. In SQL Server Management Studio (SSMS), create two databases on PRIMARY1 , named test1 and test2 .


  3. Create a table in both test1 and test2 , called table1 , using the following script:

    CREATE TABLE dbo.table1(col1 nchar(10), col2 nchar(10))



  4. Prepare the mirror databases for test1 and test2 on PRIMARY2 .


  5. Establish the database mirroring sessions on the two databases using the specifications below.














































    Principal Role



    PRIMARY1



    Principal Service Account



    CORP\SQLSvc1



    Principal Endpoint



    TCP://primary1.corp.contoso.local:5022



    Mirror Role



    PRIMARY2



    Mirror Service Account



    CORP\SQLSvc2



    Mirror Endpoint



    TCP://primary2.corp.contoso.local:5022



    Witness Role



    WITNESS



    Witness Service Account



    CORP\SQLSvc3



    Witness Endpoint



    TCP://witness.corp.contoso.local:5022



    Operating Mode



    High safety






After you configure database mirroring on table1 and table2 , you can use the Database Mirroring Monitor to make sure that the servers have connectivity and are synchronized for both databases. It should look similar to the screenshot below.



Configure Log Shipping


Role: Database Administrator


Perform the following steps to set up log shipping for the two databases.



  1. On PRIMARY1 , PRIMARY2 , and REMOTE1 , in SQL Server Configuration Manager, set the SQL Server Agent service to start automatically.

  2. Start the SQL Server Agent service on all three machines.


  3. Create the following share folders:


















    Network Name



    Permissions



    \\WITNESS\test1


    \\WITNESS\test2



    SQLSvc1 : Read and Write


    SQLSvc2 : Read and Write


    SQLSvc4 : Read


    SQLAgent4 : Read



    \\REMOTE1\test1


    \\REMOTE1\test2



    SQLSvc4: Read


    SQLAgent4 : Read and Write





  4. On PRIMARY1 and PRIMARY2 , set up log shipping of test1 and test2 to REMOTE1 using the following parameters:
























    Database



    Backup Folder



    Destination Folder for Copied Files



    Restore Mode



    test1



    \\WITNESS\test1



    \\REMOTE1\test1



    No recovery



    test2



    \\WITNESS\test2



    \\REMOTE1\test2



    No recovery





NOTE : Since you are mixing database mirroring and log shipping, you cannot perform this step using the SSMS user interface alone. For instructions, see the Log Shipping to a Third Destination from a Database Mirroring Pair section in Database Mirroring and Log Shipping Working Together .


When you are finished, the SSMS view of the three database instances in Object Explorer should look similar to the screenshot below. PRIMARY1 is the principal database server for both databases and PRIMARY2 is the mirror database server.



Configure Access for the Client Application


Role: Database Administrator


For client access to the databases, perform the following steps in SSMS for the various database instances:




  1. On PRIMARY1 , add CORP\SQLClient as a login , and then give CORP\SQLClient the following database memberships for both test1 and test2 :


    • db_datareader

    • db_datawriter



  2. Perform a manual database mirroring failover for both test1 and test2 to PRIMARY2 .


  3. In PRIMARY2 , add CORP\SQLClient as a login , and then give CORP\SQLClient the following database memberships for both test1 and test2:


    • db_datareader

    • db_datawriter



  4. Perform a manual database mirroring failover back to PRIMARY1 .


You will use CORP\SQLClient to connect to the databases from the client application in later steps. If a failure happens during your migration process, you have ensured that clients have access to the automatic failover partner. In a disaster recovery scenario, you must bring REMOTE1 online, and then add CORP\SQLClient to REMOTE1 , and then configure the same database memberships so that client applications can connect to it.


Role: Application Developer


On CLIENT , perform the following steps to run the client application.



  1. Install Windows 7 .

  2. Join the machine to the corp.contoso.com domain using the CORP\Administrator account.

  3. Logout and login as CORP\Install .

  4. Install SQL Server 2008 R2 Management Studio .

  5. Start SSMS and connect to PRIMARY1 and PRIMARY2 to verify that the database instances can accept remote connections.

  6. Install Visual Studio .

  7. Logout and login as CORP\SQLClient .


  8. Create a console application (named ConsoleApplication1 by default), and then replace the code in Program.cs with the code below. This code continually attempts to connect to test1 and insert a record. Each connection attempt has three retries and a 5-second interval between each retry. The connection logic is taken from Implementing Application Failover with Database Mirroring .

    using System;


    using System.Data;


    using System.Data.SqlClient;


    using System.Threading;



    namespace ConsoleApplication1


    {


    class Program


    {


    const string connString = @"Server=PRIMARY1;Failover Partner=PRIMARY2;Initial Catalog=test1;Integrated Security=True";



    static bool ExecuteSQLWithRetry_NoResults(string strConn, string strCmd, int iRetryInterval, int iMaxRetries)


    {


    SqlConnection conn = null;


    SqlCommand cmd = null;



    for (int iRetryCount = 0; iRetryCount < iMaxRetries; iRetryCount++)


    {


    try


    {


    conn = new SqlConnection(strConn);


    conn.Open();


    if (null != conn && ConnectionState.Open == conn.State)


    {


    cmd = new SqlCommand(strCmd, conn);


    cmd.ExecuteNonQuery();


    return true;


    }


    }


    catch (Exception ex) { }


    finally


    {


    try


    {


    if (null != cmd)


    cmd.Dispose();


    if (null != conn && ConnectionState.Closed != conn.State)


    conn.Close();


    }


    catch (Exception ex) { }


    }



    if (iRetryCount < iMaxRetries)


    Thread.Sleep(iRetryInterval * 1000);


    }



    return false;


    }



    static void Main(string[] args)


    {



    while (true)


    {


    Random rand = new Random();


    string cmdString = "Insert into table1 values (N'" + rand.Next() + "',N'" + rand.Next() + "')";



    if (ExecuteSQLWithRetry_NoResults(connString, cmdString, 5, 3))


    Console.WriteLine("Success: " + cmdString);


    else Console.WriteLine("Error: " + cmdString);


    }


    }


    }


    }



  9. Run the application by opening the Debug menu and clicking Start Debugging .


You have now finished setting up the base configuration and are ready to start the migration scenario to the SQL Server AlwaysOn solution in SQL Server 2012.


But wait, this blog has gotten too long already! In the next installment , you will actually start the migration steps. Stay tuned!

%3CLINGO-SUB%20id%3D%22lingo-sub-384145%22%20slang%3D%22en-US%22%3EHow%20To%3A%20Migrate%20to%20AlwaysOn%20AlwaysOn%20from%20Prior%20Deployments%20Combining%20Database%20Mirroring%20and%20Log%20Shipping%20%E2%80%93%20Part%201%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-384145%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Oct%2016%2C%202012%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3EWriter%3A%20%3C%2FSTRONG%3E%20Cephas%20Lin%20(Microsoft)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3EContributors%3A%20%3C%2FSTRONG%3E%20Oleg%20Bulanyy%20(Microsoft)%2C%20Jimmy%20May%20(Microsoft)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThis%20blog%20series%20is%20a%20follow-up%20to%20my%20whitepaper%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fjj635217.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Migration%20Guide%3A%20Migrating%20to%20AlwaysOn%20Availability%20Groups%20from%20Prior%20Deployments%20Combining%20Database%20Mirroring%20and%20Log%20Shipping%20%3C%2FA%3E%20.%20In%20this%20series%2C%20I%20will%20walk%20you%20through%20the%20following%20end-to-end%20migration%20scenario%20inside%20a%20test%20environment%3A%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EFrom%20a%20SQL%20Server%202008%20R2%20solution%3A%20%3CSTRONG%3E%20Database%20mirroring%20%3C%2FSTRONG%3E%20for%20high%20availability%20and%20%3CSTRONG%3E%20log%20shipping%20%3C%2FSTRONG%3E%20for%20disaster%20recovery%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ETo%20a%20SQL%20Server%202012%20solution%3A%20%3CSTRONG%3E%20Availability%20Group%20%3C%2FSTRONG%3E%20for%20both%20high%20availability%20and%20disaster%20recovery%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3EBefore%20I%20go%20further%2C%20l%20want%20to%20refer%20you%20to%20a%20whitepaper%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fjj191711.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20AlwaysOn%20Architecture%20Guide%3A%20Building%20a%20High%20Availability%20and%20Disaster%20Recovery%20Solution%20by%20Using%20AlwaysOn%20Availability%20Groups%20%3C%2FA%3E%20.%20This%20is%20a%20great%20architectural%20guide%20for%20anyone%20who%20is%20interested%20about%20the%20end-to-end%20deployment%20of%20the%20AlwaysOn%20scenario%20I'm%20discussing%20in%20this%20blog.%3C%2FP%3E%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId-1680720548%22%20id%3D%22toc-hId-1732466679%22%3EHow%20to%20Use%20This%20Blog%20Series%3C%2FH2%3E%3CBR%20%2F%3E%3CP%3EThis%20is%20a%20disclaimer%20similar%20to%20the%20one%20I%20have%20in%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fsqlalwayson%2Farchive%2F2012%2F09%2F28%2Fhow-to-migrate-from-failover-cluster-instances-and-database-mirroring-to-sql-server-alwayson-part-1.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20How%20To%3A%20Migrate%20from%20Failover%20Cluster%20Instances%20and%20Database%20Mirroring%20to%20SQL%20Server%20AlwaysOn%20%3C%2FA%3E%20.%20My%20walkthrough%20steps%20are%20to%20help%20people%20get%20started%20on%20implementing%20a%20end-to-end%20migration%20scenario%20in%20a%20%3CEM%3E%20test%20environment.%20%3C%2FEM%3E%20I%20have%20already%20published%20prescriptive%20guidance%20on%20the%20migration%20scenario%20in%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fjj635217.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Migration%20Guide%3A%20Migrating%20to%20AlwaysOn%20Availability%20Groups%20from%20Prior%20Deployments%20Combining%20Database%20Mirroring%20and%20Log%20Shipping%20%3C%2FA%3E%20.%20If%20you%20are%20a%20database%20architect%20seeking%20guidance%20on%20migrating%20your%20production%20system%20to%20SQL%20Server%20AlwaysOn%2C%20I%20advise%20you%20to%20look%20there%20first.%20If%20you%20are%20a%20database%20administrator%20who%20needs%20help%20to%20get%20started%20on%20migrating%20your%20company's%20database%20system%20to%20SQL%20Server%20AlwaysOn%2C%20then%20my%20blog%20is%20for%20you.%20To%20expedite%20the%20testing%20phase%20of%20you%20migration%20scenario%2C%20I%20recommend%20that%20you%20walk%20through%20the%20entirety%20of%20this%20blog%20series%2C%20which%20can%20help%20you%20recognize%20and%20avoid%20many%20issues%20that%20can%20trip%20you%20up%20along%20the%20way.%20Once%20you%20have%20successfully%20completed%20the%20walkthrough%20in%20your%20test%20environment%2C%20you%20will%20know%20what%20success%20looks%20for%20a%20generalized%20scenario.%20The%20next%20natural%20step%20continue%20the%20testing%20phase%20with%20the%20specification%20of%20your%20SQL%20Server%20deployment.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EI%20cannot%20emphasize%20enough%3A%20%3CSTRONG%3E%20DO%20NOT%20%3C%2FSTRONG%3E%20apply%20these%20migration%20steps%20blindly%20to%20your%20production%20system.%20To%20minimize%20the%20risks%20of%20your%20actual%20migration%20to%20SQL%20Server%20AlwaysOn%2C%20test%20these%20steps%20thoroughly%20against%20every%20aspect%20of%20your%20SQL%20Server%20deployment%20(I%20repeat%2C%20%3CEM%3E%20in%20a%20test%20environment%20%3C%2FEM%3E%20)%20and%20incorporate%20all%20the%20necessary%20tweaks%20to%20accommodate%20your%20specific%20scenario.%3C%2FP%3E%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId--871436413%22%20id%3D%22toc-hId--819690282%22%3EMigration%20Walkthrough%20Overview%3C%2FH2%3E%3CBR%20%2F%3E%3CP%3EYou%20will%20walk%20through%20the%20migration%20procedures%20as%20covered%20in%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fjj635217%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Migration%20Guide%3A%20Migrating%20to%20AlwaysOn%20Availability%20Groups%20from%20Prior%20Deployments%20Combining%20Database%20Mirroring%20and%20Log%20Shipping%20Par%20I%20%E2%80%93%20Prescriptive%20Guidance%20%3C%2FA%3E%20.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20following%20table%20outlines%20the%20starting%20point%20and%20the%20end%20point%20of%20the%20walkthrough.%3C%2FP%3E%3CBR%20%2F%3E%3CDIV%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Start%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20End%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EPRIMARY1%2C%20PRIMARY2%2C%20and%20WITNESS%20in%20Subnet1%2C%20implementing%20database%20mirroring%20for%20local%20high%20availability%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EREMOTE1%20in%20Subnet2%2C%20shipping%20logs%20from%20PRIMARY1%20and%20PRIMARY2%20for%20remote%20disaster%20recovery%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EOne%20availability%20group%20on%20PRIMARY1%2C%20PRIMARY2%2C%20and%20REMOTE1%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EPRIMARY1%20as%20primary%20replica%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EPRIMARY2%20as%20synchronous%20secondary%20replica%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EREMOTE1%20as%20asynchronous%20secondary%20replica%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CP%3EThe%20four%20stages%20of%20migration%20as%20covered%20in%20the%20whitepaper%20are%20as%20follows%3A%3C%2FP%3E%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3EOnline%20upgrade%20of%20the%20log%20shipping%20secondary%20database%20server%20(REMOTE1).%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EOnline%20upgrade%20of%20the%20mirror%20database%20server%20(PRIMARY2).%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EOnline%20upgrade%20of%20the%20principal%20database%20server%20(PRIMARY1).%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EConfiguration%20of%20an%20availability%20group.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%3CBR%20%2F%3E%3CP%3EIf%20you%20build%20the%20base%20configuration%20I%20prescribe%20here%2C%20you%20will%20find%20a%20clear%20path%20to%20success%20in%20the%20migration%20procedure.%20You%20can%20instead%20use%20your%20existing%20HADR%20solution%20as%20the%20base%20configuration%2C%20but%20just%20realize%20that%20I%20cannot%20guarantee%20that%20my%20migration%20steps%20will%20work%20as-is%20with%20your%20HADR%20solution.%20In%20all%20likelihood%2C%20you%20will%20need%20to%20tweaks%20as%20you%20follow%20along.%20Make%20sure%20that%20you%20test%20the%20migration%20steps%20thoroughly%20before%20attempting%20the%20same%20steps%20on%20your%20production%20environment.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EAs%20client%20connectivity%20is%20an%20important%20part%20of%20the%20migration%20scenario%2C%20you%20will%20simulate%20a%20client%20workload%20on%20the%20database%20system%20to%20test%20the%20HADR%20capabilities%20of%20the%20system%20before%2C%20during%2C%20and%20after%20the%20migration%20process.%20In%20addition%2C%20the%20client%20workload%20is%20used%20to%20demonstrate%20how%20to%20configure%20client%20applications%20to%20work%20with%20AlwaysOn%20Availability%20Groups.%3C%2FP%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId-674860417%22%20id%3D%22toc-hId-726606548%22%3EIndustry%20Best%20Practices%3C%2FH3%3E%3CBR%20%2F%3E%3CP%3EYou%20should%20observe%20all%20industry%20best%20practices%20in%20the%20implementation%20of%20your%20HADR%20system%20in%20general.%20For%20example%2C%20network%20redundancy%20is%20crucial%20for%20a%20production%20environment%20to%20circumvent%20outages%20due%20to%20network%20anomalies.%20You%20may%20notice%20that%20I%20simplify%20such%20elements%20especially%20in%20some%20screenshots%2C%20so%20that%20they%20don't%20become%20a%20distraction%20from%20the%20migration%20steps%20themselves.%20%3CSTRONG%3E%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId--1877296544%22%20id%3D%22toc-hId--1825550413%22%3ESuccess%20Criteria%3C%2FH3%3E%3CBR%20%2F%3E%3CP%3EThis%20walkthrough%20attempts%20to%20balance%20between%20a%20real-life%20migration%20environment%20and%20requirements%20and%20the%20generalized%20use%20case.%20While%20it%20does%20not%20possess%20the%20complexity%20of%20a%20real-life%20scenario%2C%20a%20few%20common%20migration%20criteria%20are%20included.%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EMaintain%20separation%20of%20roles%2C%20where%20applicable%20(domain%20administrator%2C%20database%20administrator%2C%20application%20developer).%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EMinimize%20downtime%20during%20migration.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EApproximate%20the%20high%20availability%20and%20disaster%20recovery%20capabilities%20during%20migration.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId--134486209%22%20id%3D%22toc-hId--82740078%22%3EPrerequisites%3C%2FH3%3E%3CBR%20%2F%3E%3CP%3ETo%20prepare%20the%20base%20configuration%2C%20you%20need%20the%20following%20hardware%20and%2For%20software%3A%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EHardware%20to%20implement%20the%20network%20topology%2C%20including%20servers%2C%20switches%2C%20and%20so%20on.%20You%20can%20simulate%20the%20network%20topology%20using%20Hyper-V.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Fevalcenter%2Fdd459137.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Windows%20Server%202008%20R2%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ESQL%20Server%202008%20R2%20SP1%20(see%20note%20below)%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22http%3A%2F%2Fwww.microsoft.com%2Fexpress%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Visual%20Studio%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Fevalcenter%2Fcc442495%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Windows%207%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3ETo%20complete%20the%20migration%20process%20successfully%2C%20you%20need%20the%20following%20additional%20software%3A%3C%2FP%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3ESQL%20Server%202012%20(see%20note%20below)%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%3E%20KB%202494036%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3ENOTE%20%3C%2FSTRONG%3E%20%3A%20For%20more%20information%20on%20all%20supported%20version%20upgrades%2C%20see%20%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms143393.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Supported%20Version%20and%20Edition%20Upgrades%20%3C%2FA%3E%20.%20The%20in-place%20upgrade%20described%20in%20this%20blog%20series%20does%20not%20support%20upgrade%20paths%20from%20or%20to%20Evaluation%20Editions%20of%20SQL%20Server.%20Furthermore%2C%20you%20must%20use%20an%20edition%20of%20SQL%20Server%20that%20supports%20database%20mirroring%2C%20log%20shipping%2C%20and%20AlwaysOn%20Availability%20Groups%20where%20applicable.%20The%20minimum%20license%20requirement%20for%20completing%20the%20walkthrough%20is%20summarized%20below.%3C%2FP%3E%3CBR%20%2F%3E%3CDIV%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESQL%20Server%20Developer%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3EPRIMARY1%2C%20PRIMARY2%2C%20and%20REMOTE1%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESQL%20Server%20Express%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3EWITNESS%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CP%3EFor%20more%20information%2C%20see%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fcc645993.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Features%20Supported%20by%20the%20Editions%20of%20SQL%20Server%202012%20%3C%2FA%3E%20.%3C%2FP%3E%3CBR%20%2F%3E%3CH2%20id%3D%22toc-hId-1804837631%22%20id%3D%22toc-hId-1856583762%22%3EStep%200%3A%20Setup%20Base%20Configuration%3C%2FH2%3E%3CBR%20%2F%3E%3CP%3ESo%2C%20let's%20start!%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFirst%2C%20you%20implement%20the%20network%20topology%20shown%20in%20the%20diagram%20below.%20The%20two%20subnets%20simulate%20the%20two%20sites%20in%20Contoso%20Corporation.%20The%20client%20machine%20simulates%20a%20write%20workload%20on%20the%20SQL%20Server%20instances%20during%20the%20migration%20process.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97614i2EE305FA4853A139%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20walkthrough%20steps%20focus%20on%20the%20software%20configuration%20on%20the%20server%20nodes%20and%20not%20on%20the%20implementation%20of%20the%20network%20topology.%3C%2FP%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId--943832835%22%20id%3D%22toc-hId--892086704%22%3ESet%20up%20the%20Domain%20Controller%20and%20Accounts%3C%2FH3%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Role%3A%20Domain%20Administrator%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3E%3CBR%20%2F%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fcc753100(v%3Dws.10).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ESet%20up%20the%20domain%20controller%20%3C%2FA%3E%20(DC)%20for%20DNS%20service%20with%20the%20domain%20specifications%20below.%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Domain%20Name%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ECORP%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20FQDN%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3Ecorp.contoso.com%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CBR%20%2F%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fcc784390(v%3DWS.10).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ECreate%20the%20domain%20%3CSTRONG%3E%20user%20%3C%2FSTRONG%3E%20accounts%20%3C%2FA%3E%20show%20below%3A%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Domain%20Administrator%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ECORP%5CAdministrator%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20SQL%20Installation%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ECORP%5CInstall%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Database%20Administrator%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ECORP%5CDBAdmin%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Client%20Application%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ECORP%5CSQLClient%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20SQL%20Service%20Accounts%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ECORP%5CSQLSvc1%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECORP%5CSQLSvc2%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECORP%5CSQLSvc3%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECORP%5CSQLSvc4%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECORP%5CSQLAgent1%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECORP%5CSQLAgent2%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECORP%5CSQLAgent3%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECORP%5CSQLAgent4%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId-798977500%22%20id%3D%22toc-hId-850723631%22%3EDeploy%20SQL%20Server%202008%20R2%20Solution%3C%2FH3%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Role%3A%20Database%20Administrator%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPerform%20the%20following%20steps%20on%20%3CSTRONG%3E%20PRIMARY1%20%3C%2FSTRONG%3E%20%2C%20%3CSTRONG%3E%20PRIMARY2%20%3C%2FSTRONG%3E%20%2C%20%3CSTRONG%3E%20WITNESS%20%3C%2FSTRONG%3E%20%2C%20and%20%3CSTRONG%3E%20REMOTE1%20%3C%2FSTRONG%3E%20%3A%3C%2FP%3E%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3EInstall%20%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Fevalcenter%2Fdd459137.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Windows%20Server%202008%20R2%20%3C%2FA%3E%20SP1%2C%20and%20then%20join%20the%20machine%20to%20the%20domain.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EGive%20%3CSTRONG%3E%20CORP%5CInstall%20%3C%2FSTRONG%3E%20administrative%20privileges%20on%20the%20local%20machine.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CBR%20%2F%3E%3CP%3EInstall%20the%20default%20SQL%20Server%202008%20R2%20instance%20with%20the%20respective%20service%20accounts%20shown%20below.%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Machine%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20SQL%20Service%20Account%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20SQL%20Agent%20Account%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3EPRIMARY1%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESQLSvc1%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESQLAgent1%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3EPRIMARY2%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESQLSvc2%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESQLAgent2%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3EWITNESS%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESQLSvc3%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESQLAgent3%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3EREMOTE1%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESQLSvc4%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ESQLAgent4%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CBR%20%2F%3E%3CP%3EOpen%20the%20firewall%20for%20remote%20access%20of%20the%20default%20SQL%20Server%20instance%20with%20the%20following%20command%3A%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Enetsh%20advfirewall%20firewall%20add%20rule%20name%3D'SQL%20Server%20(TCP-In)'%20dir%3Din%20action%3Dallow%20protocol%3DTCP%20localport%3D1433%20profile%3Ddomain%3C%2FP%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EIn%20%3CSTRONG%3E%20SQL%20Server%20Configuration%20Manager%20%3C%2FSTRONG%3E%20%2C%20under%20the%20%3CSTRONG%3E%20SQL%20Server%20Network%20Configuration%20%3C%2FSTRONG%3E%20node%2C%20enable%20the%20TCP%2FIP%20protocol.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ERestart%20the%20database%20instance.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId--1753179461%22%20id%3D%22toc-hId--1701433330%22%3ECreate%20Databases%20and%20Configure%20Database%20Mirroring%3C%2FH3%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Role%3A%20Database%20Administrator%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPerform%20the%20following%20steps%20to%20create%20the%20databases%20and%20configure%20database%20mirroring%20sessions.%3C%2FP%3E%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3E%3CBR%20%2F%3E%3CDIV%3EOn%20both%20%3CSTRONG%3E%20PRIMARY1%20%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E%20PRIMARY2%20%3C%2FSTRONG%3E%20%2C%20open%20port%205022%2C%20the%20default%20port%20SQL%20Server%20uses%20for%20database%20mirroring%20with%20the%20following%20command%3A%3C%2FDIV%3E%3CBR%20%2F%3E%3CP%3Enetsh%20advfirewall%20firewall%20add%20rule%20name%3D'SQL%20Server%20Mirroring%20(TCP-In)'%20dir%3Din%20action%3Dallow%20protocol%3DTCP%20localport%3D5022%20profile%3Ddomain%3C%2FP%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EIn%20%3CSTRONG%3E%20SQL%20Server%20Management%20Studio%20%3C%2FSTRONG%3E%20(SSMS)%2C%20%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms186312.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20create%20two%20databases%20%3C%2FA%3E%20on%20%3CSTRONG%3E%20PRIMARY1%20%3C%2FSTRONG%3E%20%2C%20named%20%3CSTRONG%3E%20test1%20%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E%20test2%20%3C%2FSTRONG%3E%20.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CBR%20%2F%3E%3CDIV%3E%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms188264.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ECreate%20a%20table%20%3C%2FA%3E%20in%20both%20%3CSTRONG%3E%20test1%20%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E%20test2%20%3C%2FSTRONG%3E%20%2C%20called%20%3CSTRONG%3E%20table1%20%3C%2FSTRONG%3E%20%2C%20using%20the%20following%20script%3A%3C%2FDIV%3E%3CBR%20%2F%3E%3CP%3ECREATE%20TABLE%20dbo.table1(col1%20nchar(10)%2C%20col2%20nchar(10))%3C%2FP%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms189053.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EPrepare%20the%20mirror%20databases%20%3C%2FA%3E%20for%20%3CSTRONG%3E%20test1%20%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E%20test2%20%3C%2FSTRONG%3E%20on%20%3CSTRONG%3E%20PRIMARY2%20%3C%2FSTRONG%3E%20.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CBR%20%2F%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms188712.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EEstablish%20the%20database%20mirroring%20sessions%20%3C%2FA%3E%20on%20the%20two%20databases%20using%20the%20specifications%20below.%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Principal%20Role%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3EPRIMARY1%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Principal%20Service%20Account%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ECORP%5CSQLSvc1%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Principal%20Endpoint%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ETCP%3A%2F%2Fprimary1.corp.contoso.local%3A5022%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Mirror%20Role%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3EPRIMARY2%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Mirror%20Service%20Account%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ECORP%5CSQLSvc2%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Mirror%20Endpoint%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ETCP%3A%2F%2Fprimary2.corp.contoso.local%3A5022%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Witness%20Role%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3EWITNESS%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Witness%20Service%20Account%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ECORP%5CSQLSvc3%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Witness%20Endpoint%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ETCP%3A%2F%2Fwitness.corp.contoso.local%3A5022%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Operating%20Mode%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3EHigh%20safety%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%3CBR%20%2F%3E%3CP%3E%3CBR%20%2F%3E%20After%20you%20configure%20database%20mirroring%20on%20%3CSTRONG%3E%20table1%20%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E%20table2%20%3C%2FSTRONG%3E%20%2C%20you%20can%20use%20the%20%3CSTRONG%3E%20Database%20Mirroring%20Monitor%20%3C%2FSTRONG%3E%20to%20make%20sure%20that%20the%20servers%20have%20connectivity%20and%20are%20synchronized%20for%20both%20databases.%20It%20should%20look%20similar%20to%20the%20screenshot%20below.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97615iCFA60D131B1AC3A3%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId--10369126%22%20id%3D%22toc-hId-41377005%22%3EConfigure%20Log%20Shipping%3C%2FH3%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Role%3A%20Database%20Administrator%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EPerform%20the%20following%20steps%20to%20set%20up%20log%20shipping%20for%20the%20two%20databases.%3C%2FP%3E%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3EOn%20%3CSTRONG%3E%20PRIMARY1%20%3C%2FSTRONG%3E%20%2C%20%3CSTRONG%3E%20PRIMARY2%20%3C%2FSTRONG%3E%20%2C%20and%20%3CSTRONG%3E%20REMOTE1%20%3C%2FSTRONG%3E%20%2C%20in%20SQL%20Server%20Configuration%20Manager%2C%20set%20the%20%3CSTRONG%3E%20SQL%20Server%20Agent%20%3C%2FSTRONG%3E%20service%20to%20start%20automatically.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EStart%20the%20%3CSTRONG%3E%20SQL%20Server%20Agent%20%3C%2FSTRONG%3E%20service%20on%20all%20three%20machines.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CBR%20%2F%3E%3CP%3ECreate%20the%20following%20share%20folders%3A%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Network%20Name%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Permissions%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%5C%5CWITNESS%5Ctest1%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%5C%5CWITNESS%5Ctest2%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3ESQLSvc1%20%3C%2FSTRONG%3E%20%3A%20%3CSTRONG%3E%20Read%20%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E%20Write%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3ESQLSvc2%20%3C%2FSTRONG%3E%20%3A%20%3CSTRONG%3E%20Read%20%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E%20Write%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3ESQLSvc4%20%3C%2FSTRONG%3E%20%3A%20%3CSTRONG%3E%20Read%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3ESQLAgent4%20%3C%2FSTRONG%3E%20%3A%20%3CSTRONG%3E%20Read%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%5C%5CREMOTE1%5Ctest1%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%5C%5CREMOTE1%5Ctest2%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20SQLSvc4%3A%20Read%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3ESQLAgent4%20%3C%2FSTRONG%3E%20%3A%20%3CSTRONG%3E%20Read%20%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E%20Write%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CBR%20%2F%3E%3CP%3EOn%20%3CSTRONG%3E%20PRIMARY1%20%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E%20PRIMARY2%20%3C%2FSTRONG%3E%20%2C%20set%20up%20log%20shipping%20of%20%3CSTRONG%3E%20test1%20%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E%20test2%20%3C%2FSTRONG%3E%20to%20%3CSTRONG%3E%20REMOTE1%20%3C%2FSTRONG%3E%20using%20the%20following%20parameters%3A%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CTABLE%3E%0A%20%20%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Database%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Backup%20Folder%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Destination%20Folder%20for%20Copied%20Files%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Restore%20Mode%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3Etest1%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%5C%5CWITNESS%5Ctest1%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%5C%5CREMOTE1%5Ctest1%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ENo%20recovery%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3Etest2%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%5C%5CWITNESS%5Ctest2%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3E%5C%5CREMOTE1%5Ctest2%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%20%3CTD%3E%3CBR%20%2F%3E%3CP%3ENo%20recovery%3C%2FP%3E%3CBR%20%2F%3E%3C%2FTD%3E%0A%20%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3ENOTE%20%3C%2FSTRONG%3E%20%3A%20Since%20you%20are%20mixing%20database%20mirroring%20and%20log%20shipping%2C%20you%20cannot%20perform%20this%20step%20using%20the%20SSMS%20user%20interface%20alone.%20For%20instructions%2C%20see%20the%20%3CSTRONG%3E%20Log%20Shipping%20to%20a%20Third%20Destination%20from%20a%20Database%20Mirroring%20Pair%20%3C%2FSTRONG%3E%20section%20in%20%3CA%20href%3D%22http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2Fd%2F9%2F4%2Fd948f981-926e-40fa-a026-5bfcf076d9b9%2FDBMandLogShipping.docx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Database%20Mirroring%20and%20Log%20Shipping%20Working%20Together%20%3C%2FA%3E%20.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EWhen%20you%20are%20finished%2C%20the%20SSMS%20view%20of%20the%20three%20database%20instances%20in%20%3CSTRONG%3E%20Object%20Explorer%20%3C%2FSTRONG%3E%20should%20look%20similar%20to%20the%20screenshot%20below.%20%3CSTRONG%3EPRIMARY1%20%3C%2FSTRONG%3E%20is%20the%20principal%20database%20server%20for%20both%20databases%20and%20%3CSTRONG%3E%20PRIMARY2%20%3C%2FSTRONG%3E%20is%20the%20mirror%20database%20server.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F97616iDB77B71EC2F28547%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%3CH3%20id%3D%22toc-hId-1017279920%22%20id%3D%22toc-hId-1069026051%22%3EConfigure%20Access%20for%20the%20Client%20Application%3C%2FH3%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Role%3A%20Database%20Administrator%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFor%20client%20access%20to%20the%20databases%2C%20perform%20the%20following%20steps%20in%20SSMS%20for%20the%20various%20database%20instances%3A%3C%2FP%3E%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3E%3CBR%20%2F%3E%3CDIV%3EOn%20%3CSTRONG%3E%20PRIMARY1%20%3C%2FSTRONG%3E%20%2C%20add%20%3CSTRONG%3E%20CORP%5CSQLClient%20%3C%2FSTRONG%3E%20as%20a%20%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Faa337562.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20login%20%3C%2FA%3E%20%2C%20and%20then%20give%20%3CSTRONG%3E%20CORP%5CSQLClient%20%3C%2FSTRONG%3E%20the%20following%20database%20memberships%20for%20both%20%3CSTRONG%3E%20test1%20%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E%20test2%20%3C%2FSTRONG%3E%20%3A%3C%2FDIV%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3Edb_datareader%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3Edb_datawriter%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EPerform%20a%20%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms186348.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20manual%20database%20mirroring%20failover%20%3C%2FA%3E%20for%20both%20%3CSTRONG%3E%20test1%20%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E%20test2%20%3C%2FSTRONG%3E%20to%20%3CSTRONG%3E%20PRIMARY2%20%3C%2FSTRONG%3E%20.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CBR%20%2F%3E%3CDIV%3EIn%20%3CSTRONG%3E%20PRIMARY2%20%3C%2FSTRONG%3E%20%2C%20add%20%3CSTRONG%3E%20CORP%5CSQLClient%20%3C%2FSTRONG%3E%20as%20a%20%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Faa337562.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20login%20%3C%2FA%3E%20%2C%20and%20then%20give%20%3CSTRONG%3E%20CORP%5CSQLClient%20%3C%2FSTRONG%3E%20the%20following%20database%20memberships%20for%20both%20test1%20and%20test2%3A%3C%2FDIV%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3Edb_datareader%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3Edb_datawriter%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EPerform%20a%20%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms186348.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20manual%20database%20mirroring%20failover%20%3C%2FA%3E%20back%20to%20%3CSTRONG%3E%20PRIMARY1%20%3C%2FSTRONG%3E%20.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%3CBR%20%2F%3E%3CP%3EYou%20will%20use%20%3CSTRONG%3E%20CORP%5CSQLClient%20%3C%2FSTRONG%3E%20to%20connect%20to%20the%20databases%20from%20the%20client%20application%20in%20later%20steps.%20If%20a%20failure%20happens%20during%20your%20migration%20process%2C%20you%20have%20ensured%20that%20clients%20have%20access%20to%20the%20automatic%20failover%20partner.%20In%20a%20disaster%20recovery%20scenario%2C%20you%20must%20bring%20%3CSTRONG%3E%20REMOTE1%20%3C%2FSTRONG%3E%20online%2C%20and%20then%20add%20%3CSTRONG%3E%20CORP%5CSQLClient%20%3C%2FSTRONG%3E%20to%20%3CSTRONG%3E%20REMOTE1%20%3C%2FSTRONG%3E%20%2C%20and%20then%20configure%20the%20same%20database%20memberships%20so%20that%20client%20applications%20can%20connect%20to%20it.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CSTRONG%3E%20Role%3A%20Application%20Developer%20%3C%2FSTRONG%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EOn%20%3CSTRONG%3E%20CLIENT%20%3C%2FSTRONG%3E%20%2C%20perform%20the%20following%20steps%20to%20run%20the%20client%20application.%3C%2FP%3E%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3EInstall%20%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Fevalcenter%2Fcc442495%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Windows%207%20%3C%2FA%3E%20.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EJoin%20the%20machine%20to%20the%20%3CSTRONG%3E%20corp.contoso.com%20%3C%2FSTRONG%3E%20domain%20using%20the%20%3CSTRONG%3E%20CORP%5CAdministrator%20%3C%2FSTRONG%3E%20account.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ELogout%20and%20login%20as%20%3CSTRONG%3E%20CORP%5CInstall%20%3C%2FSTRONG%3E%20.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EInstall%20%3CSTRONG%3E%20SQL%20Server%202008%20R2%20Management%20Studio%20%3C%2FSTRONG%3E%20.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EStart%20SSMS%20and%20connect%20to%20%3CSTRONG%3E%20PRIMARY1%20%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E%20PRIMARY2%20%3C%2FSTRONG%3E%20to%20verify%20that%20the%20database%20instances%20can%20accept%20remote%20connections.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EInstall%20%3CA%20href%3D%22http%3A%2F%2Fwww.microsoft.com%2Fexpress%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Visual%20Studio%20%3C%2FA%3E%20.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ELogout%20and%20login%20as%20%3CSTRONG%3E%20CORP%5CSQLClient%20%3C%2FSTRONG%3E%20.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CBR%20%2F%3E%3CDIV%3E%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fzfzh36t7(v%3Dvs.100).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ECreate%20a%20console%20application%20%3C%2FA%3E%20(named%20%3CSTRONG%3E%20ConsoleApplication1%20%3C%2FSTRONG%3E%20by%20default)%2C%20and%20then%20replace%20the%20code%20in%20%3CSTRONG%3E%20Program.cs%20%3C%2FSTRONG%3E%20with%20the%20code%20below.%20This%20code%20continually%20attempts%20to%20connect%20to%20%3CSTRONG%3E%20test1%20%3C%2FSTRONG%3E%20and%20insert%20a%20record.%20Each%20connection%20attempt%20has%20three%20retries%20and%20a%205-second%20interval%20between%20each%20retry.%20The%20connection%20logic%20is%20taken%20from%20%3CA%20href%3D%22http%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fcc917713.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20Implementing%20Application%20Failover%20with%20Database%20Mirroring%20%3C%2FA%3E%20.%3C%2FDIV%3E%3CBR%20%2F%3E%3CP%3Eusing%20System%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eusing%20System.Data%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eusing%20System.Data.SqlClient%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eusing%20System.Threading%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Enamespace%20ConsoleApplication1%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eclass%20Program%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Econst%20string%20connString%20%3D%20%40%22Server%3DPRIMARY1%3BFailover%20Partner%3DPRIMARY2%3BInitial%20Catalog%3Dtest1%3BIntegrated%20Security%3DTrue%22%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Estatic%20bool%20ExecuteSQLWithRetry_NoResults(string%20strConn%2C%20string%20strCmd%2C%20int%20iRetryInterval%2C%20int%20iMaxRetries)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESqlConnection%20conn%20%3D%20null%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ESqlCommand%20cmd%20%3D%20null%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Efor%20(int%20iRetryCount%20%3D%200%3B%20iRetryCount%20%26lt%3B%20iMaxRetries%3B%20iRetryCount%2B%2B)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Etry%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Econn%20%3D%20new%20SqlConnection(strConn)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Econn.Open()%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eif%20(null%20!%3D%20conn%20%26amp%3B%26amp%3B%20ConnectionState.Open%20%3D%3D%20conn.State)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ecmd%20%3D%20new%20SqlCommand(strCmd%2C%20conn)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ecmd.ExecuteNonQuery()%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ereturn%20true%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ecatch%20(Exception%20ex)%20%7B%20%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Efinally%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Etry%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eif%20(null%20!%3D%20cmd)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ecmd.Dispose()%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eif%20(null%20!%3D%20conn%20%26amp%3B%26amp%3B%20ConnectionState.Closed%20!%3D%20conn.State)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Econn.Close()%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ecatch%20(Exception%20ex)%20%7B%20%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eif%20(iRetryCount%20%26lt%3B%20iMaxRetries)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThread.Sleep(iRetryInterval%20*%201000)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ereturn%20false%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Estatic%20void%20Main(string%5B%5D%20args)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Ewhile%20(true)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ERandom%20rand%20%3D%20new%20Random()%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Estring%20cmdString%20%3D%20%22Insert%20into%20table1%20values%20(N'%22%20%2B%20rand.Next()%20%2B%20%22'%2CN'%22%20%2B%20rand.Next()%20%2B%20%22')%22%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eif%20(ExecuteSQLWithRetry_NoResults(connString%2C%20cmdString%2C%205%2C%203))%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EConsole.WriteLine(%22Success%3A%20%22%20%2B%20cmdString)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eelse%20Console.WriteLine(%22Error%3A%20%22%20%2B%20cmdString)%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%7D%3C%2FP%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3ERun%20the%20application%20by%20opening%20the%20%3CSTRONG%3E%20Debug%20%3C%2FSTRONG%3E%20menu%20and%20clicking%20%3CSTRONG%3E%20Start%20Debugging%20%3C%2FSTRONG%3E%20.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%3CBR%20%2F%3E%3CP%3EYou%20have%20now%20finished%20setting%20up%20the%20base%20configuration%20and%20are%20ready%20to%20start%20the%20migration%20scenario%20to%20the%20SQL%20Server%20AlwaysOn%20solution%20in%20SQL%20Server%202012.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EBut%20wait%2C%20this%20blog%20has%20gotten%20too%20long%20already!%20In%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fsqlalwayson%2Farchive%2F2012%2F10%2F16%2Fhow-to-migrate-to-alwayson-from-prior-deployments-combining-database-mirroring-and-log-shipping-part-2.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20the%20next%20installment%20%3C%2FA%3E%20%2C%20you%20will%20%3CEM%3E%20actually%20%3C%2FEM%3E%20start%20the%20migration%20steps.%20Stay%20tuned!%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-384145%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Oct%2016%2C%202012%20Writer%3A%20Cephas%20Lin%20(Microsoft)%20Contributors%3A%20Oleg%20Bulanyy%20(Microsoft)%2C%20Jimmy%20May%20(Microsoft)%20This%20blog%20series%20is%20a%20follow-up%20to%20my%20whitepaper%20Migration%20Guide%3A%20Migrating%20to%20AlwaysOn%20Availability%20Groups%20from%20Prior%20Deployments%20Combining%20Database%20Mirroring%20and%20Log%20Shipping.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-384145%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerAlwaysOn%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 12:57 PM
Updated by: