BizTalk Server General - Synchronizing BizTalk database backup with application database backup

Author

Synchronizing BizTalk database backup with application database backup

McGeeky

2005-05-03, 5:52 pm

I am planning the database backup strategy for our system. I know that
BizTalk has database backup stored procedures that we can take advantage of
that ensure that all the BizTalk databases are backed up as a set.

We also have an application specific database that also needs backing up.
But this must also be kept in sync with the BizTalk databases during a
backup. How can I make sure that happens? Has anyone done that before?

--
McGeeky
http://mcgeeky.blogspot.com

Joel West

2005-05-04, 5:54 pm

Yes, this is possible (and supported). When you say that you need your
database to be "in sync" with BTS I assume that this is because you are
participating in a DTC that spans a BTS db and your db (see SQL books
online for background on log marks). If this is not the case you can still
incorporate your db into the BTS backup process but there won't be any
synchronization.

Here are the steps to add a 3rd party database to our backup process:

Build the objects in the new database:
1. Locate the <install dir>\Microsoft BizTalk Server 2004\Schema dir and
run Backup_Setup_All_Procs.sql and Backup_Setup_All_Tables.sql in the
target database. This creates the necessary procs, table and role and
assignees permissions to the stored procs.
Configure:
2. Link the Sql Server holding the Mgmt database to the Sql Server holding
the new database. The account used to run the Sql Agent service on the
Mgmt Sql Server must be either a domain account or a local account that is
mapped to each machine holding a database to be backed up. If the
databases are on the same machine skip this step.
3. Add a login on the Sql Server that holds the new database for the
account running the Sql Agent service on the Mgmt Sql Server. If the
databases are on the same machine skip this step.
4. Add a user in the new database for the login created in the previous
step and add them to the BTS_BACKUP_USERS role. This role is created and
granted execute on the necessary procs by the scripts in step 1.
5. Add the database to the BizTalkMgmtDb..adm_OtherBackupDatabases table:
DefaultDatabaseName - the name of your database
DatabaseName - the name of your database (yes,
DefaultDatabaseName and DatabaseName hold the same values for this
scenario).
ServerName - the name of the sql server that holds your
database
BTSServerName - the name of the server that holds the
Management database.

As for testing to know whether or not your database is in sync - go through
the restore process outlined at
http://www.msdn.microsoft.com/libra...y/en-us/operati
ons/htm/ebiz_ops_backuprestore_fexk.asp for all databases. This is an
important process to rehearse to make sure that your backup
process/disaster recovery plans work as expected. If you can correctly
restore your database to the same mark name as the other BTS databases all
of your state that is based on distributed transactions shared with BTS
should be in sync with the state in the BTS dbs.

One other thing - you mention "backup stored procedures". Just an FYI -
the only support method of kicking off the BTS backups is through the
Backup BizTalk Server Sql Agent job.

Thanks,
Joel

--------------------
| From: "McGeeky" <anon@anon.com>
| Subject: Synchronizing BizTalk database backup with application database
backup
| Date: Tue, 3 May 2005 15:37:07 +0100
| Lines: 14
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <ujBDe4#TFHA.616@TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.biztalk.general
| NNTP-Posting-Host: 194.129.182.225
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.biztalk.general:13511
| X-Tomcat-NG: microsoft.public.biztalk.general
|
| I am planning the database backup strategy for our system. I know that
| BizTalk has database backup stored procedures that we can take advantage
of
| that ensure that all the BizTalk databases are backed up as a set.
|
| We also have an application specific database that also needs backing up.
| But this must also be kept in sync with the BizTalk databases during a
| backup. How can I make sure that happens? Has anyone done that before?
|
| --
| McGeeky
| http://mcgeeky.blogspot.com
|
|
|
|

McGeeky

2005-05-04, 5:54 pm

Hey Joel - that's fantastic! Thanks for all that information! Its just what
we wanted to hear!

The application database is being updated exclusively by the SQL Adapter
from BizTalk so I am presuming DTC will be taken care of... or not?

--
McGeeky
http://mcgeeky.blogspot.com

"Joel West" <jdwest@mail.microsoft.com> wrote in message
news:zfvofcMUFHA.3336@TK2MSFTNGXA01.phx.gbl...
> Yes, this is possible (and supported). When you say that you need your
> database to be "in sync" with BTS I assume that this is because you are
> participating in a DTC that spans a BTS db and your db (see SQL books
> online for background on log marks). If this is not the case you can
still
> incorporate your db into the BTS backup process but there won't be any
> synchronization.
>
> Here are the steps to add a 3rd party database to our backup process:
>
> Build the objects in the new database:
> 1. Locate the <install dir>\Microsoft BizTalk Server 2004\Schema dir and
> run Backup_Setup_All_Procs.sql and Backup_Setup_All_Tables.sql in the
> target database. This creates the necessary procs, table and role and
> assignees permissions to the stored procs.
> Configure:
> 2. Link the Sql Server holding the Mgmt database to the Sql Server
holding
> the new database. The account used to run the Sql Agent service on the
> Mgmt Sql Server must be either a domain account or a local account that is
> mapped to each machine holding a database to be backed up. If the
> databases are on the same machine skip this step.
> 3. Add a login on the Sql Server that holds the new database for the
> account running the Sql Agent service on the Mgmt Sql Server. If the
> databases are on the same machine skip this step.
> 4. Add a user in the new database for the login created in the previous
> step and add them to the BTS_BACKUP_USERS role. This role is created and
> granted execute on the necessary procs by the scripts in step 1.
> 5. Add the database to the BizTalkMgmtDb..adm_OtherBackupDatabases table:
> DefaultDatabaseName - the name of your database
> DatabaseName - the name of your database (yes,
> DefaultDatabaseName and DatabaseName hold the same values for this
> scenario).
> ServerName - the name of the sql server that holds your
> database
> BTSServerName - the name of the server that holds the
> Management database.
>
> As for testing to know whether or not your database is in sync - go
through
> the restore process outlined at
>
http://www.msdn.microsoft.com/libra...y/en-us/operati
> ons/htm/ebiz_ops_backuprestore_fexk.asp for all databases. This is an
> important process to rehearse to make sure that your backup
> process/disaster recovery plans work as expected. If you can correctly
> restore your database to the same mark name as the other BTS databases all
> of your state that is based on distributed transactions shared with BTS
> should be in sync with the state in the BTS dbs.
>
> One other thing - you mention "backup stored procedures". Just an FYI -
> the only support method of kicking off the BTS backups is through the
> Backup BizTalk Server Sql Agent job.
>
> Thanks,
> Joel
>
> --------------------
> | From: "McGeeky" <anon@anon.com>
> | Subject: Synchronizing BizTalk database backup with application database
> backup
> | Date: Tue, 3 May 2005 15:37:07 +0100
> | Lines: 14
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | Message-ID: <ujBDe4#TFHA.616@TK2MSFTNGP12.phx.gbl>
> | Newsgroups: microsoft.public.biztalk.general
> | NNTP-Posting-Host: 194.129.182.225
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.biztalk.general:13511
> | X-Tomcat-NG: microsoft.public.biztalk.general
> |
> | I am planning the database backup strategy for our system. I know that
> | BizTalk has database backup stored procedures that we can take advantage
> of
> | that ensure that all the BizTalk databases are backed up as a set.
> |
> | We also have an application specific database that also needs backing
up.
> | But this must also be kept in sync with the BizTalk databases during a
> | backup. How can I make sure that happens? Has anyone done that before?
> |
> | --
> | McGeeky
> | http://mcgeeky.blogspot.com
> |
> |
> |
> |
>

Joel West

2005-05-09, 5:49 pm

Jacques Lederer

2005-09-28, 5:59 pm

Thank you very much for the very clear explanations. I do have a specific
question. I have noticed an oddity regarding the adm_OtherBackupDatabases
table. I had to go into this because I have one SQL server (BTPDATA) and 2
BTS servers (BTP1 and BTP2).
By default, in the adm_OtherBackupData there were 4 rows like this:
EDI BizTalkEDIDb BTPDATA BTP1
EDI BizTalkEDIDb BTPDATA BTP2
HWS Administration DB BizTalkHwsDb BTPDATA BTP1
HWS Administration DB BizTalkHwsDb BTPDATA BTP2
This was misleading because for these 4 lines it created 4 full backups (not
even talking about the incremental ones) when I only need 2 for these 2 DBs.

When I looked into it, I realized that only the columns DataBaseName and
ServerName were used...
In the sp_BackupAllFull and sp_MarkAll stored procs it uses:
"select DatabaseName, ServerName
from adm_OtherBackupDatabases
group by DatabaseName, ServerName"
So I get my adm_OtherBackupData now like this:
EDI BizTalkEDIDb BTPDATA BTP1
HWS Administration DB BizTalkHwsDb BTPDATA BTP1
My1stDB FirstDB BTPDATA BTP1
My2dDB SecondDB BTPDATA BTP1
etc.

(I suppose it doesn't matter whether I write BTP1 or X)
This seems to work fine.
Let me know if I missed something, otherwise it might help others. My own
DBs are all on the same SQL server (BTPDATA).

Regards,
Jacques