locked
SQL server change for existing 2 production Biztalk server RRS feed

  • Question

  • I have a requirement:  we have to move entire SQL server where BizTalk and applications are deployed to new SQL server.

    For those what are the steps I need to follow? Should I need to Un-deploy all application from the admin console and redeploy in new SQL server?

    I have a set up of 2 Biztalk server and 1 SQL server (Not clustered) in production.


    Jagadeesh RS

    Friday, August 21, 2015 5:26 AM

Answers

  • Hi Jagadeesh,

    You can follow the guidance here to move the databases:

    https://msdn.microsoft.com/en-us/library/ee378546.aspx

    And please follow below approach to configure applications

    1. Export each Application to an .msi and a binding file.
    2. Unconfigure the BizTalk Group
    3. Configure the BizTalk Group using the new Domain Groups
    4. Create Hosts and Instances with names matching the previous setup
    5. Import/Install the .msi's
    6. Import the bindings
    7. Start all applications


    Thanks, If my reply is helpful please mark as answer or vote as helpful.

    Friday, August 21, 2015 5:56 AM
    Moderator
  • Can you clarify.  Are you changing just the SQL Server or did you setup up two new BizTalk Server computers as well?

    If it's just the SQL Server:

    1. Follow the instructions in https://msdn.microsoft.com/en-us/library/ee378546.aspx?f=255&MSPPError=-2147217396
    2. You don't need to export/import or reconfigure anything, the apps are installed on the BizTalk computers, not the SQL Server.
    3. Be very careful and follow all the steps.
    4. Backup everything first.
    • Proposed as answer by Angie Xu Friday, August 28, 2015 2:52 AM
    • Marked as answer by Angie Xu Monday, August 31, 2015 1:48 AM
    Friday, August 21, 2015 11:29 AM
    Moderator

All replies

  • Hi Jagadeesh,

    You can follow the guidance here to move the databases:

    https://msdn.microsoft.com/en-us/library/ee378546.aspx

    And please follow below approach to configure applications

    1. Export each Application to an .msi and a binding file.
    2. Unconfigure the BizTalk Group
    3. Configure the BizTalk Group using the new Domain Groups
    4. Create Hosts and Instances with names matching the previous setup
    5. Import/Install the .msi's
    6. Import the bindings
    7. Start all applications


    Thanks, If my reply is helpful please mark as answer or vote as helpful.

    Friday, August 21, 2015 5:56 AM
    Moderator
  • Can you clarify.  Are you changing just the SQL Server or did you setup up two new BizTalk Server computers as well?

    If it's just the SQL Server:

    1. Follow the instructions in https://msdn.microsoft.com/en-us/library/ee378546.aspx?f=255&MSPPError=-2147217396
    2. You don't need to export/import or reconfigure anything, the apps are installed on the BizTalk computers, not the SQL Server.
    3. Be very careful and follow all the steps.
    4. Backup everything first.
    • Proposed as answer by Angie Xu Friday, August 28, 2015 2:52 AM
    • Marked as answer by Angie Xu Monday, August 31, 2015 1:48 AM
    Friday, August 21, 2015 11:29 AM
    Moderator
  • Hi Jagadeesh ,

    If you have log running transactions then approach of moving MSI wont work ,as it will have risk of transaction failure .

    The proper way of moving database is to run the BizTalk backup job(full and partial backup ) and then try to restore it to the destination sql box .

    To restore your databases

    1.On the destination system, open SQL Server Management Studio, and connect to your SQL Server.

    2.Expand SQL Server Agent, and expand Jobs. Do the following:

    a.Right-click the BTS Log Shipping - Get Backup History job and select Disable. The status changes to Success.

    b.Right-click the BTS Log Shipping - Restore Databases job and select Disable. The status changes to Success.

    c.Right-click the BTS Log Shipping - Restore To Mark and select Start Job at Step. Select Step ID 1 and select Start.

    When the status changes to Success, the SQL Server Agent jobs and BizTalk Server databases are restored to the destination system.


    System_CAPS_importantImportant

    If the Status is Error, select the link in the Message field to determine the cause. These jobs should have a Success status before continuing.

    3.On the BizTalk Server where you edited the SampleUpdateInfo.xml file, open a command prompt, and go to:

    32-bit computer: %SystemDrive%\Program Files\Microsoft BizTalk Server <version>\Schema\Restore

    64-bit computer: %SystemDrive%\Program Files (x86)\Microsoft BizTalk Server <version>\Bins32\Schema\Restore


    4.At the command prompt, type:

    cscript UpdateDatabase.vbs SampleUpdateInfo.xml

    This script updates all tables that store information about the location of other databases.


    System_CAPS_importantImportant

    Run UpdateDatabase.vbs on one server in the BizTalk group.

    On 64-bit computers, you must run UpdateDatabase.vbs from a 64-bit command prompt. Note that the default command prompt on 64-bit computers is a 64-bit command prompt and is located at %SystemDrive%\windows\System32\cmd.exe.

    If EDI is configured, then update the SampleUpdateInfo.xml file and the UpdateRegistry.vbs file:

     1. Open the SampleUpdateInfo.xml file for editing (%SystemRoot%\Program Files\Microsoft BizTalk Server <version>\Schema\Restore). Add the following text in the <OtherDatabases> section:

    <Database Name="MsEDIAS2" oldDBName="old dta db name" oldDBServer="old dta server" newDBName="new dta db name" newDBServer="new dta server" />

    Save the edited SampleUpdateInfo.xml file.

     2. Open the UpdateRegistry.vbs file for editing (%SystemRoot%\Program Files\Microsoft BizTalk Server <version>\Schema\Restore). Go to line 131 and replace @Name='EDI' with @Name='MsEDIAS2'. The new line looks like:

    set node = configObj.selectSingleNode("/UpdateConfiguration/OtherDatabases/Database[@Name='MsEDIAS2']")

    Save the edited UpdateRegistry.vbs file.

    5.Copy the edited SampleUpdateInfo.xml file to the following folder on every computer running BizTalk Server in this BizTalk group:

    32-bit computer: Copy to %SystemDrive%\Program Files\Microsoft BizTalk Server <version>\Schema\Restore

    64-bit computer: Copy to %SystemDrive%\Program Files (x86)\Microsoft BizTalk Server <version>\Bins32\Schema\Restore


    6.On each computer in the BizTalk Server group, open a command prompt, and go to:

    32-bit computer: %SystemDrive%\Program Files\Microsoft BizTalk Server <version>\Schema\Restore

    64-bit computer: %SystemDrive%\Program Files (x86)\Microsoft BizTalk Server <version>\Bins32\Schema\Restore


    7.At the command prompt, type:

    cscript UpdateRegistry.vbs SampleUpdateInfo.xml

    This script updates all registry entries that store information about the location of other databases.

    System_CAPS_importantImportant


    Run UpdateRegistry.vbs on every server in the BizTalk group.

    On 64-bit computers, you must run UpdateRegistry.vbs from a 64-bit command prompt. Note that the default command prompt on 64-bit computers is a 64-bit command prompt and is located at %SystemDrive%\windows\System32\cmd.exe.
     
    8.Restart all the BizTalk Server services. See How to Start, Stop, Pause, Resume, or Restart BizTalk Server Services.

    9.After restoring your databases, restart the Windows Management Instrumentation service:

    a.Open services.msc.


    b.Right-click Windows Management Instrumentation, and then select Restart.


    10.Open BizTalk Server Administration. Do the following:

    a.Right-click the BizTalk Group and select Remove.


    b.Right-click BizTalk Server Administration and select Connect to Existing Group.


    c.In SQL Server name, select the name of the SQL Server instance that hosts the BizTalk Management database. When you select the SQL Server instance, BizTalk Server automatically detects the BizTalk Server databases on that computer.


    d.In Database name, select your BizTalk Management database (BizTalkMgmtDb by default), and then select OK.


    The BizTalk Server Administration console adds the BizTalk group to the Administration console.

    Your BizTalk Server is now restored and should be running. Next, configure the Backup BizTalk Server job to start writing backups to a new destination server. You should also reconfigure a new destination system.


    System_CAPS_importantImportant


    If you are using the Rules Engine, after restoring the databases, you must restart the Rule Engine Update Service on every server in the BizTalk Server group. See How to Start, Stop, Pause, Resume, or Restart BizTalk Server Services.

    System_CAPS_noteNote


    If you are using BAM, this is the time to restore the BAM databases. See Backing Up and Restoring BAM.

    You can refer below MSDN article .

    https://msdn.microsoft.com/en-us/library/aa546753.aspx

    https://msdn.microsoft.com/en-us/library/ee406927.aspx

    https://msdn.microsoft.com/en-us/library/aa547565.aspx

    Thanks

    Abhishek

    Monday, August 24, 2015 7:17 AM
  • Sorry, but do not follow Abhishek0127's advice.

    The correct and only way to move the databases is to follow the instructions in the article:

    How to Move the BizTalk Server Databases

    Monday, August 24, 2015 11:14 AM
    Moderator