Sunday, 15 October 2017

DTS package conversion in SSIS to use in SQL 2012 and SQL 2016

In my company my team decided to migrate whole SQL 2008 to brand new SQL 2016 in Azure VMs. This was a good idea as we should always be up to date in terms of technology.
We did migration all SQL 2008 to SQL 2016 without any problem but suddenly we saw we need to migrate warehouse as well and it was containing very complex DTS package almost 50 in number and we have very less time to finish this task.

We first decided to do following things:

Upgrade DTS in SSIS with wizard available in SQL 2008

But this was not helpful because if you have some VB script written in DTS and it enables or disables any step then it will not work in SSIS because enabling and disabling is not allowed in SSIS. YOu will have to manually correct all these code. So we tried this and dropped this idea.

Upgrade DTS in SSIS with wizard available in SQL 2008

Manually Convert whole DTS in SSIS:

This was blunder for us as it was going to take many months of re creation and testing so tried this and dropped this idea as well because we got a very motivational article over internet 😂😁 .

How to Run DTS in SSIS?

When you will search this over internet people will suggest you there is no success in this way and you will ave to convert DTS in SSIS manually no wizard will help you to do so but they are not correct because we saw a very complex DTS package which was calling other DTS package as its child and again those childs were calling further their childs DTS package.
Microsoft provides a way to run DTS in SSIS.
You just need to provide correct variable values and you will be able to run this package in production without any problem.

How to Run DTS in SSIS

Sunday, 2 July 2017

SQL Server Failover Clustering in SQL Server 2016 with DNS in Azure

As we know that database mirroring is no longer supported in the latest version of SQL Server i.e. in MS SQL Server 2016.
So people are moving toward SQL Server failover clustering as they want to achieve High Availability of database.
Here we will setup the things in Azure cloud service, you can enjoy your free subscription of Azure cloud for 1 month.

So we can do this in two ways:
1. by using DNS Server which will cost you extra server need more money and maintenance.
2. By using workgroup i.e. without DNS Server this will take less cost in comparison to first one.
Best way to do implement SQL Server failover clustering

Its all up to your requirement if you have low budget then go without DNS and if have sufficient budget then go with DNS Server extra.
Both ways have their own pro and cons so we can't rigid with one way.
Steps by Steps to create a SQL Server Always On Availability Group on Azure Virtual Machines.

Create resource group

1)     Sign in to the Azure portal.
2)     Click + to create a new object in the portal.
3)     Type resource group in the Marketplace search window.
4)     Click Resource group.
5)     Click Create.

6)     In the Resource group blade, under Resource group name type a name for the resource group.  

For example, type SQLTEST-RG-WestUs


7)     If you have multiple Azure subscriptions, verify that the subscription is the Azure      subscription you want to create the availability group in.
8)     Select a location. The location is the Azure region where you want to create the availability group. For this tutorial, we are going to build all resources in one Azure location.
9)     Verify Pin to dashboard is checked. This optional setting places a shortcut for the resource group on the Azure portal dashboard.
10)     Click Create to create the resource group.

Create availability sets:

      Before creating virtual machines, you need to create availability sets. Availability sets reduce downtime for planned or unplanned maintenance events. An Azure availability set is a logical group of resources that Azure places on physical fault domains and update domains. A fault domain ensures that the members of the availability set have separate power and network resources. An update domain ensures that members of the availability set are not brought down for maintenance at the same time.  To create an availability set, go to the resource group and click Add. Filter the results by typing Availability Set. Click Availability Set in the results. Click Create.
       After you create the availability sets, return to the resource group in the Azure portal and create three SQL Virtual Machine (2 SQL+ 1 DNS)
       It will take few times to create all three machines and then do following steps:

1. Make one server as DNS server and to configure DNS please follow following steps:

Install DNS Role first by using Server Manager you will get link to install new role and feature.
You need to configure  new DNS server if you don't have already Follow this link to set up DNS