PartitionDB Hybrid Service – Installation Guide
PartitionDB Hybrid service provides a simple path to extend a database to the cloud and to manage on premise and cloud databases as one. The service can be installed automatically by running the installer, or manually as explained below. Completing one of the options will let you start using the service.
Note that the installation should be performed on each of the databases, on premise and the cloud. If you set up your cloud machine using PartitionDB service, this step is already done for you. If not, or if this is your on premise database, please go ahead and run the installer described below.
Before you start
This guide assumes SQL Server is installed on the machine.
For automatic installation please download the installer and run it. Below are the steps you should take when running the installer:
In case you prefer to set up your environment manually, follow this step by step guide:
- Open SQL Server properties
- Allow mixed login mode for Linked Server access.
- Select ‘security’ from the pages list.
- In ‘Server Authentication’ check ‘SQL Server and Windows authentication mode’.
- Configure remote connections:
- Select ‘connections’ from the pages list.
- In ‘Remote server connections’ check:
‘Allow remote connections to this server’
‘Require distributed transactions for server-to-server communication’.
- Open services window again and stop the DTC service (Distributed Transaction Coordinator)
- Open a ‘cmd’ window. Note that administrator privileges are required. This can be done by right click on the cmd.exe in the browser or in the Start menu.
- Run msdtc uninstall and install commands:
- In the Start menu, run dcomcnfg
- Browse to LocalDTC: Console Root->Component Services->Computes->My Computer-> Distributed Transaction Coordinator->Local DTC
- Select Properties for ‘Local DTC’ using a right click
- Select the ‘Security’ tab
- Make sure to have the following options checked:
- Allow remote clients
- Allow remote administration
- Allow inbound
- Allow outbound
- Enable XA Transactions
- Enable SNA LU 6.2 Transactions
At this step you can either disable the firewall (which is not recommended) or open the two ports: 1143 and 135 (which is the default DTC port).
In case of disabling the firewall, we can see that the ‘Windows Firewall state’ is set to off, and you can move to the next step.
If you prefer to enable the required ports (recommended), the firewall should be active and a new rule should be created for each port. Therefore, this process has to be repeated two times, one rule per port.
- In the Start->Run window run ‘firewall.cpl’
- Open ‘Advanced settings’ by clicking on it
- Open ‘Inbound Rules’ by clicking on it
- Create a new inbound rule by clicking on ‘New Rule…’
- Follow the wizard. In ‘Protocol and Ports’ select ‘Port’ and click ‘Next’
- In ‘Rule Type’ select ‘TCP’ and fill out ‘Specified local ports’ with 1433. Click ‘Next’
- In ‘Action’ select ‘Allow the connection’ and click ‘Next’
- In ‘Profile’ check all three options: Domain, Private, Public, and click ‘Next
- Name the new rule in ‘Name’ and click ‘Finish’
- Repeat the process for port 135
This step guides you how to define a remote server from your MS SQL. This step is mandatory if configuring the master database, in order to give it access to the remote database. If configuring the slave database this step is optional. If an access from the slave database to the master is desired, follow the instructions in this step, if not, skip to the next step.
- Open SQL Server management studio. In the object explorer under ‘Server Object’, right click on Linked Servers and select ‘New Linked Server’:
- In the General tab, select a name for your linked server.
In ‘Server type’, If all servers in your platform are running the same version of SQL, select SQL Server.
If not, and the SQL versions differ, select ‘Other data source’. In the drop down list select either “Microsoft OLE DB Provider for SQL Server” or ‘SQL Server Native Client’. It is highly recommended to work with the Native Client due to performance reasons.
- Select ‘Security’ in the left pane and add a user:
- Finally, go to ‘Server Options’ in the left pane and enable the following options:
You have successfully defined a new linked server. Note that linked servers should be defined between databases only if they reside on different servers,