Configuring and testing Transactional replication from On-premise SQL server to Azure Managed Instance

Hello Readers !

Below is a demo on configuring transactional replication from an On-premise SQL server to an Azure Managed instance. I have included as many screenshots as possible for making the blog detailed enough to be followed when  you configure it in your own environment.It was rather easy to configure it with the UI than the scripts in MSDN documentation., which is why we will be using the UI for the Demo. I will also be sharing a separate blog on configuring  transactional replication between two managed instance with the existing limitations using the scripts.

Lets get to the setup required. 

Networking requirements : Have a Point to Site/Site to Site VPN or Express route configured to connect your on-premise machine to the Azure VNet.

Publisher and Distrubutor : My Local default Instance

Subscriber : A general purpose Instance I have in our POC VNet.

 Snapshot folder :I have created a folder named repl_Snapshot in my local. and gave my machine account full access to it.  

Both my SQL server service and agents are set to run with Local system account.
I will be using SQL accounts for authenticating between  the replication partners like how it is suggested for MI-MI replication.

I have a DB named DBA created to host the a few tables for maintenance jobs which will be my publication Databases

Articles/Objects for publication : Two tables Named Config and block_locks in DBA database

I also have created a DB named DBA_Sub in the managed instance to which I will be replicating these tables.

I have created a login earlier named AzureAdmin in both the instances and gave it has got sysadmin rights on the instances which would be reused here too.

Steps : 

1.Configuring Distributor

Right Clicking on the replication folder in my local instance and launching the 'Configure Distribution ' wizards



Clicking on next and supplying the snapshot folder location.


Clicking next again will take us to the window to enter data and log file locations of distribution database.



Clicking next from here till the last window as there are no input fields.







Finish to complete the distributor configuration,


2. Creating Publication 

Right clicking on Local publication folder under the replication folder and launching 'New Publication' Wizards



Clicking next to choose the Publication database which is DBA


Choosing the replication type as Transactional Replication


Selecting the articles/Objects for replication


I am also adding a small row filter on the block_locks table to check if it works too.



Basically i am choosing rows that as got ID values less than 500 , to be replicated from this article.

Pressing OK will gave me a confirmation like the below.



Clicking Next on it takes me to Snapshot agent configuration window.



I am choosing both the checkboxes to initiate the first snapshot and to run snapshot every 1 hour. (Only for testing. I dont recommend to run Snapshot agent every one hour for sensitive environment as it will be unnecessary and can put load on your server)



Next window lets us to supply the accounts underwhich the agents have to run.



I have clicked on Security settings to launch the below window. I am choosing the Snapshot agent to run under SQL agent account  and supply the creds to connect to publisher.



Clicking Ok will take us to the previous window like below. I am reusing the security settings for Snapshot agent in the log reader agent by clicking the checkbox below.


We are done with creating publication here






3. Creating Subscription in the managed instance

Right clicking on Replication/Local subscriptions in the managed instance and creating new subscription  and  in the new window supplying SQL credentials to connect to the publisher  ie, my local SQL instance. 



Choosing the publication which we created above in the next window




We are going with Push Subscription as thats what works between the MI Instances. I will test the pull one against Local-MI later and post the results here.



Next window will let us choose the subscriber database.

In the next window, I am supplying the credentials to connect to the subscribers and choosing to run the distributor agent under SQL agent account.

I am not setting any schedule for the distributor agent and let it run continuously.

We have choice here to initialize subscription and I am opting for it.

Below gives a summary of whatever settings we have made so far.

Clicking on Finish will finish the 'create subscription' windows.

4.  Verifying if the Tables got replicated

The tables are visible  in the managed instance already as we set to initialize subscription in windows above.




And the filer on Block_locks worked too




I could also see the agents running without any issues from the replication monitor I have launched from the publisher.

That marks the end of this blog. I will come back with a different topic next time under Azure or SQL server. Keep visiting. 

Introduction to Azure Data factory and its components

Hello Readers!

The below blog content is about the PaaS offering by Azure for the ETL process, called Azure data factory. I intend to introduce the components that one should know in ADF ,before migrating your SSIS packages into Azure. 

Azure Data Factories (ADF)

ADF is the Azure data integration is the Platform or the Service that will help us to perform data movement, transformation and package executions either by its own compute components or with the help of other services available in Azure. Just like the SSIS in your on-premise , it has been equipped with a variety of tools to make the ETL process easy and code-free.

Integration RunTime

Integration runtime is the component of ADF that does the job of moving data or executing a package etc. We can assign compute resources to it and make it join our VNet and hence can be made connected to the resources in both on-premise and within the Azure Vnet based on the network configuration of the VNet.

Types of IR

1.Azure Integration Runtime  : 
 This comes default when we create a data factory instance and it essentially helps to run data flow within azure, copy data to and fro two cloud data stores, and dispatch transformation tasks to other tools like Data Bricks and HD insights. 
2. Self Hosted Integration Runtime :
This can be downloaded and installed in our private machines and can be used to perform data integration activities securely in a private network (on-premise) with the azure data factory looking after the execution through the pipeline defenitions. This can also dispatch transformation tasks to the other tools like Data Bricks and HD insights.
3. SSIS Integration Runtime
This is what is being used to migrate and execute SSIS packages in Azure. We would learn about in the blog content for the migration and execution Demos later. 

Data factory Pipeline:

Pipeline are basically grouping activities (also called pipeline activities) for a specific task. You can have one more activity within a pipeline and can be made connected different data sources by means of linked services. 
Linked services :
These are like connection manager entries we have in SSIS and it helps to connect data factory to the data stores. 
Triggers : 
We can set the schedules to execute the pipelines in Triggers and can disable/Enable them as we like. 

Hope the above explanations are simple enough. Its worthy to note that Data factory doesn't do transformation tasks on its own and depend on other tools for it. But that should not bother you if you have the transformation tasks defined in SSIS package itself and you only need to execute it. The first two integration Runtimes , i have defined above can made use of , to dispatch the transformation tasks other than the ones in the SSIS packages to a variety of tools that are available in Azure itself . 

Creating Managed instance and some basic UI methods for instance management

Hello Everyone,

The below blog content is on creating a general purpose managed instance for testing purpose ,using the azure portal and I will also be discussing a bit on using a few options to manage the instance .

Prerequisites :  


1.You have basic understanding about SQL servers and its administration in general.

2.Awareness of basic azure networking/Security concepts like NSGs, Subnet, VNet etc.
(I will write a separate blog on Azure networking configuration sometime later)

It is recommended to have your Managed instance hosted in a seperate subnet for us to have control over the inbound and outbound traffic for the Managed instance.When we create one managed instance, one could see that there is a default NSG that gets created by itself ,mapped to the subnet we designate to host the Managed instance(s).

Lets get to the steps.

1. Search for the resource name "Managed instance " in the azure portal home page. See below for the screenshot







2.You should be able to find the Azure resource for the Managed instance like below.

3.Click on Create, , fill in the subscription, resource group, instance name and location.

There is also an option of creating the resource group in this window.  You can go for that if you want. But I am assuming that you would also have a resource group created for grouping all the resources you would need for your POCs/Tests.

We have opted for East-Us2 as the location as the location looks to be the right fit for us because of three reasons. Its comparatively cheaper , has every resource we needed and is closer to our client location.You can choose a different location if you want based on your requirement.

I have suffixed the name of the instance with MIGP for denoting general purpose.

4.Scroll down for supplying more details .

Against Compute+storage field , you can provide the resource you need for the MI. You can also decide to go for General purpose or Business critical in the same place.
Click on configure managed instance and the below window will appear.

As you see in the screenshot, on top you would get to choose business critical or general purpose.I have opted for general purpose for now and set the minimum VCpu and storage to keep the live DB file. We don't get to change the memory as it comes with the VCpu setting itself. (Memory for MI is 5 times that of the VCpu. For example : 4VCpu has 20 GB memory).

Upon choosing the desired configurations, click on Apply . (You can change these after creating the instance too. See below).

I have supplied the Admin credentials for creation here. This is the place you give your instance admin credential (Similar to SA account).

Click on next :Networking to continue .

5.Networking options

You can either create a new VNet or choose VNet/Subnet name from the list, which you may have created already.

   

Below that there is a field named 'Connection type'

Two options we got here.

Azure team had left their short note for describing both the options, which I am attaching here for your reference.

I am leaving this to the Proxy method for now .We can change it to redirect later after adding a few inbound/outbound rules in the NSG associated with our managed instance subnet. (I will cover this in future blogs).

Below the connection type options, you can choose to enable or disable public end points. This is quite important to understand. Its recommended that you set up your Point- to-Site /Site to Site VPN setup ready for connecting to the instance over a private endpoint as the public endpoint is exposing your managed instance over the internet. You can disable after a later point too , when you have your VPN setup to connect from the On-premise SSMS.

6.Additional Settings.

Here as you see in the screenshot, you would get to choose the collation and the server time zone for your managed instance. You can check the collation you follow in your Production server and set it accordingly and for the timezone, I recommend to set it to the UTC time as i have set below as its the general best practice to follow. 

At present, we are not configuring any DR option like Geo -replication.

You may turn it off for now. 

7.Click on Review+Create

This is where you get to review the settings we have applied til now.

I had to mask the named i have used as those are confidential to our client and project. 

Once you click on Create ,your resource deployment to azure will begin and MI creation takes close to 6 hours.

You may check the portal again to see your Managed instance in 6 hours.

Basic Configurtions:

For getting the connection string details, there is a direct option named, Connection string in the left pane for the instance.

For public end point, you may copy the MI instance Url (Starting from tcp:MIservername,3342)., and connect to that from your SSMS supplying the admin credentials you have supplied. 

1.Changing the backup retention of database

Azure Portal gives the option to do this along with the other options like powershell. 

You can set the short term retention to any day between 7-35 in the database page in portal. Sample is given below.

2)Scaling the instance Up/Down

You can change the pricing tier (business critical to General purpose) or your compute resources in the left pane option called 'Pricing Tier'. You will see a window similar to the one we saw while setting up the instance.

Change what you need and click on 'Apply' to apply them. 

I hope you find this blog post knowledgeable. Have a great day ! 

Checklist for SIde by SIde SQL server migration project tasks

Hello Readers!

As a senior DBA, one has to become familiar with checklists for each of the planned activities like migration, so as to avoid mistakes.

Below I am trying to list down the steps one should not miss while performing a side by side SQL server migration , that are common for most of the SQL versions. Please comment below,  if you got anything more to add, which will help us to learn more together. 

Pre Activity Checklist

1.Raise tickets/Inform stakeholders as needed and request a realistic downtime as required which includes a rollback time incase of failure.

2.Get the target system ready with the help of server team and make sure it has got the right windows OS, enough storage, memory and CPU assigned with the desired I/O Throughput.

3.Install the SQL server with the desired version and edition (equal or higher than the Source server instance)

4.Collect the information about the Database properties (Auto Stats, DB Owner, Recovery Model, Compatibility level, Trustworthy option etc)

5.Collect the information of dependent applications, Scheduled batches and  make sure application services and batches  will be stopped during the database migration

6.Collect the information of database logins (Revlogin Script output), database users and their permissions including the DB role memberships

7. Check the databases for the Orphan users to see if there are any already existing in the database before migration.

8.Check the SQL Server for any dependent objects (Example : a job working with an external file)

9.Backup  Linked servers, SQL Server audit specifications, credentials and Certificates

10.For bigger database servers , plan for Full back up prior to the activity and a differential during the activity window.

11.Back up Sp_configure options with 'Show advanced options' from the source server.

12.Put the server in maintenance mode for the scheduled window in monitoring tools to avoid unwanted alerts or intimate the team who is responsible to do it.

13.Make sure to test the migration in DEV,UAT boxes before going for Prod instance migration.

14.Make sure to note down the recent Security patch updates (GDR/QFEs only) which could be applied during the activity and incorporate security patching in the migration plan.

15.Note down Protocols, ports,service accounts credentials and start up options in SQL configuration options.

16.Note down Alerts, Operator, Mail configurations in the source server

17.Make separate plans for report service and SSIS package migrations.

Checklist to follow during the activity window

1.Intimate stakeholders that the activity is about to start ,over email.

2.Backup the database on the source with compression over to the relevant server/sharepath

3.restore link servers, credentials, and instance audit specifications and make sure to enable the instance audit in both instance and DB level.

4.Check backup headerfor date and source before restoring, to confirm if the right backup is being restored

5.Restore backups and  jobs, linked servers with 'if not exist' clause on to the target machine.

6.Change the compatibility Levels to relevant values in the target server, if the source databases are from server with lower SQL version.

7.Migrate the logins mapped to the users of the migrated databases using Sp_help_revlogin script generated earlier.

8.Fix the orphan users if reported

9.If the windows server get renamed, Please do change the instance name by sp_dropserver and sp_addserver method

10.Compare and reapply Sp_configure options backed up from the source server

11.Compare the protocols, Ports and start up options  of the source server with the new server instance and adjust the settings  accordingly.

12.Make sure the mail configurations, alerts and Operators are set as required.

Post -Activity Checklist

1.Confirm connection to the database server from a report machine / Telneting with SQL port.

2.Update the CMDB and the inventory sheets .

3.Do an overall sanity test for the instance before handing it over to the application team

4.Email and get confirmation from the application team that there are no issues reported.