Setting up Log Shipping MSSQL 2008

 – First you need to create the backup/log shipping directory at the primary and you need to share this folder and add full permissions for the primary dataserver SQL Server AND SQL Agent service accounts and read permissions for the secondary dataserver’s SQL Server AND SQL Agent service accounts.

– You need to repeat this at the destination server giving read access to the primary server sql server service account and read/write to the secondary dataserver’s SQL Agent AND SQL Server service accounts.

– You’ll next need to create the secondary database, this obviously needs to be sized the same as the primary database and make it simple recovery mode.

– You can now fire up the log shipping wizard from the primary dataserver by ticking the log shipping radio button under properties for the database. NOTE: The wizard is a bit rubbish and should not be used to set up log shipping, but it is useful for generating the various stored procs which need to be run on the primary and secondary dataservers.

– The wizard is fairly self explanatory, You select the Primary dataserver log shipping directory as the backup folder (use network share path). Also and VERY IMPORTANT make sure that you select “No, the secondary database is initialised” in the Initialize Secondary database tab. Also select the secondary logshipping directory(configured in step 2 above) as the “destination folder for copied files” in the Copy Files tab. The final step is to select Standby mode and “disconnect users” from the Restore transaction log tab.

– Click the button “Script Configuration” to output all this information to a new query window and save it, you can then close the wizard.

– First make sure the primary database is set to Full logged mode in properties then back it up into the log shipping directory created earlier.

– Copy this dump across to the secondary server log shipping directory.

– Now from the secondary server load the database previously created from this backup. Make sure you leave this database in RESTORE WITH STANDBY mode! Place the standby file in the secondary server logshipping directory.

– On the primary server run the following commands from the script you generated earlier; sp_add_log_shipping_primary_database to add a primary database. sp_add_schedule to add a schedule for the backup job, sp_attach_schedule and sp_update_job.

– (Optional) On the monitor server, execute sp_add_log_shipping_alert_job to add the alert job.

– On the secondary server, execute the jobs from the script, sp_add_log_shipping_secondary_primary, sp_add_schedule and sp_attach_schedule for the copy and restore jobs, and finally the  sp_add_log_shipping_secondary_database followed by 2 sp_update_job procs for copy and restore

– On the primary server, execute sp_add_log_shipping_alert_job and sp_add_log_shipping_primary_secondary to add the required information about the new secondary database to the primary server and get things up and running.

Performance Tuning MSSQL

MSSQL has many excellent tools which can be used for discovering performance bottlenecks.

Sp_monitor and dbcc perfmon

To get a snapshot of what the SQL Server is doing then these commands are very usefull.

Run sp_monitor twice with a gap of 30 seconds in between to get a high level overview of how busy the sql server is.

dbcc perfmon will give you much more detailed information on the state of the sql server but the command has been supervised by various SQL Server counters which can be used in the System monitor.

SSMS Reports

Sql Server has a set of excellent reports which are generated from the Dynamic Management Views, these are both at the server and database level. If you right click on the server or an individual database in the SQL Server Management Studio then you can select which report to run. You can even export the report output to a pdf. This will highlight any problems which you can then focus on.

System monitor

To drill down further the first port of call should be system monitor or perfmon, which is started by typing perfmon into the run box.

One excellent feature of this is that you can export to a Counter Log at the same time as running a SQL Profiler session, (discussed later), you can then merge them which will let you see how bad queries affect the CPU, memory etc or vice versa.

The objects to normally check for in system monitor are:

Memory, Network Interface, Physical Disk, Processor, SQLServer:Buffermanager, SQLServer:Databases, SQLServer:Exec Statistics, SQLServer:General Statistics, SQLServer:Latches, SQLServer:Locks, SQLServer:Memory Manager, SQLServer:PlanCache, SQLServer:SQL Statistics and System.

SQL Profiler

The system monitor might highlight the bad performance but you will typically need to drill down a bit further to find the bad queries which are causing the problems. To do this you use SQL Profiler.

First step is to establish a performance baseline as follows;

–              Start Profiler, Select file, New trace and connect to the relevant instance.

–              Specify a trace name, template(standard) and a location to save the trace file too. Set the maximum fiel size to 50MB and enable file rollover.

–              In the events selection tab remove Audit Login, Audit Logout, ExistingConnection and SQL:BatchStarting radio boxes.

–              Click on show all columns and select the TextData, NTUserName, LoginName, CPU, Reads, Writes, Duration, SPID, StartTime, EndTime, BinaryData, DatabaseName, ServerName and ObjectName coulumns.

–              Click on Column Filters and select the Database name to be the database you are interested in.

–              Click Run to start the trace and simultaneously start the System Monitor Counter Log.

Correlating the counter Log with Profiler Trace

–              Start Profiler, Select File, Open and select the trace file you created from the Performance baseline previously.

–              Next go to File, select Import Performance Data and select the Counter Log file you created simultaneously with the performance baseline trace.

–              In the performance Counters Limit Dialog window select Network Interface: Output Queue Length, Processor:% processor Time, System:Processor Queue Length and SQLServer:Buffer Manager:Page life expectancy. Click OK.

–              You can then scroll through the trace and find what was happening at peak activity on the Windows machine and also what effect certain commands had on the overall server performance.

Improve RepServer performance

— Various parameters that can improve the performance of the Replication server
rs_configure memory_limit, ‘200’

— dsi_sqt_cache_size
alter connection to DCOCCDWP.defacto set dsi_sqt_max_cache_size to ‘62914560’
suspend connection to DCOCCDWP.defacto
resume connection to DCOCCDWP.defacto

— Cache system tables
configure replication server set sts_full_cache_rs_classes to ‘on’
configure replication server set sts_full_cache_rs_databases to ‘on’
configure replication server set sts_full_cache_rs_columns to ‘on’
configure replication server set sts_full_cache_rs_config to ‘on’
configure replication server set sts_full_cache_rs_datatype to ‘on’
configure replication server set sts_full_cache_rs_functions to ‘on’
configure replication server set sts_full_cache_rs_objects to ‘on’
configure replication server set sts_full_cache_rs_publications to ‘on’
configure replication server set sts_full_cache_rs_queues to ‘on’
configure replication server set sts_full_cache_rs_repdbs to ‘on’
configure replication server set sts_full_cache_rs_repobjs to ‘on’
configure replication server set sts_full_cache_rs_diskaffinity to ‘on’
configure replication server set sts_full_cache_rs_routes to ‘on’
configure replication server set sts_full_cache_rs_systext to ‘off’
configure replication server set sts_full_cache_rs_sites to ‘on’
configure replication server set sts_full_cache_rs_version to ‘on’
configure replication server set sts_full_cache_rs_users to ‘on’
configure replication server set sts_full_cache_rs_translation to ‘on’
rs_configure sts_cachesize, ‘3500’

/* REP15 extra settings:
— Parallel dsi
alter connection to DCOCCDWP.defacto set parallel_dsi to ‘on’
suspend connection to DCOCCDWP.defacto
resume connection to DCOCCDWP.defacto

rs_configure exec_sqm_write_request_limit, ‘983040’
rs_configure md_sqm_write_request_limit, ‘983040’
rs_configure smp_enable, ‘on’

— Also a good idea to use disk affinity to bind connections to their own partitions:
— e.g alter connection to DCO.ProductCatalog_new set disk_affinity to ‘P08’

Sybase ASE upgrade process

1.       First you need to create a new directory on Unix for the installation, e.g /progs/fps/sybase15, and copy the installation tar file to the unix machine, and uncompress and untar it. If there is no room any other place on the machine just place it into a subdirectory under sybase15.

2.       Make sure that the os has the necessary patches, if not arrange for them to be installed.

3.       You will need to ask the Unix sa to create a new raw device for you,  sybsystemdbdev of about 128MB for the new sybsystemdb database, e.g /dev/vg_fps/rlv_systemdbdev and also check the size of the sybsystemprocs device, it should be at least 160MB in size, if it’s less ask the Unix sa to create a new raw device for you, e.g /dev/vg_fps/rlv_sysprocsdev2

4.       Run setup to install ASE15 into its own separate directory (setup -console ), e.g sybase15, answer no to the question of licenses, we will fix this later on. So specify:

–          Change the installation directory to e.g /progs/fps/sybase15

–          Select Full installation

–          If asked about licenses select Continue installation without a license key

–          Enterprise Edition

–          SR :  Server License

–          The answer yes to continue without license.

–          No to configure email alerts

Check that all the options are correct, especially the install directory, and click 1 to install the files.

–          Answer NO to the remember password feature question.

–          Deselect all the servers from configuration, we are not building the server now.

5.       You should install the latest patch for this version of ASE which can be downloaded from, just remove the original installation files and replace them with the patch files then run the patch. Do the patching after the sybase server is upgraded.

6.       Make sure the existing Sybase server is sourced and is displayed in DSQUERY etc then run the script /progs/fps/sybase15/ASE-15_0/upgrade/preupgrade specify the sa password for the existing server to check that everything is in place for an upgrade. This programme will let you know if you need to increase any parameters etc and also if you need to expand any databases. Answer NO at the end when it asks if you want to proceed with the upgrade, this is just a precheck. You will most probably need to extend sybsystemprocs and sybsystemdb onto the new unix devices created earlier and also extend model to at least 4MB, in fact all user databases must be at least 4MB. If everything is fien then the last two lines should say:

Preupgrade of Adaptive Server to 15.0 is complete.

Upgrade eligibility test succeeded.

7.       You now need to add the following 3 environment variables to the SYBASE.csh file:

OLDSYBASE= current SYBASE variable



8.       Source the new SYBASE.csh and create a resource file in $SYBASE/$SYBASE_ASE/init/sample_resource_files from the template where you fill in the values for the old $SYBASE path,  Sybase server name, Login (sa), and the password for current sa login on existing server.

9.       Turn off auditing on the existing sybase server and completely uninstall the sybsecurity database and sybaudit and reboot the Sybase server. Do an sp_configure “auditing”, 1 to make sure the auditing subsystem is switched off, VERY IMPORTANT!!

10.   You then run the upgrade with $SYBASE/$SYBASE_ASE/bin/sqlupgraderes –r $SYBASE/$SYBASE_ASE/init/sample_resource_files/<resource file>

Upgrading Rep server to REP15.0.2

Upgrade the RSSD database, start with the ID server.

  • Install the new software for replication server version 15 on your system. Put it in a new directory e.g /progs/torigodb/syb_rs15. After gunzipping and untarring the rep server tar ball run ./setup –console:

–          Accept the license agreement and select Full installation.

–          Answer no to “Will licenses be obtained from the License server”.

–          No to configure email alerts

–          No to start a sample rep server

  • You can then install any required Sybase patches, e.g ESD1
  • Generate a license key (through and store it in the SYSAM-2_0/licences directory.
  • Copy any custom scripts over from the old repserver, e.g .cshrc, repscripts etc, and modify as necessary, i.e change SYBASE path etc.
  • Run “rs_helpsub” from the rssd database to verify that all subscriptions are valid. The status only needs to be Valid at the replicate replication server.
  • Copy the interfaces file (or interfaces) from the pre version 15 to the version 15 installation.
  • Log on the RSSD ASE server and grant the primary user the sa_role “grant role sa_role to <username>” if it doesn’t already have it. You can determine the name of the primary user by looking into the configuration file, at the keyword RSSD_primary_user.
  • Log on to the replication server and run “admin health” to see if everything is ok.
  • Shutdown the replication server, backup the RSSD database then restart the replication server.
  • Leave repserver running.
  • Set the environment variable $SYBASE to the new REP15 installation directory
  • Set the environment variable SYBASE_REP to REP-15_0
  • Start $SYBASE/$SYBASE_REP/install/rs_init (rs_init)
  • Select “Configure a Server product” -> “Replication server” -> “Upgrade an existing replication server”.
  • Answer all the questions. The configuration file should be the path and filename of the old config file.
  • Do Ctrl-A to start.

After a succesfull upgrade

  • Repoint the replication server paths to the new installation and copy the old runserver file to it’s new location. Make sure to change the paths in the runserver file to pick up the new version 15 executable and correct config files etc. Also copy over the old config file.
  • Fix or copy any other files bespoke files, You need to update the file $SYBASE/config/objectid.dat file and add a line similar to

Installing Heterogeneous Replication

To perform any heterogeneous replication, replication to and from two different datasources e.g Sybase to Oracle or DB2 to Oracle, you need to use Direct Connect or as it’s now being called Enterprise Connect Data Access.

The below example is how to set up a connection to a new Oracle database but it can be applied to other databases as well obviously with some different scripts etc.

In the below example Direct connect is already installed but unloading the software is not so difficult anyway, please refer to product manuals for details.

1.       First you will need to create a new Direct Connect process by creating a new config file in the $SYBASE/$SYBASE_DCO/install directory on the server where Direct Connect is installed. Just copy the existing .cfg file and RUN file and alter to them to reflect the new settings. So in the RUN file just change the servername and in the .cfg file add the new servername, username (in this case the oracle username), the path to the new errorlog file and the connect_string.

2.       Add an entry for this new Direct Connect process in the local interfaces file like:


master tcp ether p-***-tm16 ****

query tcp ether p-***-tm16 ****

3.       Also add this into the interfaces file for the REPServer and the ASE server which is acting as the RSSD for the Repserver.

4.       Add the Oracle connection information into the tnsnames.ora file which is in $SYBASE/$SYBASE_DCO/network/admin :




(ADDRESS = (PROTOCOL = TCP)(HOST = ***.**.***.**)(PORT = ****))






5.       You can now start the new Direct Connect process by running e.g startserver –f RUN_DCODWMU.

6.       You can check that it is working by trying to log into the oracle tablespace with e.g isql –Udefacto –SDCOCCDWP –P<oracle password>.

7.       You now need to go to the Repserver and make a copy of the following files in the $SYBASE/$SYBASE_REP/scripts directory :






For the first three scripts you need to edit the scripts and enter your RSSD database name and execute the scripts against the relevant ASE server housing the RSSD database for the Repserver, e.g isql –Usa – Sdhp_SOLP –DREP_dhpsolo_RSSD -i$SYBASE/$SYBASE_REP/scripts/DCOCCDWP_ hds_oracle_udds.sql

The script hds_oracle_setup_for_replicate.sql needs to be copied over to the direct connect unix box and is run via Direct Connect against the replicate database, e.g isql -Ucm –P<oracle password> -SDCODWMU -ihds_oracle_setup_for_replicate.sql

8.       The last step is to create the connection to the Oracle database from the repserver and that is done by modifying the hds_oracle_connection_sample.sql script so that it looks similar to:

create connection to DCOCCDWP.defacto

set error class to rs_sqlserver_error_class

set function string class to rs_oracle_function_class

set username to *******

set password to **********

set ‘batch’ to ‘off’


You then run this script against the repserver.

9.       To test that everything is ok you can try to connect to the oracle database form the replication server with e.g  isql –Udefacto –SDCOCCDWP –P<oracle password>

If this connects then everything should work, at least in terms of connectivity. If it doesn’t work then you will probably have to open up the firewall both ways between the replication server port and the direct server port, e.g between pkg_solp (***.**.***.***) repserver port (2040) and dhp-tm16 (**.***.*.**) dco port (****).

10.   You then continue to define the rep defs, subscriptions etc.

How to add/update a table for replication

How to add/update a table for replication

–          First you need to create two scripts one to drop the existing subscription and replication definition and one to create the new table, replication definition and subscription.

–          You also need to extract the exiting replicant table and also a user, e.g.  origomw, if requested and save this info.

–          Before stopping replication send an email to operations to let them know of the planned downtime.

–          At this point you should suspend the replication server connection from DB2, if it’s not already down and wait 5 minutes to make sure everything is applied.

–          Do a select count(*) against an existing replicant table and compare this with a wc –l of the bcp file to make sure they match in number. For a new table this is not relevant.

–          Bcp out the existing table just in case something goes wrong.

–          Drop a user if that was requested

–          Drop subscription, rep def in rep server:

o   drop subscription DB2_ASN2_RESOURCE_NB_s2 for DB2_ASN2_RESOURCE_NB_d2

o   with replicate at origo_test.deFaktoReplica

o   without purge

o   go


o   drop replication definition “DB2_ASN2_RESOURCE_NB_d2”

o   go

–          Drop the table in ASE

o   Use database deFaktoReplica

o   Go

o   Drop table ASN2_RESOURCE_NB

o   Go

–          Create the table again

–          Bcp the data in with a command like:

o   bcp deFaktoReplica.dbo.ASN2_RESOURCE_NB  in /home/origo/deFaktoReplica/init/data/SYS3.ASN2.DDX -r ” \n” -t “\t” -c -Jiso_1 -Sorigo_test –Usa –P<password> -e bcplog.txt -z -b 10000 -m 100000

o   If you get problems check that the number of columns etc match and also check to see whether the row/tab delimiter is correct, sometimes there is an extra tab in the row delimiter, in that case try using –r “\t \n”.

–          Add the primary key, triggers and other indexes etc into the table, and possibly also a rep server specific column like changed_date.

–          Add the origomw user to the database.

–          Add the replication definition and subscription.

o   create replication definition “DB2_ASN2_RESOURCE_NB_d2”

o   with primary at TSTA.P825RAD1

o   with primary table named “DB2_ASN2_RESOURCE_NB”

o   with replicate table named “ASN2_RESOURCE_NB”

o   (

o   “RESR_ELMT_ID”          int,

o   “RECORD_EFF_END_DT”     datetime,

o   “RECORD_EFF_END_TM”     datetime,

o   “RECORD_EFF_STR_DT”     datetime,

o   “RECORD_EFF_STR_TM”     datetime,

o   “SRVC_LOC_ID”           numeric,

o   “RESR_TYPE”             char(6),

o   “RESR_ID”               char(22),

o   “ACCT_ID”               numeric,

o   “RESR_EFF_STR_DT”       datetime,

o   “RESR_EFF_END_DT”       datetime,

o   “ORD_ITEM_ID”           numeric,

o   “FST_USG_DT”            datetime,

o   “REINSTATE_CD”          char(4),

o   “REINSTALL_DT”          datetime,

o   “DISCN_CD”              char(4),

o   “ASN_RESR_DISCN_DT”     datetime,

o   “VERBAL_TRANSLATION”    char(20),

o   “COMMENT_ID”            numeric,

o   “REF_SEQ_NUM”           numeric,

o   “INIT_INSTALL_DT”       datetime,

o   “LAST_CHANGE_DT”        datetime,

o   “RESR_GRP_TYP”          char(6),

o   “ORD_ITEM_SEQ”          smallint,

o   “PRIORITY_CD”           char(1),

o   “SUB_STATUS_CD”         char(2),

o   “PRMRY_COMP_CD”         char(6),

o   “SECNDRY_COMP_CD”       char(6),

o   “NUFS_NET_SRVC_TYP”     char(3),

o   “NUFS_NUM_CAT”          char(2),

o   “USER_ID”               char(8),

o   “PREV_PHONE_NUM”        char(8),

o   “RSU”                   char(6),

o   “PRMRY_GRP”             smallint,

o   “FSL”                   char(6),

o   “CALL_INTERCEPT”        char(1),

o   “RESR_SUB_GRP”          char(1),

o   “OWNER_ACCT_ID”         numeric,

o   “OWNER_SRVC_LOC_ID”     numeric,

o   “PAYER_REF”             char(30),

o   “PAYER_KURT_ID”         int,

o   “CREATORS_REFERENCE”    char(25),

o   “UPDT_LAST_MOD_TS”      datetime

o   )

o   primary key

o   (




o   )

o   searchable columns (RECORD_EFF_END_DT)

o   go



o   define subscription DB2_ASN2_RESOURCE_NB_s2

o   for DB2_ASN2_RESOURCE_NB_d2

o   with replicate at origo_test.deFaktoReplica

o   where RECORD_EFF_END_DT = ’31 dec 9999′

o   go

–          Activate the subscription with:

o   activate subscription DB2_ASN2_RESOURCE_NB_s2

o   for DB2_ASN2_RESOURCE_NB_d2

o   with replicate at origo_test.deFaktoReplica

o   go

–          Validate subscription with:

o   validate subscription DB2_ASN2_RESOURCE_NB_s2

o   for DB2_ASN2_RESOURCE_NB_d2

o   with replicate at origo_test.deFaktoReplica

o   go

–          Resume replication and ask DB2 admin to start the Repagent on their side.

Installing RepServer 15

Installing RepServer 15

–          Untar the repserver installation files into a seperate directory and install the binaries etc by running setup –console. Answer no to email alerts and don’t enter license information.

–          If you are migrating from an earlier version of Repserver and you have already migrated the ASE server then you need to make sure that the rep server logins are not expired, (e.g REP_dhtsolo_RSSD_prim and REP_dhtsolo_RSSD_maint). If they are expired, just try to login to the ase with them, you will need to change the passwords twice, first to a temp password and then back to the original.

–          If you are using Norwegian language then you will also need to add the file into $SYBASE/charsets/iso_1 and tweak the $SYBASE/config/objectid.dat file by adding the following or similar line at the bottom of the collate section:

o  = nocase_iso_1_nor

Find the final number 148 by doing an sp_helpsort in the ase server and looking for the number associated with nocase_iso_1_nor

–          Follow the regular steps for installing a repserver, i.e rs_init which should be fairly straight forward.

When configuring the new rep server set the sort order to nocase_iso_1_nor