SQL Server is amazing, its rich tool sets makes DBA’s, Devs and Engineers life easier and easier each day that passes, now with Azure its even more amazing! One of the best features of SQL is its ability to replicate from On Premise to Azure and there are many ways to achieve this, from Always On, Data Sync or Replication. Each of this methods have its pros and cons and its up to you what to use based on your reason why you need to replicate the data. For me since there are budget constraints meaning we cannot go smorgasbord on Azure so I choose replication because I only need certain tables synced so it is visible to external applications like Tableau even outside of our network, this means I only need a read only part of the whole database.
Now having established that lets start.
First log in to your Azure then choose Subscriptions then find SQL Database and create one
Fill in the details required for your database then configure your server.
Now configure your performance and in this example we just choose the Standard one
Now press Create to spin up your new SQL Server Database. You will be notified once Deployment had succeeded.
Once its available, take note of the Server Name on the Overview menu
Now to see if everything is working well, go to your SSMS and connect using that server name, make sure any firewall rules allow you to connect to Azure
It will then ask you to add a New Firewall rule in Azure so it will make your client IP white listed
Once successful you will see your blank database on the cloud
Now you got an Azure SQL lets configure your on Premise Database so it starts syncing data to the cloud.
Go to your On Premise SQL Server, under Replication choose Local Publication then right click and choose New Publication
New Publication Wizard appears, click Next
Now choose who becomes the distributor and in this instance we use our On Premise Server. Click Next
Add the snapshot folder, this folder is simply a directory that you have designated as a share; agents that read from and write to this folder must have sufficient permissions to access it. Click Next
Now choose your database to synchronize. Click Next
Then choose a publication type, In this instance I used Transactional Replication which typically starts with a snapshot of the publication database objects and data. As soon as the initial snapshot is taken, subsequent data changes and schema modifications made at the Publisher are usually delivered to the Subscriber as they occur (in near real time). This is my method of choice since I incremental changes to be propagated to Subscribers as they occur and for dashboard and report purposes since the application requires access to intermediate data states this is the best method for it. For more information on different methods you can further read this article.
Now Click Next.
You can now choose what tables your want to publish, one caveat here is that if you want to publish tables make sure those ones you want to publish have primary keys. On the other hand if you want to publish views then make sure to take note what tables they are dependent on as they need to be replicated as well. Click Next.
You can even filter Table Rows if needed.
Now lets create the snapshot immediately then click Next
Set up the security credentials for the Snapshot Agent, click Security Settings
Lets just choose the SQL Server Agent service Account and impersonate it when connecting to the Publisher. Click OK.
Now you have the credentials in place, click Next.
Lets Create the publication then click Next.
Now lets complete the wizard, review the summary and make sure everything is correct. Click Finish.
Now it will start creating the publication.
Now you have a Distributor, lets start creating a subscription. Back to your On Premise database, choose Replication, then Local Publication. Choose your new Publication then right click and Subscribe to it.
Wizard appears. Click Next
Now choose the publisher and the publication. Click Next
Chose where to run the Distribution Agent, in this instance we will just run it at the Distributor, which means it is a push subscription. Click Next.
Now lets add a SQL Server Subscriber which is your Azure SQL.
Connect to your Azure SQL
And choose the subscription Database. Click Next.
Configure the Agent Security. Click on the … (ellipsis)
Again run is using the SQL Server Agent Service, on the Distributor, just impersonate the account. On the Subscriber use your Azure Login. Click OK.
Now security is set.
Choose your Agent Schedule, in this case I want to run continuously. Click Next.
Now set the initialize settings, lets choose Immediately. Click Next.
Wizard is completing so choose Create the subscription then click Next.
View the summary then click Finish.
Now its creating the subscriptions.
Once its done give it several minutes depending on the size of tables you are synchronizing. You can see the activity by right clicking on the publication and choosing “View Snapshot Agent Status”
It will then give you an idea when it started and what it is doing at the moment.
Once its finished, check your Azure SQL table contents you will see that things are syncing now in almost real time.
Congratulations you now made your On Premise SQL Sync to the cloud.