SQL Server on Docker containers – Welcome to the present!

In this article let’s see how to install a SQL Server Instance using a Docker container in Windows server 2016.

“Containers” is probably one of the hottest buzz words talked about these days in IT world, especially Docker containers. So, what exactly is Docker? Well, Docker is one of the few companies who develops, builds and distributes container technologies. It’s a pioneer in container technologies, similar to VMWare in virtualization technologies. I won’t be going into internals and architecture of how docker container works under the hoods in this article, instead lets just focus on installing docker containers inside windows server 2016 and then setup the latest version of SQL Server Instance on top of it.

One can install dockers in windows 10 (should be enterprise or professional with anniversary update or later and Hyper-V enabled.) or in Windows server 2016, In our case we will be using Windows Server 2016 for this purpose.

Installing Docker services on windows server 2016

In order to install Docker suite, first we have to enable containers feature in windows server 2016 and install Microsoft management package provider for docker. Okay, let’s get into action. To begin with, I created a Windows server 2016 Virtual machine and enabled direct internet access to the VM.
For complete article, see below…
https://www.sqlshack.com/sql-server-with-a-docker-container-on-windows-server-2016/

 

Simulating a Multi Subnet cluster for setting up SQL Server Always On Availability Groups – lab setup

In this article, we are going to see how to create a multi subnet cluster spanning across multiple subnets for lab purposes. Creating such an environment should help creating Availability groups simulating a far replica residing in a different Data Center (Subnet) acting as a disaster recovery site in your lab for learning/experimenting real world scenarios.

Let’s get into action, below is what we are going to setup for our lab purposes to simulate a multi subnet cluster environment and create an Always On Availability group.

  • Two nodes (Replicas) residing in my production/primary Data center (Subnet 192.168.1.x) – Let’s say these two replicas are used for High availability, assuming we have low latency, since they are residing in the same data center (Probably in the same rack), I will set them up with Synchronous Mode with Automatic Failover
  • Third node (Replica) sitting in my disaster recovery data center which is geographically dispersed (Subnet 192.168.2.x) – Assuming it’s connected over WAN and is used just for DR purposes, I would set this replica in Asynchronous Mode with Manual Failover

For more reading…go to below link.

https://www.sqlshack.com/simulating-a-multi-subnet-cluster-for-setting-up-sql-server-always-on-availability-groups-lab-setup/

SSMS 18.0 – SQL Server Priority Boost

I’ve been playing with SQL Server 2019/SSMS 18.0 since past few days and noticed something really awesome, there’s no more “Boost SQL Server Priority” option is SSMS 18.0 !!!! It’s probably one of the most requested feature in SSMS by DBA community.

The name “Boost SQL Server Priority” sounds like an amazing switch which makes SQL run on steroids, but in reality it’s the other way around.

So, what exactly does this option do?
Ans: It doesn’t matter in year 2019, Let’s just pretend we never had this option 🙂  Well, If you are still curious what this is about, enabling this setting will make SQL Server process run in “HIGH_PRIORITY_CLASS”. Almost every process in windows run in “NORMAL_EXECUTION_CONTEXT” including network drivers, Storage drivers, I/O drivers and etc etc etc. In other words, you are making SQL Server run in higher execution context on CPU than almost any other process on your server.

So, when do we need to enable this on our SQL Servers?
Ans: Never. There’s a reason why microsoft removed this option from SSMS.

Enough background, see what I am talking about in the below screenshots.

Cheers!

SQL Server 2019 CTP 2.3 is here…

Microsoft announced SQL Server 2019 CTP 2.3 in SQL bits (If you are not aware of what SQL bits is, it’s European version of SQL PASS) Keynote on Friday, first of March 2019. I really didn’t got chance to Install SQL 2019 initial release of CTP when it got released sometime around  Sep 2018, In this blog post let’s take a quick Screenshot tour of Installing SQL Server 2019 CTP 2.3 on Windows Server 2016.

First thing first, we need to download media from Microsoft.
https://www.microsoft.com/en-us/sql-server/sql-server-2019#Install

In this case, I am installing on Win Server 2016, So I picked Option 1.

As highlighted in the above screenshot, I opted to download ISO file and have a copy for myself so that I can use this to Install on a different machine in future without any hassles.  Well, It’s all done in few mins…

Now, let’s go to the actual Installation part.

1.
2.

3.

4. I am interested in just the DB Engine for now.

5. Provided my service accounts

6. Time to configure Data Directories and don’t forget to visit TempDB tab.

7. Next—-> Next and Done!

Nothing fancy here with Installation. Now, I downloaded and Installed latest release preview of SSMS which supports SQL Server 2019 and below is the output of @@Version.

Note: If you have previous releases of 2019 CTP installed on the same machine, you can’t perform an Inplace upgrade to CTP 2.3, However you can have them side by side without any issues. In other words, It’s not required to uninstall previous release of CTP in order to Install 2019 CTP 2.3 as a different Instance.

BTW, my favorite feature of this release is “Accelerated DB Recovery and Instant Rollback for long running transactions”. There are few other new features added related to SSAS and Big data Clusters as well, but I am really not interested in those at this moment.

Cheers!

Merry Christmas – From SQL Server

Below is the script which you can run directly in your SSMS and SQL Server (With the power of  Spatial results) sends you Christmas wishes directly on your screen 🙂


USE tempdb
GO


-- Prepare the scene

CREATE TABLE #ChristmasScene

(

item VARCHAR(32)

,shape GEOMETRY
) ;

--Put up the tree and star

INSERT INTO #ChristmasScene

VALUES ( 'Tree',

'POLYGON((4 0, 0 0, 3 2, 1 2, 3 4, 1 4, 3 6, 2 6, 4 8, 6 6, 5 6, 7 4, 5 4, 7 2, 5 2, 8 0, 4 0))' ),

( 'Base', 'POLYGON((2.5 0, 3 -1, 5 -1, 5.5 0, 2.5 0))' ),

( 'Star',

'POLYGON((4 7.5, 3.5 7.25, 3.6 7.9, 3.1 8.2, 3.8 8.2, 4 8.9, 4.2 8.2, 4.9 8.2, 4.4 7.9, 4.5 7.25, 4 7.5))' )

--Decorate the tree

DECLARE @i INT = 0

,@x INT

,@y INT ;

WHILE ( @i < 20 )

BEGIN

INSERT INTO #ChristmasScene

VALUES ( 'Bauble' + CAST(@i AS VARCHAR(8)),

GEOMETRY::Point(RAND() * 5 + 1.5, RAND() * 6, 0).STBuffer(0.3) )

SET @i = @i + 1 ;

END

--Christmas Greeting

INSERT INTO #ChristmasScene

VALUES ( 'M',

'POLYGON((0 10, 0 11, 0.25 11, 0.5 10.5, 0.75 11, 1 11, 1 10, 0.75 10, 0.75 10.7, 0.5 10.2, 0.25 10.7, 0.25 10, 0 10))' ),

( 'E',

'POLYGON((1 10, 1 11, 2 11, 2 10.8, 1.25 10.8, 1.25 10.6, 1.75 10.6, 1.75 10.4, 1.25 10.4, 1.25 10.2, 2 10.2, 2 10, 1 10))' ),

( 'R',

'POLYGON((2 10, 2 11, 3 11, 3 10.5, 2.4 10.5, 3 10, 2.7 10, 2.2 10.4, 2.2 10, 2 10),(2.2 10.8, 2.8 10.8, 2.8 10.7, 2.2 10.7, 2.2 10.8))' ),

( 'R',

'POLYGON((3 10, 3 11, 4 11, 4 10.5, 3.4 10.5, 4 10, 3.7 10, 3.2 10.4, 3.2 10, 3 10),(3.2 10.8, 3.8 10.8, 3.8 10.7, 3.2 10.7, 3.2 10.8))' ),

( 'Y',

'POLYGON((4 11, 4.2 11, 4.5 10.6, 4.8 11, 5 11, 4.6 10.5, 4.6 10, 4.4 10, 4.4 10.5, 4 11))' ),

( 'C',

'POLYGON((0 9, 0 10, 1 10, 1 9.8, 0.2 9.8, 0.2 9.2, 1 9.2, 1 9, 0 9))' ),

( 'H',

'POLYGON((1 9, 1 10, 1.2 10, 1.2 9.6, 1.8 9.6, 1.8 10, 2 10, 2 9, 1.8 9, 1.8 9.4, 1.2 9.4, 1.2 9, 1 9))' ),

( 'R',

'POLYGON((2 9, 2 10, 3 10, 3 9.5, 2.4 9.5, 3 9, 2.7 9, 2.2 9.4, 2.2 9, 2 9),(2.2 9.8, 2.8 9.8, 2.8 9.7, 2.2 9.7, 2.2 9.8))' ),

( 'I',

'POLYGON((3.2 9, 3.2 9.2, 3.4 9.2, 3.4 9.8, 3.2 9.8, 3.2 10, 3.8 10, 3.8 9.8, 3.6 9.8, 3.6 9.2, 3.8 9.2, 3.8 9, 3.2 9))' ),

( 'S',

'POLYGON((4 9, 4 9.2, 4.8 9.2, 4.8 9.4, 4 9.4, 4 10, 5 10, 5 9.8, 4.2 9.8, 4.2 9.6, 5 9.6, 5 9, 4 9))' ),

( 'T',

'POLYGON((5 9.8, 5 10, 6 10, 6 9.8, 5.6 9.8, 5.6 9, 5.4 9, 5.4 9.8, 5 9.8))' ),

( 'M',

'POLYGON((6 9, 6 10, 6.25 10, 6.5 9.5, 6.75 10, 7 10, 7 9, 6.75 9, 6.75 9.7, 6.5 9.2, 6.25 9.7, 6.25 9, 6 9))' ),

( 'A',

'POLYGON((7 9, 7 10, 8 10, 8 9, 7.75 9, 7.75 9.3, 7.25 9.3, 7.25 9, 7 9),(7.25 9.5, 7.25 9.8, 7.75 9.8, 7.75 9.5, 7.25 9.5))' ),

( 'S',

'POLYGON((8 9, 8 9.2, 8.8 9.2, 8.8 9.4, 8 9.4, 8 10, 9 10, 9 9.8, 8.2 9.8, 8.2 9.6, 9 9.6, 9 9, 8 9))' ) ;

--Admire the scene

SELECT *

FROM #ChristmasScene -- Tidy up the pine needles and put away the decorations

DROP TABLE #ChristmasScene

After letting it run (Shouldn’t take more than a second), navigate to ‘spatial results’ tab in your results pane and voila…..

The total credit goes to the original author of this script. Unfortunately, I don’t have his/her name, I saved this script years ago in my tool set.

Wishing you all Merry Christmas and a very happy new year 🙂

SSMS – Run as a different user shortcut

It’s always a good practice to have a separate account from your regular AD account (which you use to login to your workstation) when working with SQL Server, especially for performing admin related tasks. In this blog post, I will show you a simple trick how to create a shortcut for SSMS on your desktop which opens the application with different credentials.

Let’s say I am a DBA and I have two separate domain accounts, ‘SQLTREK\Sreekanth’ (Which I use to login to my Workstation/laptop) and ‘SQLTREK\Sreekanth_adm’ (Which I should be using when I connect to my SQL Servers). In this case, If I want to connect to a SQL Instance from my local SSMS installed on my laptop, what I could do is right click on SSMS(holding shift key) and select ‘Run as different user’ and provide SQLTREK\Sreekanth_adm credentials. Well, what If I don’t want to go through this process each and every time I close and re-open SSMS? Let’s see how we can achieve this.

Right click anywhere on your desktop and create a new shortcut

Now locate your SSMS.exe path on your machine and prefix with runas.exe /user:Diffuser, See below for exact syntax.

My SSMS path:
“C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe”

So, My Run as command which I should use to create shortcut is
C:\Windows\System32\runas.exe /user:SQLTREK\Sreekanth_ADM ” C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe “

This will open your SSMS.exe application as user ‘SQLTREK\Sreekanth_ADM’ instead of my regular account. Even better, if you don’t want to enter the password each time you open SSMS, you can pass /SaveCred switch. In that case, this is what I would use.

C:\Windows\System32\runas.exe /user:SQLTREK\Sreekanth_ADM /savecred ” C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe “

Now, give your shortcut a name.

Now I have the shortcut created, but it looks ugly with no icon. Well, let’s make it fancy…right click on the shortcut and go to properties and select change Icon button.

Now, click on browse and go to the folder where SSMS is installed.

In my case it’s “C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio” and select “ssms.ico” and hit open–>OK–>Apply–>OK.

Now I have my fancy looking SSMS shortcut with my admin credentials saved.

From now on I don’t have to hold shift key and select run as diff user and provide credentials each and every time I want to connect to my SQL Instances locally from my machine.

Hope this helps, Cheers!

Note: Using /savecred is a bad security practice.

SQL Server with Cluster Shared volumes (CSV) – Part 2

In the previous part of this series, we have seen what a cluster shared volume is and what are the advantages and other considerations to keep in mind when deploying CSVs for SQL Server workloads. In this article, I will walk though actual installation of a failover cluster Instance leveraging CSVs.

To begin with, I will walk you through my cluster setup from 20,000 foot view. I created two brand new VMs running windows server 2012 R2 and renamed them accordingly. Nothing special w.r.t disk drives at this point, Just basic VMs with a system drive(C$).

I also created 2 virtual networks in my VMWare workstation which I will be using for configuring my Public and private NICs on my nodes.

On my first node:

192.168.1.100 is the IPv4 address of my DNS server and below are my NIC settings.

Once IP address has been configured, below is how I joined my node to domain (sqltrek.local in my case).

Further reading: Deploy SQL with CSVs – Part 2