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 produ

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

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.

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