Amazon Web Services EC2 is a great IaaS Platform and many organizations use it to host SQL Server instances. Being IaaS platform is opens up easy migration scenarios from on-premises deployments and for many people its a great first step into cloud. However, hopefully soooner than later, many companies realize that they are ready to take next step – move their workloads and databases to PaaS cloud service. This is where Microsoft SQL Azure DB shines.

Microsoft AzureSQL Database (formerly SQL Azure, Windows Azure SQL Database) is a cloud-based database service from Microsoft offering data-storage capabilities. The aim is for users to just communicate with a T-SQL endpoint rather than managing database storage, files, and high availability. Azure SQL Database obviously has number of limitations like lack of support of cross database queries, SQL Broker, etc. meaning that not every database can be migrated to Microsoft Azure SQL DB without prerequisite work. However, for folks that are ready to migrate Microsoft Data Migration Assistant can be viable option for such migration from AWS EC2 IaaS.

For my tutorial I have SQL Server on Windows instance in EC2 running well known venerable AdventureWorks2016 database. Here it is amongst my other servers in EC2 console:

With AWS elastic IP assigned to that Windows machine I can easily connect to default SQL instance via my local SSMS:

Obviously previously I had to setup proper inbound rules for this machine both with AWS EC2 Security Groups and Windows Firewall.

I also had to again go to security group for the server and open inbound ports for SQL Server traffic again (port 1433). Smart way to do it is to filter by client IP of my workstation where I run my SSMS and will run Data Migration Assistant tool

Now I can make sure that I can connect to my target Microsoft Azure SQL DB from my client machine via SSMS as well:

Now lets proceed with migration. For that you will need to download and install Microsoft Data Migration Assistant from – https://www.microsoft.com/en-us/download/details.aspx?id=53595 . Once you installed the tool lets create new project. I will start with assesment project to make sure I dont have any critical incompatibilities that preclude my migration to PaaS platform.

Tool will check for compatibility issues in my database as well as feature parity to see what needs to be done to migrate any SQL Server instance features to PaaS if anything at all.

We can then provide credentials to our AWS EC2 based instance and pick database after connecting to the source:

Now that we added source lets start assessment. DMA can have any version of SQL Server as assesment source up to 2016. As assesment done you will get a nice report where you can see all of the potential issues before you migrate. It should something look like this :

After that tool will do quick assesment and present you with schema objects you can migrate , highlighting\marking any objects that may have issues

After you pick objects you wish to migrate , tool will generate SQL DML script for these objects. You can then save that script for analysis and changes , as well as apply that script via SSMS on your own. Or you can go ahead and apply the script via tool as I done for this example by pressing Deploy Schema button.

After schema deployment finish we can proceed with data migration by pressing Migrate Data button.

Next, pick your tables to migrate and start data migration. Migration of data for smaller database like AdventureWorks takes few minutes. Everything transferred other than 2 temporal tables , but those are very different from regular database table. SQL Server 2016 introduced support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. There are other ways to migrate temporal tables to SQL Azure that I will post in my next blog posts.

Well, we are pretty much done. Next, check the data has migrated to target by doing few queries in SSMS:

In my previous post I attempted to explain my new interest in Docker and running data-centric applications in container. Starting here I will show how to install SQL Server for Linux on Azure IaaS in container.

Lets start by creating Linux VM in Azure that will run Docker for us. I dont want to spend all of the time and space going through steps in portal or PowerShell since I already went through these in this post, Good video titorial can also be found here .

Assuming you successfully created Linux VM and can login like this:

Next thing we will install Docker on that VM. We will get latest and greatest version of Docker from Docker repository.

First, add the GPG key for the official Docker repository to the system:

Now we are running Docker container with SQL Server 2017 in Azure. We should be able to list our containers like:

$ sudo docker ps -a

And see output like:

Connect to SQL Server in the container.

The following steps use the SQL Server command-line tool, sqlcmd, inside the container to connect to SQL Server. First lets connect to bash inside the container using docker exec command. Note I am providing container id here as parameter fetched from previous output of ps command

Here is a quick note that can be helpful to folks. Recently I had to migrate database to Azure via taking a backup from on-premises SQL Server and restoring this backup on SQL Server on Azure VM. Now, there is a better , more structured method available for the same using Database Migration Assistant (DMA) from Microsoft – https://www.microsoft.com/en-us/download/details.aspx?id=53595 , however I went old fashioned way purely through backups.

But how do I move 800 GB backup quickly to the cloud? What about network issues and need to restart on network hiccup? All of these issues and more can be solved via AzCopy tool.

AzCopy is a command-line utility designed for copying data to and from Microsoft Azure Blob, File, and Table storage using simple commands with optimal performance. You can copy data from one object to another within your storage account, or between storage accounts.

There are two versions of AzCopy that you can download. AzCopy on Windows is built with .NET Framework, and offers Windows style command-line options. AzCopy on Linux is built with .NET Core Framework which targets Linux platforms offering POSIX style command-line options. I will showcase Windows version here for now.

So what I did first is created a File Share on Azure Blob Storage. Azure File storage is a service that offers file shares in the cloud using the standard Server Message Block (SMB) Protocol. Both SMB 2.1 and SMB 3.0 are supported. You can follow this tutorial to do so – https://www.petri.com/configure-a-file-share-using-azure-files . Next, I needed to upload my backup to this file share via AzCopy:

The basic syntax to use AzCopy from command line as follows

AzCopy /Source: /Dest: [Options]

I can upload a single file, called test.bak , from C:\Temp to the demo container in the storage account (blob service) using the following command:

Microsoft just released the SQL Server Diagnostics (Preview) extension within SQL Server Management Studio and Developer APIs to empower SQL Server customers to achieve more through a variety of offerings to self-resolve SQL Server issues.

So what can it do:

Analyze SQL Server dump.

Customers should be able to debug and self-resolve memory dump issues from their SQL Server instances and receive recommended Knowledge Base (KB) article(s) from Microsoft, which may be applicable for the fix.

Review recommendations to keep SQL Server instances up to date.

Customers will be able to keep their SQL Server instances up-to-date by easily reviewing the recommendations for their SQL Server instances. Customers can filter by product version or by feature area (e.g. Always On, Backup/Restore, Column Store, etc.) and view the latest Cumulative Updates (CU) and the underlying hotfixes addressed in the CU.

Developers who want to discover and learn about Microsoft APIs can view developer portal and then use APIs in their custom applications. Developers can log and discuss issues and even submit their applications to the application gallery.

So I installed this extension and decided to give it a go with one of the SQL Server minidumps I have.

It actually correctly identified an issue and issues suggestions. So, while this may not work on every dump, it should worth trying before you start up WinDbg.

Over the holidays I had a chance to install and run SQL Server vNext on my Ubuntu Linux machine. I did run into some issues during install, but was able to work around all of those and have SQL Server engine successfully running on Ubuntu 16.10 x64 .

To start out make sure that you are installing SQL Server vNext on Ubuntu Linux version 16 or higher and 64 bit. My first issue I ran into attempting to install on Ubuntu 14.x on 32 bit.

Run Update thru apt-get. If you didn’t update you may run into an error later, as I did

sudo apt-get update

Run actual install via apt-get again

sudo apt-get install -y mssql-server

If you are on 32 bit or didnt update source you can see this error

Unable to locate package mssql

Now if you in latest Ubuntu x 64 with updated components tou should see install occuring in your terminal window. After the package installation finishes, run the configuration script and follow the prompts.

sudo /opt/mssql/bin/sqlservr-setup

Once the configuration is done, verify that the service is running

systemctl status mssql-server

Now that install is done in 5 easy steps and SQL Services are running on Ubuntu Linux you can use SSMS on Windows to connect to your SQL Server on Linux. But in order to connect from Linux to this instance on Linux I will need to install SQL Client Tools for connectivity stack.

The following steps install the command-line tools, Microsoft ODBC drivers, and their dependencies. The mssql-tools package contains:

With the introduction of SQL Server 2016 we now have a new way to encrypt columns called Always Encrypted. What makes it different from older encryption features in SQL Server like Column Encryption and Transparent Data Encryption is that it doesn’t just encrypt data at rest or while being transmitted on the wire, but only feature that ensures that the database never sees unencrypted values of sensitive columns, as data gets encrypted on the client tier via SQL Client\database driver. While the database only sees encrypted values, the application code works exclusively with unencrypted data. When a query is executed, the driver automatically looks up the master key in the Windows Certificate Store (or other OS-dependent location). The master key is then used to decrypt a column specific key, which in turn is used for encrypting and decrypting fields and parameters.

Microsoft advertises following use cases for Always Encrypted feature:

· Client and Data On-Premises

A customer has a client application and SQL Server both running on-premises, at their business location. The customer wants to hire an external vendor to administer SQL Server. In order to protect sensitive data stored in SQL Server, the customer uses Always Encrypted to ensure the separation of duties between database administrators and application administrators. The customer stores plaintext values of Always Encrypted keys in a trusted key store which the client application can access. SQL Server administrators have no access to the keys and, therefore, are unable to decrypt sensitive data stored in SQL Server.

· Client On-Premises with Data in Azure

A customer has an on-premises client application at their business location. The application operates on sensitive data stored in a database hosted in Azure (for example in SQL Server running in a virtual machine on Microsoft Azure). The customer uses Always Encrypted and stores Always Encrypted keys in a trusted key store hosted on-premises, to ensure Microsoft cloud administrators have no access to sensitive data.

· Client and Data in Azure

A customer has a client application, hosted in Microsoft Azure (e.g. in a worker role or a web role), which operates on sensitive data stored also stored in Microsoft Azure. The customer uses Always Encrypted to reduce security attack surface area (the data is always encrypted in the database and on the machine hosting the database).

Types of Always Encrypted encryption:

SQL Server offers two encryption modes: deterministic and random.

· Deterministic encryption ensures that a given value always has the same encrypted representation. This allows you to use the column for equality comparisons, joins, and grouping.

· For more security, you can use random encryption. This prevents guessing by ensuring that a given value’s encrypted representation is never the same twice.

You should use deterministic encryption for columns that will be used as search or grouping parameters, for example a government ID number. Use randomized encryption, for data such as confidential investigation comments, which are not grouped with other records, or used to join tables.

Limitations.

If a column is encrypted, then all range-like operations such as greater/less than, pattern matching using LIKE, etc. are disallowed. Furthermore, you can’t pass encrypted values to functions, user-defined or otherwise, because the database doesn’t have access to the unencrypted values.

Equality comparisons can only be performed on columns that use deterministic encryption.

Indexes can only be applied to deterministically encrypted columns.

If joining between two columns, both columns need to use the same column encryption key.

Constants expressions that refer to encrypted columns are not allowed. For example, you cannot write WHERE SSN = ‘111-11-1111’, but you can write WHERE SSN = @SSN. This is necessary because the driver works with the SqlParameter class to handle encryption requirements.

· Open you SQL Server Management Studio and connect to your SQL Server 2016 instance or SQL Azure. As far as on premise SQL Server you can use AlwaysEncrypted feature in Enterprise or Developer Editions

· First thing we will create is Column Master Key. You can do so either in script or via nice wizard in SSMS. Below is my script to do so , but of course based on your certificate , machine, etc. your script will be different

This will actually error out, but why? Since encryption and decryption is occurring on driver level this will be way you populate these encrypted columns from client side code. You don’t have to make any changes to your data access layer of your application except addition of following to your connection string:]

Column Encryption Setting=Enabled

Together with rolling out your Column Master Key to the client it should make it easy for your application to work with AlwaysEncrypte

While troubleshooting JDBC client apps that connect to SQL SErver I ran into this issue few times, latest very recently.

As you may already know well, SQL Server differentiates its data types that support Unicode from the ones that just support ASCII. For example, the character data types that support Unicode are nchar, nvarchar, longnvarchar where as their ASCII counter parts are char, varchar and longvarchar respectively. By default, all Microsoft’s JDBC drivers send the strings in Unicode format to the SQL Server, irrespective of whether the datatype of the corresponding column defined in the SQL Server supports Unicode or not.

In the case where the data types of the columns support Unicode, everything is smooth. But, in cases where the data types of the columns do not support Unicode, serious performance issues arise especially during data fetches. SQL Server tries to convert non-unicode datatypes implicitly in the table to unicode datatypes before doing the comparison. Moreover, if an index exists on the non-unicode column, it will be ignored. This would ultimately lead to a whole table scan during data fetch, thereby slowing down the search queries drastically.

This can be corrected by resetting one of the default parameter in the Java driver. The parameter name and value to be set might vary from driver to driver, depending on the vendor.