79% of analytics users encounter data questions they can't solve each month. How are you helping your team to uncover insights from data?

Moving data isn't always easy. There are more than 340 types of databases in use today and moving data across them presents challenges for any IT team. At CloudIQ Technologies, we have years of experience helping businesses find the IT solutions that can keep up with their constantly evolving business practice. Whether you need a solution for storage, data transfer, or just need to gain better insights from your data, we can help.

Deadlocks in Azure SQL Database

Recently we were working with Azure Logic Apps to invoke Azure Functions.
By Default, Logic App runs parallel threads and we didn’t explicitly control the concurrency and left the default values.

So Logic App invoked several concurrent threads which in turn invoked several Azure Functions.
The problem was Azure Functions invoked Database Calls which caused Deadlocks. In Ideal world, Database should be able to handle numerous concurrent functions without deadlocks. Our process high percentage of shared data and we wanted to ensure the consistency , so we had Explicit Transactions in our Stored procedure calls. That’s the root cause of the problem and we didn’t want to remove the explicit Transaction.

The solution we implemented to alleviate this problem is to run this process in Sequence instead of parallel threads.

Log App Concurrency Control Behavior

For each loops execute in parallel by default. Customize the degree of parallelism, or set it to 1 to execute in sequence.

Logic_App_Concurrency

Deadlock Exception

Transaction (Process ID 166) was deadlocked on lock
| communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Deadlock_Exception

Troubleshooting Deadlocks

So we have identified Deadlock happened in the database through our Application Insights. Next logical question is , what caused this deadlock.

Azure SQL Server Deadlock Count

These queries identifies the deadlock event time as well as the deadlock event details.

                
        SELECT * FROM sys.event_log   
        WHERE event_type = 'deadlock';
        WITH CTE AS (  
        SELECT CAST(event_data AS XML)  AS [target_data_XML]   
        FROM sys.fn_xe_telemetry_blob_target_read_file('dl', 
        null, null, null)  
        )  
        SELECT target_data_XML.value('(/event/@timestamp)[1]', 
        'DateTime2') AS Timestamp,  
        target_data_XML.query('/event/data[@name=''xml_report'']
        /value/deadlock') AS deadlock_xml,  
        target_data_XML.query('/event/data[@name=''database_name'']
        /value').value('(/value)[1]', 'nvarchar(100)') AS db_name  
        FROM CTE
                
        

You can save the Deadlock xml as xdl to view the Deadlock Diagram. This provides all the information we need to identify the root cause of the deadlock and take necessary steps to resolve the issue.

References

Install and Run SQL Server Docker Container on Mac

Like most people, I use Mac , Windows as well Linux OS for development and testing purposes. Primarily I use Mac for Development purpose. I have few projects which uses SQL Server as Data Storage Layer. Setting up Docker Container on Mac and Opening up the ports was pretty easy and doesn’t take more than 10 Minutes.

Steps followed :
  • Install Docker
  • Pull SQL Server Docker Image
  • Run SQL Server Docker Image
  • Install mssql Client
  • Install Kitematic
  • Open the Ports to connect to SQL Server from the network
  • Setup port forwarding to enable access outside the network
Install Docker :

Get Docker dmg image and install. Just follow the prompts and its very straight forward. 
https://docs.docker.com/docker-for-mac/install/#download-docker-for-mac https://download.docker.com/mac/stable/Docker.dmg

Once you have installed docker , you can verify the installation and version.

                bash-3.2$ docker -v
        Docker version 17.09.0-ce, build afdb6d4 
Pull SQL Server Docker Image ( DEV Version )
                docker pull microsoft/mssql-server-linux:2017-latest 
Create SQL Server Container from the Image and Expose it on port 1433 ( Default Port )
                docker run -d --name macsqlserver -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Passw1rd' -e 'MSSQL_PID=Developer' -p 1433:1433 microsoft/mssql-server-linux:2017-latest 

-d: this launches the container in daemon mode, so it runs in the background

--name name_your_container (macsqlserver): give your Docker container a friendly name, which is useful for stopping and starting containers from the Terminal.

-e 'ACCEPT_EULA=Y: this sets an environment variable in the container named ACCEPT_EULAto the value Y. This is required to run SQL Server for Linux.

-e 'SA_PASSWORD=Passw1rd': this sets an environment variable for the sa database password. Set this to your own strong password. Also required.

-e 'MSSQL_PID=Developer': this sets an environment variable to instruct SQL Server to run as the Developer Edition.

-p 1433:1433: this maps the local port 1433 to the container’s port 1433. SQL Server, by default, listens for connections on TCP port 1433.

microsoft/mssql-server-linux: this final parameter tells Docker which image to use

Install SQL Client for MAC

If you don’t have npm installed in Mac, install homebrew and node.

                ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
        brew install node
        node -v
        npm -v 
                $ npm install -g sql-cli
         
        /usr/local/bin/mssql -> /usr/local/lib/node_modules/sql-cli/bin/mssql
        /usr/local/lib
        └── [email protected]
         
        $ npm i -g npm 
Connect to SQL Server Instance
                $ mssql -u sa -p Passw1rd
        Connecting to localhost...done
         
        sql-cli version 0.6.2
        Enter ".help" for usage hints.
        mssql> select * from sys.dm_exec_connections 
Get External Tools to Manage Docker

Kitematic

https://kitematic.com/

Open Up the Firewall to connect to SQL Server from outside the Host

Ensure your firewall is configured to allow the connections to the SQL Server. I turned of “Block all incoming connections” and enabled “Automatically allow downloaded signed software to receive incoming connections”. Without proper firewall configurations, you won’t be able to connect to the SQL Server outside the host.

Ensure Firewall allows the incoming connections to the Docker
Connecting from the Internet ( Port forwarding Setup )

Lets say you want to connect to the SQL Server you setup from outside the network or from anywhere via internet,you can setup port forwarding.

Get your public facing IP and setup the port forwarding for Port 1433 ( SQL Server port you have setup your docker container ). If its setup correctly , you should be able to telnet into that port to verify the connectivity.

        telnet 69.11.122.159 1433 

 Unless you absolutely require it , its very bad idea to expose the SQL Server to internet. It should be behind the network , only your webserver should be accessible via internet.

Troubleshooting :

While launching docker container , if you get the error saying that it doesn’t have enough memory to launch SQL Server Container, go ahead and modify the memory allocation for docker container.

  • This image requires Docker Engine 1.8+ in any of their supported platforms.
  • At least 3.25 GB of RAM. Make sure to assign enough memory to the Docker VM if you’re running on Docker for Mac or Windows.

I have setup this way.

Docker Memory configs

If you don’t provision enough memory, you will error like this.

Docker SQL Server Memory Error
Look into Docker logs

Following command ( docker ps -a and docker logs mcsqlserver ) shows list of running processes and docker logs.

        $ docker ps -a
CONTAINER ID        IMAGE                                      COMMAND                  CREATED             STATUS              PORTS                    NAMES
9ea3a24563f9        microsoft/mssql-server-linux:2017-latest   "/bin/sh -c /opt/m..."   About an hour ago   Up About an hour    0.0.0.0:1433->1433/tcp   macsqlserver
$ docker logs macsqlserver
2017-10-08 23:06:52.29 Server      Setup step is copying system data file 
'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2017-10-08 23:06:52.36 Server      Setup step is copying system data file 
'C:\templatedata\mastlog.ldf' to '/var/opt/mssql/data/mastlog.ldf'.
2017-10-08 23:06:52.36 Server      Setup step is copying system data file 
'C:\templatedata\model.mdf' to '/var/opt/mssql/data/model.mdf'.
2017-10-08 23:06:52.38 Server      Setup step is copying system data file 
'C:\templatedata\modellog.ldf' to '/var/opt/mssql/data/modellog.ldf'.
 
Security:

I highly recommend to create least privileged accounts and disable SA login. If you are exposing your SQL Server to internet, there are ton of hacking and pentest tools which uses sa login for brute force attack.

Windows Containers do not ship with Active Directory support and due to their nature can’t (yet) act as a full-fledged domain joined objects, but a certain level of Active Directory functionality can be supported through the use of Globally Managed Service Accounts (GMSA).

Windows Containers cannot be domain-joined, they can also take advantage of Active Directory domain identities similar to when a device is realm-joined. With Windows Server 2012 R2 domain controllers, we introduced a new domain account called a group Managed Service Account (GMSA) which was designed to be shared by services.

https://blogs.technet.microsoft.com/askpfeplat/2012/12/16/windows-server-2012-group-managed-service-accounts/

https://technet.microsoft.com/en-us/library/hh831782(v=ws.11).aspx

We can authenticate to Active Directory resources from Windows container which is not part of your domain. For this to work certain prerequisites needs to be met.

For once your container hosts shall be part of Active Directory and you shall be able to utilize Group Managed Service Accounts.
https://technet.microsoft.com/en-us/library/hh831782%28v=ws.11%29.aspx?f=255&MSPPError=-2147217396

The following steps needed for communicate Windows container with on premise SQL server using GMSA.
Environments are used and described for this post.

  1. Active directory Domain Controller installed on server CloudIQDC1.
    • OS – Windows Server 2012/2016.
    • The domain name is cloudiq.local
  2. Below are the Domain members (Computers) joined in DC
    • CIQ-2012R2-DEV
    • CIQSQL2012
    • CIQ-WIN2016-DKR
    • cloud-2016
  3. SQL server installed on CIQSQL2012. This will be used for GMSA testing.
    • OS – Windows 2012
  4. cloud-2016 will be used to test GSMA connection.
    • This is the container host we are using to connect on premise SQL server using GMSA account.

  5. The GMSA account name is “container_gsma”. We will create this and configure it.
Step 1: Create the KDS Root Key
  1. We can generate this only once per domain.
  2. This is used by the KDS service on DCs (along with other information) to generate passwords.
  3. Login to domain controller.
  4. Open PowerShell and execute the below.
                            Import-module ActiveDirectory
        Add-KdsRootKey –EffectiveTime ((get-date).addhours(-10));5.
         

  5. Verify your key using the below command.
            Get-KdsRootKey
         
Step 2: Create GMSA account
  1. Create GSMA account using the below command.
            
        New-ADServiceAccount -Name container_gmsa -DNSHostName cloudiq.local 
        -PrincipalsAllowedToRetrieveManagedPassword "Domain Controllers", "domain admins", 
        "CN=Container Hosts,CN=Builtin, DC=cloudiq, DC=local" -KerberosEncryptionType RC4, AES128, AES256
         

  2. Use below command to verify the created GMSA account.
            Get-ADServiceAccount -Identity container_gmsa 
  3. If everything works as expected, you’ll notice a new gMSA object in your domain’s Managed Service Account.
Step 3: Add GMSA account to Servers where we are going to use.
  1. Open the Active directory Admin Center.
  2. Select the container_gmsa account and click on properties.
  3. Select the security and click on add.
  4. Select only Computers
  5. Select Computers you want to use GMSA. In our case we need to add CIQSQL2012 and cloud-2016.
  6. Reboot Domain controller first to these changes take effect.
  7. Reboot the computers who will be using GMSA. In our case we need to reboot CIQSQL2012 and cloud-2016.
  8. After reboots, login to Domain controller. Execute the below command.
            
        Set-ADServiceAccount -Identity container_gmsa -PrincipalsAllowedToRetrieveManagedPassword 
        CloudIQDC1$,cloud-2016$, CIQSQL2012$
         

Step 4: Install GMSA Account on Servers
  1. Login to the system where the GMSA account which will use it. In our case login to cloud-2016. This is the container host we are using to connect on premise SQL server using GMSA account.
  2. Execute the below command if AD features are not available.
            
        Enable-WindowsOptionalFeature -FeatureName ActiveDirectory-Powershell -online -all
         
  3. Execute the below commands
            Get-ADServiceAccount -Identity container_gmsa
        Install-ADServiceAccount -Identity container_gmsa
        Test-AdServiceAccount -Identity container_gmsa 

  4. If everything is working as expected then you need to create credential spec file which need passed to docker during container creation to utilize this service account. Run the below commands to downloads module which will create this file from Microsoft github account and will create a JSON file containing required data.
            
        Invoke-WebRequest "https://raw.githubusercontent.com/Microsoft/Virtualization-Documentation/live/windows-server-container-tools/ServiceAccounts/CredentialSpec.psm1" 
        -UseBasicParsing -OutFile $env:TEMP\cred.psm1
        Import-Module $env:temp\cred.psm1
        New-CredentialSpec -Name Gmsa -AccountName container_gmsa
        #This will return location and name of JSON file
        Get-CredentialSpec 

Step 5: SQL Server Configuration to allow GMSA
  1. On SQL server create login for GMSA account and add it to "Sysadmin" role. Based on your on premise DB access, you can add suitable roles.
            CREATE LOGIN [cloudiq\container_gmsa$] FROM WINDOWS
        sp_addsrvRolemember "cloudiq\container_gmsa$", "sysadmin" 

CloudIQ is a leading Cloud Consulting and Solutions firm that helps businesses solve today’s problems and plan the enterprise of tomorrow by integrating intelligent cloud solutions. We help you leverage the technologies that make your people more productive, your infrastructure more intelligent, and your business more profitable. 

US

626 120th Ave NE, B102, Bellevue,

WA, 98005.

INDIA

Chennai One IT SEZ,

Module No:5-C, Phase ll, 2nd Floor, North Block, Pallavaram-Thoraipakkam 200 ft road, Thoraipakkam, Chennai – 600097


© 2020 CloudIQ Technologies. All rights reserved.

Get in touch

Please contact us using the form below

USA

626 120th Ave NE, B102, Bellevue, WA, 98005.

+1 (206) 203-4151

INDIA

Chennai One IT SEZ,

Module No:5-C, Phase ll, 2nd Floor, North Block, Pallavaram-Thoraipakkam 200 ft road, Thoraipakkam, Chennai – 600097

+91-044-48651163