Microsoft SQL Server

Introduction

Microsoft SQL Server is a relational database management system developed by Microsoft. Its primary function is storing and retrieving data as requested by other software applications.

Reference: https://www.connectionstrings.com/sql-server/ .

Connection String

To Connect to Microsoft SQL server, set the Server and Database Properties. Additionally, set the Integrated Security Property.

Connection String Parameter

Parameter Description
Data Source Set this to the hostname of the Server of the Microsoft SQL server.
Initial Catalog Set this to the database Name in the specified Microsoft SQL server.
Integrated Security Set this to authenticate with Using Windows authentication.

Connecting to Microsoft SQL Server

  1. Set the hostname of the server as the ‘Data Source’ parameter on your connection string. If the server is on premise server, it will just be a server label.

  2. Next is the ‘Initial Catalog’ parameter. This narrows down the connection to a single database in the server. In SQL Server Management Studio, your options will be contained in the ‘databases’ directory.

  3. Set the Integrated Security property to SSPI. We want an encrypted connection over the network. If there is a security breach, the loss will not include your authentication credentials. Set the ‘Encrypt’ parameter to ‘True’.

  4. Set the login credentials for the server. Set the UserName and Password.

Data Migrations

To Check for the data migrations:

  1. Add a new connection in Loome Integrate as shown.

    • Go to tasks and click on Connections.

    • Add a new connection Using Add New Connection Option.

    • Choose Microsoft SQL Connector from the available Connector Options.

  2. Using the connection string parameters created (You can Specify the Username and password either in the connection string or in the box Shown Below), Verify the connection using the VERIFY CONNECTION Option in Loome Integrate. Once the Connection is verified, Insert the Connection Using Insert Option.

  3. Create a Job Using ADD A JOB option in Loome Integrate, add a data migration task to the job.

  4. Create a New Task by right clicking job list and then Add a New Task Option. If you want to edit an Existing Task Use Edit Task.

  5. Choose a Data Migration Task.

  6. Choose the source and the destination for the migration of data. Name the Task.

  7. Choose the Tables or the data that need to be migrated from the source to the destination. You can Copy all Tables by checking Copy all tables box.

  8. Execute the Job. Check for results and the details of the data migrated in Execution History.