Introduction

This guide describes two approaches for staging in an MS SQL environment: Two databases on one server and one database on two servers each.

References

• Concept - Mass Data Replication

Preconditions

• 2 MS SQL databases (Azure SQL Managed Instance)
• 2 environments:
  • Each connected to one of the databases
  • Different ports
  • One configured as edit, one as live

Databases

Databases on the Same SQL Server

In case both databases use the same login on one server, the databases do know each other. That means, a query for one database can access the data of the other database by identifying it with the full qualified name.

In this case, the database is_live accesses the table DOMAININFORMATION of database is_edit.

In case the databases use different logins, they do not know each other. Instead, they behave like they are on two different servers.

Databases on Different SQL Servers

If the database has to access a database from another database server or the databases have different logins, a linked server must be created.

A detailed documentation on how to create one can be found here: Create Linked Servers (SQL Server Database Engine).

Such a database link can be created with the Microsoft SQL Server Management Studio, or with a script. 
After manually creating a database link in the Studio, the link can be exported as script for reusing.

Necessary Rights

For creating linked servers using the Management Studio, the CONTROL SERVER permission or a membership in the sysadmin fixed server role is required.

When using T-SQL statements, the ALTER ANY LINKED SERVER permission or a membership in the setupadmin fixed server role is required.

Refer to Create Linked Servers (SQL Server Database Engine) for further information.

Link Creation

The dialog for creating a linked server connection:

The following statement can be used for reading the table DOMAININFORMATION using a linked server.
The notation is simply the linked server name, followed by the name of the database and the used schema, in this case followed by dbo.

use is_live;
GO

select * from [jdevpool01].is_edit.dbo.DOMAININFORMATION

Manual Creation of the DB-link

To manually create a DB-Link perform the following steps:

1. Right click Linked Servers and select New Linked Server...
   The New Linked Server dialog opens.
   
2. Enter the host name.
   
3. Switch to Security.
4. Make sure, login and password for the linked server are included.
   
5. For local testing, the local login can be inserted as well.

6. Click Ok.

   Info

   The Server Options do not have to be changed.

7. For exporting the linked server as a script, right click the newly created connection and choose Script Linked Server as | CREATE to | New Query Editor Window to export it as SQL query.

   

Script

The SQL-script can be used for the creation of a database-link, see CreateConnection.sql.

DECLARE 
@var_server NVARCHAR(MAX) = 'TARGET_SERVER_HOSTNAME',
@var_locallogin NVARCHAR(MAX) = 'LOCAL_LOGIN',
-- @var_currentlogin NVARCHAR(MAX) = 'OPTIONAL_SECOND_LOCAL_LOGIN',
@var_rmtuser NVARCHAR(MAX) = 'TARGET_SERVER_USERNAME',
@var_rmtpassword NVARCHAR(MAX) = 'TARGET_SERVER_PASSWORD';

-- If the server do exists:
-- EXEC sp_dropserver @var_server, 'droplogins'

EXEC sp_addlinkedserver @server = @var_server, @srvproduct=N'SQL Server'
EXEC sp_addlinkedsrvlogin @rmtsrvname=@var_server,@useself=N'False',@locallogin=@var_locallogin,@rmtuser=@var_rmtuser,@rmtpassword=@var_rmtpassword
-- EXEC sp_addlinkedsrvlogin @rmtsrvname=@var_server,@useself=N'False',@locallogin=@var_currentlogin,@rmtuser=@var_rmtuser,@rmtpassword=@var_rmtpassword
EXEC sp_serveroption @server=@var_server, @optname=N'collation compatible', @optvalue=N'false'
EXEC sp_serveroption @server=@var_server, @optname=N'data access', @optvalue=N'true'
EXEC sp_serveroption @server=@var_server, @optname=N'dist', @optvalue=N'false'
EXEC sp_serveroption @server=@var_server, @optname=N'pub', @optvalue=N'false'
EXEC sp_serveroption @server=@var_server, @optname=N'rpc', @optvalue=N'false'
EXEC sp_serveroption @server=@var_server, @optname=N'rpc out', @optvalue=N'false'
EXEC sp_serveroption @server=@var_server, @optname=N'sub', @optvalue=N'false'
EXEC sp_serveroption @server=@var_server, @optname=N'connect timeout', @optvalue=N'0'
EXEC sp_serveroption @server=@var_server, @optname=N'collation name', @optvalue=null
EXEC sp_serveroption @server=@var_server, @optname=N'lazy schema validation', @optvalue=N'false'
EXEC sp_serveroption @server=@var_server, @optname=N'query timeout', @optvalue=N'0'
EXEC sp_serveroption @server=@var_server, @optname=N'use remote collation', @optvalue=N'true'
EXEC sp_serveroption @server=@var_server, @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

All the manual changes are in the first lines.

The variable @var_server must have the hostname of the linked server as value.
The variable @var_locallogin must have the username that the local server will use as value. That is the database username of the live-system.
The variable @var_rmtuser must have the username for the linked server as value. That is the database username of the edit-system.
The variable @var_rmtpassword must have the password for the linked server as value. That is the database password of the edit-system.

Optional:

If lines 4 and 10 are uncommented, the variable @var_currentlogin can be set as another login. The idea is, that the person who will create this link must have the admin-role. Also, the person might want to test SQL statements that utilize the database link. The server does not need the admin-role and should not have it. Therefore, anther login for the creator of the database link has to be created.

All the lines below line 10 just declare default values and can be removed, except the command GO.

Databases in Azure Managed Instance

Preconditions

It is required that both databases are located in the same managed instance. A linked server across multiple managed instances is not possible.

Link Creation

1. Create a new linked server:

   EXEC master.dbo.sp_addlinkedserver @server = N'<LINKED SERVER NAME>', @srvproduct=N'', @provider=N'SQLNCLI11', @provstr=N'Server=<SERVER_DNS_NAME>;Initial Catalog=master;Persist Security Info=False;MultipleActiveResultSets=FalseEncrypt=yes;TrustServerCertificate=yes;Connection Timeout=30;User ID=<EDIT USER>'
   GO

   The name of the linked server can be choosen freely. In the {{provstr}} attribute the server DNS name has to be specified. This can be determined by executing {{SELECT @@SERVERNAME}}. Furthermore it is required to add the name if the EDIT database user as {{User ID}}.

2. Run the following test script to verify that the linked server was successfully created:

    Expand source

   BEGIN
       SET NOCOUNT ON;
       DECLARE @retval int = 0,
               @sysservername sysname;
       BEGIN TRY
           SELECT  @sysservername = CONVERT(sysname, <LINKED SERVER NAME>);
           EXEC @retval = sys.sp_testlinkedserver @sysservername;
           SELECT 'SUCCESS';
       END TRY
       BEGIN CATCH
           SELECT 'FAILED';
       END CATCH;      
   END
   
   

3. Configure the remote login:

   EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'<LINKED SERVER NAME>',@useself=N'False',@locallogin=N'<LIVE USER NAME>',@rmtuser=N'<EDIT USER NAME>',@rmtpassword='<EDIT USER PASSWORD>'
   GO

Edit Configuration

Some configurations on the edit system are required to enable staging. 

staging.properties

The staging.properties are stored under <ISShare>/system/config/cluster/staging.properties:

staging.system.type=editing
staging.process.EntityParallelism.mssql=1

In the current version, for MSSQL the parallel processing is not supported. Therefore, the property staging.process.EntityParallelism.mssql has to be set to 1.

replication-clusters.xml

The replication-clusters.xml is stored under <ISShare>/system/config/custer/replication-clusters.xml.

Two Databases on the Same Server

In this XML file, the connection to the live-system is configured.

<?xml version="1.0" encoding="UTF-8" ?>
<replication-configuration
    xsi:schemaLocation="http://www.intershop.com/xml/ns/enfinity/7.5.0/core/replication replication.xsd"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns="http://www.intershop.com/xml/ns/enfinity/7.5.0/core/replication">

    <target-clusters>
      <target-cluster id="DEV7_EDIT">
          <target-systems>
              <target-system id="DEV8_LIVE" active="true">
                  <webserver-url>http://host-live:81</webserver-url>
                  <source-server-group>BOS</source-server-group>
                  <target-server-group>BOS</target-server-group>
				  <source-database-link>db-schema-edit.dbo</source-database-link>
               </target-system>                
          </target-systems>
      </target-cluster>
  </target-clusters>

</replication-configuration>

The important thing here is to configure the URL of the live-system. The staging is based on a webservice-call from the edit to the live-system. 
The edit-system tells the live-system the name of a db-link from the live-db to the edit-db with the content of the tag source-database-link. In a normal environment, such a link for MSSQL has to end on dbo. A database could be configured to another value than dbo.
As long as the databases are hosted within the same server, they do know each other an simply have to be addressed by name.

Two Databases on Two Different Servers

In the XML file, the database-link to the live-server has to be inserted, including the database name.

<?xml version="1.0" encoding="UTF-8" ?>
<replication-configuration
    xsi:schemaLocation="http://www.intershop.com/xml/ns/enfinity/7.5.0/core/replication replication.xsd"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns="http://www.intershop.com/xml/ns/enfinity/7.5.0/core/replication">

    <target-clusters>
      <target-cluster id="DEV7_EDIT">
          <target-systems>
              <target-system id="DEV8_LIVE" active="true">
                  <webserver-url>http://host-live:81</webserver-url>
                  <source-server-group>BOS</source-server-group>
                  <target-server-group>BOS</target-server-group>
				  <source-database-link>[db-host-edit].db-schema-edit.dbo</source-database-link>
               </target-system>                
          </target-systems>
      </target-cluster>
  </target-clusters>

</replication-configuration>

The important thing here is the source-database-link. It is the name of the link like it is in the database in brackets, followed by the name of the used database on the server and the namespace, here dbo.

Live Configuration

staging.properties

The staging.properties are under <ISShare>/system/config/cluster/staging.properties

staging.system.type=live
staging.process.EntityParallelism.mssql=1

The live system must have live set as the staging type, analogous to the edit system that uses the staging type edit.
Also, the property staging.process.EntityParallelism.mssql must be set to 1.

DBINIT

Edit

On the edit system, a normal dbinit can be performed with dbinit -classic.

Live

A copy of the edit system is required on the live system. This is because staging fails if the UUIDs of the domains on the systems are different.

The Microsoft SQL Server Management Studio can be used for that.

Staging Task Creation

To create a staging task perform the following steps:

1. Login to the Commerce Management and switch to Mass Data Tasks | Data Replication Tasks.
   
2. Click New.
3. Click Apply and switch to the Replication Groups tab.
   
4. Click New.
   
   The desired staging-groups will be assigned to the new task.
   
5. Click Assign and switch back to the General tab.
6. Click Submit and confirm by clicking OK.
   
   The replication task is ready.
7. Login to the Organization Management and switch to Data Replication | Data Replication Processes.
   
8. Click New to create a new process.
9. Click Next >>.
   
10. Confirm the target.
    At this point, the right configuration of the target-cluster can be confirmed.
    
11. Select the newly created replication task.
    
12. Click Finish.
    
13. Click Run to execute the staging task.