Upgrading SQL Server databases to Adaptive Server databases

If you are upgrading SQL Server databases in your replication system to Adaptive Server 12.5.1, use the database upgrade procedure as follows.

Suspend transaction processing and replication system activities.
Drain transaction logs for primary databases.
Drain the RSSD transaction log.
Disable the log truncation point.

WARNING! Sybase strongly recommends you perform a dump database and dump transaction before executing the steps below.

Suspending transaction activity in the databases and suspending replication

Complete the following tasks before upgrading version 4.9.x or 10.0.x SQL Servers in your replication system to Adaptive Server 12.5.1.

Before you upgrade databases to Adaptive Server, suspend transaction activity in the databases and suspend replication.

Replication includes the creation and dropping of both routes and subscriptions.

Verify that the subscriptions you are creating with the create subscription command, with primary data in the databases being upgraded, have reached a “valid” state at the primary Replication Server.

Do not upgrade while the above subscriptions are being created.

Make sure no users create subscriptions for the data in the database you are upgrading until the upgrade procedure is finished.
Run rs_helproute in each Replication Server System Database (RSSD) being upgraded to determine each RSSD’s status.

The status of all routes should be “Active.” See Chapter 5, “Managing Routes” in the Replication Server Administration Guide to resolve route problems.
Shut down the applications that are using the databases you are upgrading.
Use the admin who command in Replication Server to identify the existing Data Server Interface (DSI) connections to the data server being upgraded.
Suspend all DSI connections to non-RSSD databases you are upgrading by using the following command for each database:
```
suspend connection to dataserver.database
```
Leave the DSI connections for the RSSDs running.

Draining transaction logs for primary databases

For each primary database you are upgrading, follow these steps to ensure that the Replication Server completely processes the pre-upgrade log.

Use the logmon script located in the $SYBASE/upgrade directory on the data server you are upgrading to drain the logs. As the logs are being drained, you are prompted to start and stop Replication Servers and LTMs.

Before running the logmon script, set your SYBASE environment variable to the location of your current SQL Server. You must execute logmon using the full path name of your current SQL Server installation. After running the logmon command, set the SYBASE environment variable back to the location of your new Adaptive Server.

Use the following for logmon:
```
logmon -S server_name -U user_name -P password 
   [-l file_name] [-help] [-version]
```
Where:
- server_name – is the SQL Server to be upgraded.
- file_name – is the file into which the LTM restoration scripts are to be written. You use this file to reenable replication after the upgrade, as described in “Restoring Functionality in Adaptive Server” in Chapter 5 of the Adaptive Server Installation Guide.
- -help – displays the help screen, and the -version option displays the version of the logmon script.
When the transaction logs are drained, logmon displays the full path name of the file logmon.server_name, which you need later to reenable replication after upgrading.

Manually draining the RSSD transaction log

Manually drain the transaction log of each primary RSSD in the data server you are upgrading.

If Replication Server has routes to other Replication Servers, you must make sure that Replication Server processes all transactions in the RSSD transaction log before you upgrade the databases.

You can make sure the transaction log is completely processed by creating a replication definition in the primary Replication Server and then watching for it to appear in the replicate Replication Server’s RSSD. When the replication definition is in the replicate RSSD, the log is fully processed.

Creating a replication definition for ensuring that the RSSD log is processed

Log in to the primary Replication Server.
Create a temporary replication definition:
```
create replication definition rep_def_name 
   with primary at dataserver.database 
   (column_a int) 
   primary key (column_a)
```
The data server and database names must be valid, but the replication definition does not have to reference an actual table.
Log in to the replicate RSSD.
Execute the following query to see whether the replication definition has arrived from the primary RSSD:
```
select * from rs_objects 
   where objname = "rep_def_name"
```

When the replication definition has arrived in the replicate RSSD, the RSSD transaction log has been drained.

Disabling the LTM truncation point

When you upgrade a primary database, the LTM must not be running and the LTM truncation point should be turned off for the duration of the upgrade.

Shut down the LTMs for the databases you are upgrading.
Shut down Replication Servers for the RSSDs you are upgrading.
In each primary RSSD, execute the following commands to turn off the LTM truncation point:
```
1> use database 
2> go 
1> dbcc settrunc ("ltm", "ignore") 
2> go
```
If the dbcc settrunc command fails because the server “context” is reserved, execute the following commands to allow SQL Server to recognize that the LTM is disconnected:
```
1> begin tran 
2> commit tran 
3> go 500
```
Then turn off the LTM truncation point:
```
dbcc settrunc ("ltm", "ignore")
```
Repeat step 3 for each primary database and each primary RSSD.

Draining each replicated primary

Manually drain the transaction log of each replicated primary in the data server you are upgrading.

Note the timestamp of the last log page:
```
1> use database 
2> go 
1> dbcc traceon (3604) 
2> go
```
Trace output is sent to the terminal.
```
1> select root from sysindexes where name = "syslogs"
2> go
```
The value returned is the last log page.
```
1> dbcc page (database, last_log_pageid, 0) 
2> go
```
The timestamp is a 6-byte hexadecimal number. Save this value for reference in step 3.

Append to the log:

1> use database 
2> go 
1> begin tran 
2> commit tran 
3> go 500

Check the LTM truncation point:
```
1> use database 
2> go 
1> dbcc gettrunc 
2> go
```
The value in the ltm_trunc_page column is the LTM truncation point.
```
1> dbcc traceon (3604) 
2> go
```
Trace output is sent to the terminal.
```
1> dbcc page (database, ltm_trunc_page, 0)
2> go
```
Note the timestamp. Continue with this step until the timestamp of the LTM truncation page is greater than the timestamp value you saved.

Shut down the LTM for this replicated database, and then disable the LTM truncation point.
```
1> use database 
2> go 
1> dbcc settrunc ("ltm", "ignore") 
2> go
```
Suspend the connection to the RSSD databases from Replication Server.
```
1> suspend connection to dataserver.database 
2> go
```

Restoring replication after upgrade

Zero out the locator in the RSSD for each replicated primary.
Using isql, connect to the RSSD and execute the following commands:
```
1> use RSSD 
2> go 
1> rs_zeroltm dataserver, RSSD 
2> go
```
Set the LTM truncation point to “valid” in each replicated primary. Using isql, connect to the replicated primary database, and execute the following commands:
```
1> use database 
2> go 
1> dbcc settrunc ("ltm", "valid") 
2> go
```

Shutting down Replication Servers and LTMs

Shut down Replication Servers and LTMs for the database you are upgrading.

Granting replication roles (upgrading from 10.0.x only)

Grant the replication_role to each login account that the LTM uses to scan logs from SQL Server by using the following command:
```
sp_role "grant", replication_role, login_name
```

Upgrading pre-11.0 SQL Server to Adaptive Server

Execute the logmon.server_name script to reenable replication. Your SYBASE environment variable should be set to the new Adaptive Server. You are prompted for the RSSD name, RSSD server name, RSSD username, and RSSD password.