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.

Wait for all remaining transactions to be replaced.
Run this Replication Server command:
```
admin who, sqm
```
Find the entry that corresponds to the inbound queue for this database by looking for the Info field for the queue_number, queue_type entry. For an inbound queue, the queue type is 1. Note the last segment:block entry for the queue.
Open the queue dump file by executing the following Replication Server command, where file_name is the name of the dump file you will dump to:
```
sysadmin dump_file, "file_name"
```
Use isql to update one row in a single replicated table in the primary database:
```
update table set column = column 
   where key = unique_value
```
The update command helps to track whether all modifications to the replicated database have been sent to the Replication Server.

If you have Replication Server version 11.0 or later, choose a table that does not use the replicate minimal columns clause, or use the alter replication definition command...replicate all columns command to change the replication definition before updating the row. If you alter the replication definition, be sure to change it back after you complete this upgrade procedure.
In the primary Replication Server, execute the admin who, sqm command until the last segment:block entry for the inbound queue changes.
Execute the following Replication Server command to dump the last block of the inbound queue to the dump file you created in step 3:
```
sysadmin dump_queue, queue_number, queue_type, 
   last_seg, block, 1
```
Use the queue_number, queue_type, last_seg, and block values found in the output of the admin who, sqm command in step 5.
Examine the dump file to make sure it contains the transaction corresponding to the update you performed in step 4 (you can use Notepad to examine the file).
Repeat steps 5 through 7 until the transaction corresponding to the update is in the dump file. After draining the transaction logs, do not allow any other activity in the databases. If activity does occur, you need to redrain the logs.

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.
Use Sybase Central to shut down the servers.

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.