Setting Up SQL Server

Sql Replication

Setting Up SQL Server

 

You will need administrative access to the SQL Server instance that you intend to use in order to complete the following initial setup tasks:

 

1.Create a SQL Server Login for use by the replicator and set the password to the account.

2.Allow the new login to perform bulk operations.

3.Create a new SQL Server database.

4.Set the owner of the new database to the name of the new login.

 

To assist with these steps you will find an SQL script in a file named ConfigureDatabase.sql in the root folder of the development environment. You can execute this script via SQL Server Management Studio in order to perform the steps listed above. Unless you edit it to do something different, the script creates a login called SqlReplicationUser with the password set to SqlReplicationPassword, and creates a database named SqlReplication.

 

Setting the Database Connect String

 

The Synergy SQL Connection API used by the replicator to connect to and interact with the database uses a "connection string" to identify the database server, database name and the database username and password. This connection string is stored in the environment variable REPLICATOR_DATABASE, or can be specified via the -database command line option. The default connection string is configured for my development environment:

 

net:SqlReplicationUser/SqlReplicationPassword/SqlReplication/SISQL2017///@1958:SISQL2017!VTX12_SQLNATIVE

 

You will need to be changed it to match your environment.

 

If you are running replicator from the supplied Visual Studio project you will find that the REPLICATOR_DATABASE environment variable is set via the "common properties" tab in the Visual Studio project properties; change the value and then restart Visual Studio.

 

If you are running replicator from the supplied Workbench projects you will find that REPLICATOR_DATABASE environment variable is set in the project properties of both the application and replicator projects.

 

If you are running replicator as a Windows Service using the supplied batch file RegisterReplicatorService.bat you will find that the -database command option is used.

 

Here are a few examples of valid SQL Connection connect strings for use with SQL Server databases.

 

Database

Instance

Connect via

Authentication

Connect string

Local

Either

DSN

SQL Server Login

VTX12_SQLNATIVE:uid/pwd/dsn

Local

Default

Database name

SQL Server Login

VTX12_SQLNATIVE:uid/pwd/dbname/.///

Local

Default

Database name

Windows Login

VTX12_SQLNATIVE://dbname/.///Trusted_connection=yes

Local

Named

Database name

SQL Server Login

VTX12_SQLNATIVE:uid/pwd/dbname/.\\\instance///

Local

Named

Database name

Windows Login

VTX12_SQLNATIVE://dbname/.\\\instance///Trusted_connection=yes

Remote

Either

DSN

SQL Server Login

net:uid/pwd/dsn@port:server_ip!VTX12_SQLNATIVE

Remote

Default

Database name

SQL Server Login

net:uid/pwd/dbname/server_name///@port:server_ip!VTX12_SQLNATIVE

Remote

Default

Database name

Windows Login

net://dbname/server_name///Trusted_connection=yes@port:server_ip!VTX12_SQLNATIVE

Remote

Named

Database name

SQL Server Login

net:uid/pwd/dbname/server_name\\\instance///@port:server_ip!VTX12_SQLNATIVE

Remote

Named

Database name

Windows Login

net://dbname/server_name\\\instance///Trusted_connection=yes@port:server_ip!VTX12_SQLNATIVE

 

A local connection should be used when the SQL Server database is hosted on the same system that the replicator is running on. A remote connection should be used when the SQL Server database is located on a different system, and requires that the Synergy OpenNET server is running on the database server system.

 

The various parts of the connect string are replaced as follows:

 

Value

Replaced with

uid

Username of the SQL Server login to use.

pwd

Password of the SQL Server login to use.

dsn

Name of an ODBC datasource to use.

dbname

Name of the SQL Server database to connect to.

port

TCP/IP port number that the Synergy OpenNet server is listening on (usually 1958) on the remote database server.

server_name

Name of the remote SQL Server (Window server name).

server_ip

The DNS name or TCP/IP address of the remote database server.

instance

Name of the SQL Server named instance.

 

If you are using an ODBC DSN to connect to the database then you should:

 

Create the DSN wherever the database is located. For local databases the DSN should be defined on the local system. For remote databases the DSN should be created on the remote server system.

For local databases, create the DSN to match the bit-size that you are building the replicator application with. If you are building replicator for x86 then create a 32-bit System DSN. If you are building replicator for x64 then create a 64-bit System DSN.

For remote databases, create a DSN to match the bit size of the server that is hosting the database, and running the Synergy SQL OpenNet server. For 32-bit servers (rare), create a 32-bit System DSN on the server. For 64-bit servers (usual), create a 64-bit System DSN on the server.

 

You can find additional information about SQL Connection connect strings in the SQL Connection documentation.