How to move AlwaysON AG Databases ?

Back in 2011, we have seen how to move a database which is participating in DB Mirroring here. In this blog post, let’s see how to move a database which is participating in a AG to a new drive(location). In my AG setup,I’ve three replicas(2 near Replicas-Sync mode and 1 far replica sitting in a different data center-Async mode). The database which I will be moving to a new location is “sales”. See below for current paths.

1

Now we shall move these files to below mentioned new location (In this post, let’s see method two mentioned below):

E:\Devices\MSSQL13.MSSQLSERVER\MSSQL\DATA ( MDF File)
F:\Devices\MSSQL13.MSSQLSERVER\MSSQL\Data ( LDF File)

Method one:

Remove the database from AG.
Detach the database.
Move the files physically to new location(s).
Attach the database.
Rejoin the database in your AG.

Method Two:
In this method we shall see how to move files without removing the database from AG.

Steps to perform in sequence:

Suspend data movement for the database which you are working on to all replicas.
Logically move the database files(On all the Replicas).
Stop the SQL Server services. – This step will create an outage for all other databases residing on this instance.
Now move the physical MDF and LDF files to your new location.
Start the SQL service from config manager.
Resume data movement.

Before proceeding any further, I made sure the new path exists on of my  replicas.

1.

2

2. Run this on all the replicas.

3

Now…I stopped SQL Services and moved physical files to new locations and started SQL Server.

3. Now resume data movement.

4

Voila…Now it’s all set as per my requirement.

5

That is it folks for today! Have fun…

Lab setup – AlwaysOn AGs in a Multi Subnet Cluster – Part 2

In part1, I’ve shown how to create a windows cluster in a multi subnet setup.  In this post let’s see how to create a AG and corresponding listener.

In my lab, I will be creating two AGs and two corresponding listeners.

Details:
I’ve two databases – > sales and customers.
Two AGs – > Sales_AG and Customers_AG.Two Listners – > sqllst_Sales and sqllst_Cust.
For sales AG, I’ve disabled “Database level health detection”, a new feature introduced in SQL 2016.

AG1

ag2

Now, for Listener two IPs from both subnets have been provided.

ag3

ag5

Same process has been followed for creating customers AG and listener as well(But this time I’ve enabled Database level health detection).

ag4

Since this is a multi-subnet setup two entries(one from each subnet) will be created in DNS for each listener name as shown below.

ag_final

That’s about it folks.

Lab setup – AlwaysOn AGs in a Multi Subnet Cluster – Part 1

Let’s see how to setup an AG(SQL 2016) in a multi subnet cluster(Geo cluster) in a lab environment.

Below is my lab setup:

Two Replicas sitting in my Production Data center.(Subnet 192.168.1.x) – Sync Mode Automatic Failover.
Third(Far) Replica sitting in my DR Data Center.(Subnet 192.168.2.x) – Asynch Mode Manual Failover.

So, What do we need to be able to setup multiple subnets and routing in a lab environment? Answer is “Routing and Remote Access“. Have that installed by going to Add Roles/Features on your AD/DNS server.

Pre-req Step: Created 2 NICs on my SANDC machine with IPs 192.168.1.100 and 192.168.2.100

Open Routing and remote access config tool; right click on the root node and select “Enable and Configure Routing and remote access”.

Now…under IPv4, under General right click and select new routing protocol and select “RIP Version 2 for Internet Protocol”.

1

Now right click on RIP and select new interface and select your NIC1 and hit okay and next repeat the same step and select NIC2 this time and click okay. You are done with routing…That’s all you need for routing to work(As long as you got all the IPs and DNS details right).

2

4

3

5

Now, I’ve setup 3 nodes(two nodes(Prd) in 1.x and one(DR) in 2.x) and installed Failover Cluster feature on all the nodes and disabled all firewalls.

My PRD nodes: STLSQLAG1 and STLSQLAG2.
My DR Node:AZSQLAG3

Do find all my NIC settings from all my nodes at the very end of this post.

Now, Let’s create Windows Cluster:

Please refer to my earlier posts on how to create a cluster under “Clustering category” for detailed steps. Below are the steps at a high level.

clu1

clu2

clu3

Now…My cluster is ready, but it’s missing Quorum which is very critical for a cluster to be healthy. For that I’ve created a File share witness as Quorum.

clu4

clu5

FYI, NIC settings from Cluster manager are shown below.

clu6

clu7

NIC settings on all of my nodes:

STLSQLAG1: (1 NIC Card)

nic1

STLSQLAG2: ( 1 NIC Card)

nic2

AZSQLAG3(DR Server): 1 NIC Card

nic_AZ_DR

AD/DNS Server: ( 2 NICs one for 1.x and other for 2.x)

nic3_sandc

nic4_sandc

In this post we’ve seen how to setup a geo cluster in a lab environment. So, this completes the prep work needed from Windows stand point…Let’s see how to create AGs and Listeners in our Multi Subnet environment in next part of this series.

Setting up TDE on SQL Server Failover Cluster

Back in 2011 I wrote couple of Blog posts when I was initially exploring Encryption options we have in SQL Server. Never really got a chance to work on TDE since then. Fast forward to 2016, I am participating in TDE project, where we are enabling TDE for few of our databases which are hosted on SQL Server Failover Clustered Instances. Currently we are in testting/POC phase. In this post, I will share what my findings are about TDE on clustered instances. Let’s get started…

Enabling TDE on a clustered database is no different than how we enable on a Standalone Instance. Steps are as follows:

--a.
Create database TDE_Test
GO
--1.create a master key in master database.
USE master
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Very$ecurepwd123'
--2.Create a certificate(name it)
USE master
GO
CREATE CERTIFICATE CLUSTPRD1_TDECert
WITH SUBJECT = 'Transparent Data Encryption Certificate'
--3. Create a database encryption key. –Based on the certificate from Step 2–Can use AES, DES, Triple DES, RC4 etc
USE TDE_Test--The db to protect
GO
CREATE DATABASE ENCRYPTION KEY WITH
ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE
CLUSTPRD1_TDECert--Cert from Step 2

--4. Turn it on!
USE TDE_Test--The db to protect
GO
ALTER DATABASE TDE_Test
SET ENCRYPTION ON
GO
-----Backup Certs---V V Imp
USE MASTER  
GO  
BACKUP CERTIFICATE CLUSTPRD1_TDECert   
TO FILE = 'C:\TDE_Keys\CLUSTPRD1_TDECert.cer'  
WITH PRIVATE KEY (FILE = 'C:\TDE_Keys\CLUSTPRD1_TDECert_Key.pvk' ,  
ENCRYPTION BY PASSWORD = 'MyAw3S0m3Pwd123#' )  
GO
--Backup SMK---VVV Important</pre>

BACKUP SERVICE MASTER KEY TO FILE = 'c:\keys\SQLPRD1_SMK.bak' ENCRYPTION BY PASSWORD = 'gytj6%&5gjOUytp';

Okay…so what happens when SQL gets failed over to another node? Will TDE work? Will the database be still in usable state? The answer is “YES” from my initial testing. Everything remains intact. Nothing breaks!

Note: This is different from the way how it works in Alwayson AGs.

Reason: See below Screenshot taken from BOL.

3_arch

The Master Key which we created on step 1 is protected by Service master key(SMK) which is scoped at Instance level is the root of our Hierarchy, And then the certificate and the keys are scoped at master and user database levels which will be failed over and moved between nodes.So….Nothing special is required for FCIs. Please correct me If I am wrong and post any thoughts in comments.

Cheers!

SQL Server 2016 Cluster setup – What’s changed?

In this short blog post let’s take a screenshots tour of SQL Server 2016 Failover cluster installation and see if Microsoft made any significant changes to the setup process.

1

2

3

4

In the above two screenshots, I’ve provided my VNN and Instance name for SQL.

5

Add your Disks as needed.

6

provide your IP address.

7

Enter your Service accounts.

8

Select your Database directories accordingly and go to TempDB tab to check SQL installation wizard creating tempdb data files based on the number of CPUs you have got.

9

10

11

This is the first node of my cluster setup, hence you can see PRDSQLTREKA as the only node listed in the above screenshot. Next Next Done.

Now on the second node:

Select Add node to SQL Server Failover cluster and Next-Next-Next, provide service account and done.

12

13

Basically nothing has been changed specific to Cluster setup. If you are comfortable with earlier cluster setups, then you should be golden. Cheers!

 

SQL Server 2016 AlwaysON AG setup – Part 2

Now that I’ve created a windows cluster and installed SQL server on all the three nodes(Part 1), I am ready to configure AGs. Below is the screenshots tour.

1.JPG

You can see  a new option “Database Level Health Protection” (In CTP releases It had “Basic AG”and “Per Database DTC Support” options as well.). Now, on Enterprise/Eval/Developer editions, all we get is above option. If you are trying to setup AG on standard edition(Wait till RTM gets released), then You should be seeing an option for Basic AG(Well, only Basic AG can be configured on standard edition)

So, what’s DB level health protection? If one of your databases in your AG goes offline or becomes unavailable for some reason, entire AG doesn’t get impacted.

Next:

2

3.JPG

Here’s the bonus, Now we get 3 Synchronous(Automatic) replicas and Active secondaries are Load balanced with your routing list and also distributed transactions are now supported in 2016 AGs:)

Now, pick your backup preferences.

4.JPG

And finally configure Listener:

6.JPG

Below are the corresponding CNOs/VCOs/DNS records created in my AD.

From my SSMS:

9

That’s it folks! Happy weekend…

SQL Server 2016 AlwaysON AG setup – Part 1

This is a quick guide on how to configure AlwaysON AGs on SQL Server 2016. This blog post isn’t a thorough walk through, My goal in this post is to show you guys what’s different in SQL 2016 comparing with 2012 or 2014. (For detailed information on how to setup AGs in your own lab, please refer to my previous posts on SQL 2012).

First and foremost, I’ve built a windows cluster with 3 nodes(No shared Storage) for setting up AGs(See below).

a

Starting from Node SQLTrekAG1, Inserted SQL Server 2016 RC2 media, hit setup.exe…basic sanity checks…next next…blah blah.

1

One thing you might have already noticed, all our management tools and Data tools has a separate link to install. Well, This is a huge change(I would say improvement, YMMV), in earlier versions we had option to select Client tools when selecting features and IIRC data tools was a separate download all together in SQL 2014.

Next:

2.JPG

As mentioned earlier we no longer see client tools(basic and complete) in features selection.

Next:

3.JPG

One more amazing enhancement, now it always remind DBAs about IFI:)

Next:

4.JPG

Choose your dirs and if you go to tempdb tab, you can see that now SQL Server is smart enough to identify number of CPUs on the box and will choose number of data files for your tempdb.

Everything else should be similar to what we had in previous versions. Now after performing a standalone installation successfully, you can see SSMS hasn’t been installed.

5.JPG

Now let’s install SSMS on this machine:

All I had to do was double click setup.exe and on Installation tab, select “Install SQL Server Management Tools”and……Oops!Bummer….! Did I already mention, you need to have Internet connection to be able to do this. Well, see below screenshot carefully again.

6

It downloads an 815 MB file(SSMS-Setup-enu.exe), now that’s the media for installing SSMS and other SQL utilities! Store it in your media library, from now on we don’t need internet connection.

Note: You can directly go to https://msdn.microsoft.com/en-us/library/mt238290.aspx for downloading the same(As of 2016 RC2).

Run that executable…I got a warning about shared components already installed(I am good with that) and next to Install. That’s it!

It installs .NET framework 4.6.1(took a while), Report viewer, batch parser, SSMS, SQLCMD, BCP, SMOs, Visual studio 2015 Shell(took its sweet time), SQLPS,Profiler,DTA and all those goodies. Restart if required.

Now, I enabled AGs from SQL Config manager.

9.JPG

I will install SQL by performing the same on all the three nodes and will come up with part-2 later where I setup an actual AG. Cheers!