This blog walks you through setting up a local instance of SQL Server on your machine in the aim to create a Mock Up Data Source. This blog has been written from  a technical perspective so it is assumed here that you are tech friendly. This procedure is a sub part of data integration between on-premises data stores cloud data stores using Data Factory and falls under the process of ‘Moving Data between on-premises sources and the cloud with Data Management Gateway.’ Links to full instructions for the latter part of the process will be shared below as well, but the prime focus of this blog will be on the pre-requisite aspect (as this can turn out to be a nuisance if configured incorrectly):

  1. Download SQL Express (Developer’s Edition)
  2. Install SQL Express. Select ‘Custom’ During the installation create a User. Make sure to install a ‘Database Engine’ and any other features required.
  3. Now, carry out the following checks:
    • Go into SQL Server Configuration Manager
    • Select SQL Server Network Configuration
    • Protocol for MSSSQLSERVER
    • TCP/IP needs to be enabled. Right click on TCP/IP and select enable.
    • On IP Address tab make sure you scroll down to the IPAll section and set the port number to 1433.
    • Restart the Server. To do this:
      • Go to SQL Server Services
      • SQL Server
      • Right Click ‘Restart’
        • If you have issues restarting, then just restart your machine. The server should automatically start once the machine has been restarted but nevertheless double check in configuration manager.
        • A few spot checks to see if the server is running:
          • ‘ping localhost’ in cmd line
          • Enable telnet to be able to connect to the port:
            • Run command prompt in Admin mode
            • Type the following in command prompt to enable telnet: ‘dism /online /Enable-Feature /FeatureName:TelnetClient’
            • Now open a new command prompt
            • Type ‘Telnet’ and press Enter. This will show the telnet welcome message.
          • Once telnet has been enabled you should be able to connect to local host via telnet giving it the TCP Port.
            • Open a command prompt
            • Type in ‘telnet <IP Address> <Port>’ and press enter. Port here should be 1433.
            • If a blank screen appears then the port is open, and the test is successful.
            • If you receive a ‘connecting …’ message or error message, then something is blocking the port. Most likely this could be a firewall either Windows or Third party.
  4. Connect from command line with the following (connects using SQL and Windows based auth):
  5. ‘C:\> sqlcmd -S <ip-add> -E’. NOTE: You can find the local IP Address by typing ‘Ipconfig’ in command prompt.
  6. Run the following, substituting in your own credentials:
    • Role should be minimum of ‘db_datareader’. SQL allows a user to be allocated one from three roles, ‘db_datareader’ (read permission only), ‘db_datawriter’ (read and write permissions) and ‘db_owner’ (all permisssions).
    • <login_name> and <user_name> should be the same.
      • CREATE DATABASE <db_name>
      • GO
      • CREATE LOGIN <login_name> WITH PASSWORD = N’<password>’
      • GO
      • USE <db_name>
      • GO
      • CREATE USER <user_name> FOR LOGIN <login_name>
      • GO
      • EXEC sp_addrolemember ‘db_owner’, ‘<username>’
      • GO
      • exit
  7. Now, connect with the test account created by running the following in command prompt:
    • sqlcmd -S <ip-add> -d <db_name> -U <login_name> -P <password>
  8. You should now be at SQL Prompt having successfully connected.
  9. You should now connect to the server via SSMS (SQL Server Management Studio) using the above created credentials:
    • Open SSMS
    • Select ‘Database Engine’
    • For the server name, use the computer name if your server is called ‘MSSQLSERVER’. This means you are using an unnamed instance of the server. If your server is called ‘SQLEXRPRESS’ then you are using a named instance and will have to use the following syntax ‘localhost\SQLEXPRESS’.
    • Username: username created above
    • Password: password created above
    • Either authentication method should work.
    • Now, commence in SSMS as normal
    • You can continue and set up the Mock Database Gateway and ADF Pipeline in Azure. Go on the below link to read a full walk through on how to do this. You would also follow the below link for a real data source gateway set-up, just configuring the input data set to be the actual data source:

https://docs.microsoft.com/en-us/azure/data-factory/v1/data-factory-move-data-between-onprem-and-cloud

The same set up can be completed using a VM (Virtual Machine). Follow the same steps but firstly deploy a Virtual Machine in Azure, then connect to it via Remote Desktop and continue as normal.

 

%d bloggers like this: