PartitionDB Hybrid Service – Installation Guide

Introduction

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.

Automatic Installation

For automatic installation please download the installer and run it. Below are the steps you should take when running the installer:

  • Make sure to log in with admin credentials
  • Provide server name and credentials and click ‘connect’

  • Once connected, click ‘Next’

This step is configuring the ports, set up remote access etc.

  • Click ‘Automatic configuration’ for an automatic process. This is the recommended way.
  • A manual process is also available. Progress step by step clicking ‘See details’ for detailed explanation.

  • Provide the name and credentials for the linked server. Note that if installing the master database, this information must be provided, so the remote database can be accessed. If installing the slave database, this step is optional.

  • When finished click ‘Next’.

PdbLogic is PartitionDB Hybrid engine. It is a database that should be attached to your database in order to use the service.

  • Click ‘Attach PdbLogic’.
  • Another option is to manually attach PdbLogic to your database. It is located in the provided path.

  • When finished click ‘Next’.
  • Check ‘Add PartitionDB Add in to MSSQL’ if you wish to have PartitionDB UI available to you in your MSSQL.
  • Click ‘Finish’ to complete the installation.

  • To get started refer to the post demonstrating hybrid solution.

Manual Installation

In case you prefer to set up your environment manually, follow this step by step guide:

  • Open the Start menu and hit “run”
  • Type services.msc and hit Ok

  • Find ‘SQL Server Browser’ service in the list
  • Start the service by right click on the entry in the list and select ‘Start’

  • 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 SQL server configuration manger. This can be done from the Start->Run:

  • Enable ‘Named Pipes’
    • Select SQL Server Network Configuration
    • Select your SQL server
    • Enable ‘Named Pipes’

  • 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:
    msdtc –uninstall
    msdtc -install

  • 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

  • Open services window

  • Find Distributed Transaction Coordinator in the list and start it using right click

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

In order to have all the configuration changes take effect an SQL Server restart should be performed at this point.

  • Open the SQL Server Configuration again
  • Restart the SQL Server using right click

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,

Installation is completed. To get started refer to the post demonstrating hybrid solution.