Move BizTalk Server Databases from one SQL server to another

In this article we will explain how to migrate or move BizTalk databases from one server to another server. Let’s assume we want to move our BizTalk primary databases from local server to remote server or any other server on the network. Same method can also be used to move databases to SQL server cluster.

Assumptions:

· You should have admin user account access to both server, source and destination server.

· You should have basic knowledge of SQL Server.

· You should have basic knowledge of configuring BizTalk Server.

Step by step procedure

There are few major steps to follow which are described below. We will also explain these steps in detail in later.

1. Stop all BizTalk server services.

a. Perform a partial stop of all BizTalk services, do not perform a full stop!

b. Stop all host instances.

c. Stop BizTalk server services.

i. BizTalkServerApplication

ii. Enterprise Single Sign-On Service

iii. Rule Engine Update Service

iv. Any other BizTalk service that is running

clip_image001

clip_image002

2. Stop the SQL Server Agent service.

clip_image003

3. Stop IIS.

4. Backup following BizTalk databases using Backup BizTalk Server job.

a. BAMPrimaryImport

b. BAMAlertsApplication

c. BAMAlertsNSMain

d. BizTalkDTADb

e. BizTalkMgmtDb

f. BizTalkMsgBoxDb

g. BizTalkRuleEngineDb

h. SSODB

For configuring Backup BizTalk Server job please follow instructions mentioned at https://msdn.microsoft.com/en-us/library/aa546765.aspx

you may need to run sp_ForceFullBackup before running the job.

5. Restore above backup databases on the remote server. – NOTE: do not use simple restore task, use cs script method instead.

Open SQL server management studio and restore all the backed up databases. In the SampleUpdateInfo.xml file, comment out all of the databases except for those you have moved to the new server. Edit C:\Program Files\Microsoft BizTalk Server 2006\Schema\Restore\SampleUpdateInfo.xml

a. Do a “Replace All” and change SourceServer to ….
b. Do a “Replace All” and change DestinationServer to ….

Copy the edited SampleUpdateInfo.xml file to the following folder, open a command prompt, and go to computer: %SystemDrive%\Program Files (x86)\Microsoft BizTalk Server <version>\Bins32\Schema\Restore and run

cscript UpdateDatabase.vbs SampleUpdateInfo.xml

Copy the edited SampleUpdateInfo.xml file to the following folder computer: Copy to %SystemDrive%\Program Files (x86)\Microsoft BizTalk Server <version>\Bins32\Schema\Restore open a command prompt, and go to computer: %SystemDrive%Program Files (x86)Microsoft BizTalk Server <version>Bins32SchemaRestore and run

cscript UpdateRegistry.vbs SampleUpdateInfo.xml

6. Script all SQL server users from source server and run those script on destination server.

7. Script all SQL server agent jobs from source server and run those script on destination server.

8. Restart Windows Management Instrumentation service under services from services.msc

9. Re configure BizTalk server administrator

a. Remove current BizTalk group

b. Connect to existing group

c. Configure destination SQL server by giving server name

d. Configuring databases

Your BizTalk Server databases is now moved or migrated to another database server and BizTalk should be running properly.

Feel free to contact in case of any query

References:

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

Author: Jahanzaib.khan@alliedc.com

Posted in BizTalk, SQL. 1 Comment »

SQL Server Restrict Memory Usage

EXEC sp_configure ‘show advanced options’, 1

RECONFIGURE WITH OVERRIDE

EXEC sp_configure ‘max server memory (MB)’, 200

RECONFIGURE WITH OVERRIDE

SQL: Get Comma Seperated Values

 

Select @CSV = isnull(@TRX_REF + ‘,’,”) + seqID from table

Calling webservice from SQL 2000

 Sending  XML data over HTTP to a web service, using XMLHTTP object

 Sqlteam.com/forums/topic.asp?topic_ID=63292

SQL: Group data by weeks

– Group by weeks
starting on Mondays

select count(OrderID) as numOrders,

min(dateadd(day, datediff(day,’19000101′,OrderDate)/7*7, ‘19000101’)) as WeekStarting,

min(OrderDate) as EarliestOrder, max(OrderDate) as LatestOrder from
Northwind..Orders

group by datediff(day,’19000101′,OrderDate)/7

order by WeekStarting

 

via [SQLTeam]

 

SQL Server: Data Source name not found

Recently, I had a problem when SQL Server 2000 Enterprise Manager fails to register (local)database; the error message was “Data Source name not found and no default driver specified”

 

 

This was a problem with faulty installation of SQL Server or somehow ODBC/Data Access Components configurationgone bad. The solution thatworked in my case is to export and import HKLM\Software\ODBC\ODBC.INST\SQL Server registry key from a normal system with ODBC drivers installed to the faulty SQL machine’s registry

 

A Visual Explanation of SQL Joins