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.