Patrick Keisler

Jun 142016
 

The host for T-SQL Tuesday #79 is Michael J. Swart (b|t), and his topic of choice is to write about the new release of SQL Server 2016.

This past weekend I attended the SQL Server 2016 Launch Discovery Day in Raleigh, NC. I have attended several SQL Server launch events over the years, but this one was quite different. While it wasn’t a real launch event, it was marketed as a hands-on event to learn about the newest release of SQL Server. The hands-on part for everyone to breakup into teams of five to solve a pre-determined problem. Basically, this was hackathon for SQL Server, and something I’ve never done before.

We started early in the morning with a few presentations about SQL Server 2016. Kevin Feasel (b|t) spoke about the new features, and SQL Server MVP Rick Heiges (b|t) spoke about a real-world win using columnstore indexes in SQL Server 2016. Just before our lunch break the hack was revealed; the basics of which were simple.

First, you are given a set of data set; just five tables containing the following information.

  • Virtual Chapter membership (with member location)
  • SQL Saturday Registration Data and session/track
  • PASS Membership Data
  • Multi-year Summit Data including title, track, score etc
  • Multi-year Summit Registration data including geographic location of attendee

Then you must design a solution to answer these questions.

  • From how far away do attendees travel to SQL Saturday? Are there any geographic trends to the distances traveled?
  • Does the SQL Saturday session data provide any insight into what sessions are chosen for Summit? Are there any trends in session or topic content that can be established?
  • Are there are geographical insights that can discerned? Do the sessions presented at SQL Saturday help predict popularity or selection of sessions at Summit?
  • Does virtual chapter member data provide any insights into PASS membership growth?

The judges score each solution based on the following criteria.

  • Use of new features in SQL Server 2016
  • Usefulness of the dashboard in Power BI/visualizations for the community
  • Completeness of the solution
  • Innovativeness of solution
  • Bonus points for mobile friendliness

Your solution must be completed in 3 hours.

On paper this all sounds pretty easy, but in practice it was quite hard. I am no BI developer and the other members of my team did not have any expertise in that area either, but we still managed to create a solution and have fun doing so.

The first issue was had was how to combine our development work on the same database. This one was easy…just use Azure. In the span of about 30 minutes, I spun up a new Azure VM with SQL Server 2016 pre-installed, uploaded the database, setup logins, and opened the appropriate ports. I then gave my team members the URL and credentials so they each could connect from their laptops.

One of my team members, Dhruv, wanted to get SQL Server R Services installed to analyze the data set. Machine learning was his specialty, and since R Services is a new feature for SQL Server 2016, we thought this would be a good place to start. However, this proved to be mistake for our team. We spent way too much time trying to get it setup. This was mainly do to the the need to install the R components without an internet connection, or I should say a slow connection. I wish we could have implemented this, because Dhruv had some really good ideas. Even without R Services, he was able to create some nifty reports using Power BI.

One of my other team members, Mike, spent a lot of time trying to understand the data, and how to query it to produce the data for our reports. I’m not sure if this was by design, but let me just say the data set was horrible and every team complained about the time needed to clean it up to the point of being useful. Either way, it was just one of the many problems that we needed to overcome. Most of Mike’s code was used in the Power BI dashboard that Dhruv created, but he was also able to write some code that made some good points about membership growth potential; however, we did not have time to build a report for it.

Our team (Team Tiger) finished our solution and presented to the group, but it was clear from the other presentations that we had been over matched. The winning solution was from Team Cheetah who had some unique insights into the data and designed their reports to reflect that detail. Not to mention, their presentation went into a lot of detail about what they had learned.

I really liked the entire event, but I wish that we had more time to work on the solution. Three hours seems like a lot, but after thinking about the challenge for a few days, there were so many more ideas that I came up with. For starters, one of the areas to score points was the use of new SQL Server 2016 features. That basically translates into ANY new feature whether it helps you produce a better looking report or not. With that in mind, I could have done the following.

  • Enable the Query Store to capture performance metrics from our solution.
  • Enabled Row-Level Security so we could the reports show different data based on which user is running it.
  • Spin up additional VMs in different Azure datacenters so I could create a load-balanced Availability Group that would provide high availability and better response time for users closer to each datacenter.
  • Setup Stretch Database for a single table to store older data in Azure.

While none of these things would have improved our presentation using Power BI, they are tasks that could have been easily implemented by me given my skillset. And by implementing them it would have definitely scored us a lot more points for the use of new SQL Server 2016 features. This is the big lesion that I learned from the event…always play to your strengths. Don’t try to learn a new skill in a few hours, just use the ones you already have. It will be a much better use of your time, and will most likely produce a better end result.

As I said, this was my first hackathon of any kind, but now I can’t wait to attend another one; especially one that deals with SQL Server.

May 242016
 

Have you ever tried to restore over an existing database only to receive the following error message?

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing ‘AdventureWorks2012’ database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

We could easily avoid this error by using the WITH REPLACE option in the RESTORE DATABASE command, but we want to know why this error occurs and how SQL Server knows they are not from the same family. The answer is the database_guid.

The database_guid is a GUID that is unique to every database; even across servers. If you restore a backup to another server, the restored database will get a new database_guid. You can view this value for every database by looking in the catalog view sys.database_recovery_status.

SELECT DB_NAME(database_id), database_guid
FROM master.sys.database_recovery_status;

If the database_guid is different for every database regardless of which SQL Server it’s on, then how does the restore still know the backup and database are from two different families? The answer is the family_guid.

The family_guid is the original database_guid when the database was first created, and does not change for the life of that database. When you issue a RESTORE DATABASE command, SQL Server will compare the family_guid to the database_guid if you are attempting to restore over an existing database. When those numbers don’t match, you will receive that error. You can get the family_guid from the same catalog view.

SELECT DB_NAME(database_id), database_guid, family_guid
FROM master.sys.database_recovery_status

You can use the following code to pull the family_guid from the backup and then compare it to the database you are trying to overwrite.

-- Build a temp table to store the backup file header information.
IF OBJECT_ID('tempdb..#BackupHeader') IS NOT NULL
  DROP TABLE #BackupHeader;
GO

CREATE TABLE #BackupHeader (
   BackupName nvarchar(128)
  ,BackupDescription nvarchar(255)
  ,BackupType smallint
  ,ExpirationDate datetime
  ,Compressed bit
  ,Position smallint
  ,DeviceType tinyint
  ,UserName nvarchar(128)
  ,ServerName nvarchar(128)
  ,DatabaseName nvarchar(128)
  ,DatabaseVersion int
  ,DatabaseCreationDate datetime
  ,BackupSize numeric(20,0)
  ,FirstLSN numeric(25,0)
  ,LastLSN numeric(25,0)
  ,CheckpointLSN numeric(25,0)
  ,DatabaseBackupLSN numeric(25,0)
  ,BackupStartDate datetime
  ,BackupFinishDate datetime
  ,SortOrder smallint
  ,CodePage smallint
  ,UnicodeLocaleId int
  ,UnicodeComparisonStyle int
  ,CompatibilityLevel tinyint
  ,SoftwareVendorId int
  ,SoftwareVersionMajor int
  ,SoftwareVersionMinor int
  ,SoftwareVersionBuild int
  ,MachineName nvarchar(128)
  ,Flags int
  ,BindingID uniqueidentifier
  ,RecoveryForkID uniqueidentifier
  ,Collation nvarchar(128)
  ,FamilyGUID uniqueidentifier
  ,HasBulkLoggedData bit
  ,IsSnapshot bit
  ,IsReadOnly bit
  ,IsSingleUser bit
  ,HasBackupChecksums bit
  ,IsDamaged bit
  ,BeginsLogChain bit
  ,HasIncompleteMetaData bit
  ,IsForceOffline bit
  ,IsCopyOnly bit
  ,FirstRecoveryForkID uniqueidentifier
  ,ForkPointLSN numeric(25,0) NULL
  ,RecoveryModel nvarchar(60)
  ,DifferentialBaseLSN numeric(25,0) NULL
  ,DifferentialBaseGUID uniqueidentifier
  ,BackupTypeDescription nvarchar(60)
  ,BackupSetGUID uniqueidentifier NULL
  ,CompressedBackupSize bigint
  ,containment tinyint NOT NULL
);

--SQL Server 2014/2016 have three extra columns in the file header.
IF (@@MICROSOFTVERSION/POWER(2,24) > 11)
ALTER TABLE #BackupHeader
ADD
   KeyAlgorithm nvarchar(32)
  ,EncryptorThumbprint varbinary(20)
  ,EncryptorType nvarchar(32);
GO

-- Insert file header info into the temp table.
INSERT #BackupHeader
EXEC ('RESTORE HEADERONLY FROM DISK = ''C:\SQL_Backup\AdventureWorks2012.bak''');
GO

-- Compare the family_guid values.
SELECT DatabaseName,FamilyGUID FROM #BackupHeader;
GO

SELECT DB_NAME(database_id),family_guid FROM sys.database_recovery_status
WHERE database_id = DB_ID('AdventureWorks2012');
GO

As you can see they do not match, which is why we get the error.

BONUS INFO:
There is one case where the database_guid can be the same even for different databases. If you detach a database, make a copy of the MDF and LDF files, and then re-attach those files as a different database name, the database_guid values will be the same.

SELECT DB_NAME(database_id), database_guid, family_guid
FROM sys.database_recovery_status;

As you can see, the database_guid and family_guid are the same for all three copies of the AdventureWorks database.

Have you ever tried to restore over an existing database only to receive the following error message?

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing ‘AdventureWorks2012’ database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

We could easily avoid this error by using the WITH REPLACE option in the RESTORE DATABASE command, but we want to know why this error occurs and how SQL Server knows they are not from the same family. The answer is the database_guid.

The database_guid is a GUID that is unique to every database; even across servers. If you restore a backup to another server, the restored database will get a new database_guid. You can view this value for every database by looking in the catalog view sys.database_recovery_status.

SELECT DB_NAME(database_id), database_guid
FROM master.sys.database_recovery_status;

If the database_guid is different for every database regardless of which SQL Server it’s on, then how does the restore still know the backup and database are from two different families? The answer is the family_guid.

The family_guid is the original database_guid when the database was first created, and does not change for the life of that database. When you issue a RESTORE DATABASE command, SQL Server will compare the family_guid to the database_guid if you are attempting to restore over an existing database. When those numbers don’t match, you will receive that error. You can get the family_guid from the same catalog view.

SELECT DB_NAME(database_id), database_guid, family_guid
FROM master.sys.database_recovery_status;

You can use the following code to pull the family_guid from the backup and then compare it to the database you are trying to overwrite.

-- Build a temp table to store the backup file header information.
IF OBJECT_ID('tempdb..#BackupHeader') IS NOT NULL
  DROP TABLE #BackupHeader;
GO
CREATE TABLE #BackupHeader (
   BackupName nvarchar(128)
  ,BackupDescription nvarchar(255)
  ,BackupType smallint
  ,ExpirationDate datetime
  ,Compressed bit
  ,Position smallint
  ,DeviceType tinyint
  ,UserName nvarchar(128)
  ,ServerName nvarchar(128)
  ,DatabaseName nvarchar(128)
  ,DatabaseVersion int
  ,DatabaseCreationDate datetime
  ,BackupSize numeric(20,0)
  ,FirstLSN numeric(25,0)
  ,LastLSN numeric(25,0)
  ,CheckpointLSN numeric(25,0)
  ,DatabaseBackupLSN numeric(25,0)
  ,BackupStartDate datetime
  ,BackupFinishDate datetime
  ,SortOrder smallint
  ,CodePage smallint
  ,UnicodeLocaleId int
  ,UnicodeComparisonStyle int
  ,CompatibilityLevel tinyint
  ,SoftwareVendorId int
  ,SoftwareVersionMajor int
  ,SoftwareVersionMinor int
  ,SoftwareVersionBuild int
  ,MachineName nvarchar(128)
  ,Flags int
  ,BindingID uniqueidentifier
  ,RecoveryForkID uniqueidentifier
  ,Collation nvarchar(128)
  ,FamilyGUID uniqueidentifier
  ,HasBulkLoggedData bit
  ,IsSnapshot bit
  ,IsReadOnly bit
  ,IsSingleUser bit
  ,HasBackupChecksums bit
  ,IsDamaged bit
  ,BeginsLogChain bit
  ,HasIncompleteMetaData bit
  ,IsForceOffline bit
  ,IsCopyOnly bit
  ,FirstRecoveryForkID uniqueidentifier
  ,ForkPointLSN numeric(25,0) NULL
  ,RecoveryModel nvarchar(60)
  ,DifferentialBaseLSN numeric(25,0) NULL
  ,DifferentialBaseGUID uniqueidentifier
  ,BackupTypeDescription nvarchar(60)
  ,BackupSetGUID uniqueidentifier NULL
  ,CompressedBackupSize bigint
  ,containment tinyint NOT NULL
);

--SQL Server 2014/2016 have three extra columns in the file header.
IF (@@MICROSOFTVERSION/POWER(2,24) > 11)
ALTER TABLE #BackupHeader
ADD 
   KeyAlgorithm nvarchar(32)
  ,EncryptorThumbprint varbinary(20)
  ,EncryptorType nvarchar(32);
GO
 
-- Insert file header info into the temp table.
INSERT #BackupHeader
EXEC ('RESTORE HEADERONLY FROM DISK = ''C:\SQL_Backup\AdventureWorks2012.bak''');
GO
 
-- Compare the family_guid values.
SELECT DatabaseName,FamilyGUID FROM #BackupHeader;
GO
 
SELECT DB_NAME(database_id),family_guid FROM sys.database_recovery_status
WHERE database_id = DB_ID('AdventureWorks2012');
GO

As you can see they do not match, which is why we get the error.

BONUS INFO:
There is one case where the database_guid can be the same even for different databases. If you detach a database, make a copy of the MDF and LDF files, and then re-attach those files as a different database name, the database_guid values will be the same.

SELECT DB_NAME(database_id), database_guid, family_guid
FROM sys.database_recovery_status;

As you can see, the database_guid and family_guid are the same for all three copies of the AdventureWorks database.

Nov 172015
 

UPDATED – May 16, 2017 – Please review the updated code here.

In my previous post, Configuring the Azure SQL Database Firewall, I discussed how you can configure the firewall rules to manage access to the SQL Server. Today, I wanted to demonstrate how you can accomplish the same thing with your on-premise SQL Servers by creating a whitelist.

So let’s start off by defining “whitelist”. According to the American Heritage Dictionary, it is “a list of people or organizations that have been approved to receive special considerations”. In our example, that “special consideration” is access to the SQL Server only if your workstation IP address is on the whitelist.

So why would we care about this? Why not just manage access to SQL Server the normal way with logins and passwords. Here is one example. Let’s say you have a company policy that prohibits anyone from using a common login to connect to a SQL Server. But your application uses a single SQL login to make its connection to SQL Server, and EVERY developer in the company knows the password. Even though there is a written policy in place, what would prevent one of those developers form connecting to SQL Server to fix a bug, or worse, change data to circumvent the application logic.

A whitelist will define which logins are allowed to connect to SQL Server from a specific IP address. Using our scenario from above, we can walk through an example. First we need to create the whitelist table to enforce this policy: the application login (WebSiteLogin) should only be allowed to connect to SQL Server if it originates from the web server’s hostname (webserver1) and IP address: 192.168.100.55.

USE master;
GO
IF OBJECT_ID('dbo.WhiteList') IS NOT NULL
DROP TABLE dbo.WhiteList;
GO

CREATE TABLE dbo.WhiteList(
   Id INT IDENTITY(1,1) PRIMARY KEY
  ,LoginName VARCHAR(255)
  ,HostName VARCHAR(255)
  ,HostIpAddress VARCHAR(15)
);
GO

Next, we need to add an entry to allow access.

INSERT dbo.WhiteList(LoginName,HostName,HostIpAddress)
SELECT 'WebSiteLogin','webserver1','192.168.100.55';
GO

Just creating the whitelist does nothing. That’s why we need to use a logon trigger to enforce the whiltelist rules.

CREATE TRIGGER WhiteListTrigger
ON ALL SERVER FOR LOGON
AS
BEGIN
    DECLARE 
         @LoginName varchar(255) = ORIGINAL_LOGIN()
        ,@HostName varchar(255) = HOST_NAME()
        ,@HostIpAddress varchar(15);
SELECT @HostIpAddress = client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

IF(
  SELECT COUNT(*) FROM dbo.WhiteList
  WHERE((LoginName = @LoginName) OR (LoginName = '*'))
  AND
  ((HostName = @HostName) OR (HostName = '*'))
  AND((HostIpAddress = @HostIpAddress) OR (HostIpAddress = '*'))
  ) = 0
ROLLBACK;
END;
GO

The logon trigger simply compares the user’s login name, hostname, and IP address to what’s in the WhiteList table. If there is a match, then access is allowed. If not, then the connection is terminated via a rollback. To test this, we can attempt to login using WebSiteLogin from an IP address other than 192.168.100.55.

Immediately, we are greeted with a logon error message.

In addition to comparing the logins, the code also adds a wildcard option using the asterisk (*).

SELECT * FROM dbo.WhiteList;
GO

 

Line 1 has an asterisk for LoginName and HostIpAddress. This means that any login from any IP address is allowed to connect as long as the HostName is dbserver. Line 2 has an asterisk for HostName and HostIpAddress. This means the sa login is allowed to connect from any hostname or IP address. Line 3 is the example we used above. An asktrisk in all three columns would allow any login from any hostname or IP address to connect.

In the event you lock yourself (or everyone) out of the SQL Server, there is a quick way to restore access. You’ll need to connect to SQL Server using the Dedicated Admin Connection, either through Management Studio or the SQLCMD command line using a login with SysAdmin permission. Once connected, you can disable the logon trigger.

From the example above, you can see how quickly you can implement your own firewall for your on-premise SQL Servers using a whitelist and a logon trigger.

Additional resources about Logon Triggers:
https://msdn.microsoft.com/en-us/library/bb326598.aspx

Nov 032015
 

Azure SQL Database is a Platform as a Service (PaaS) that provides a relational database for use over the internet. That sounds super cool and easy to use. But wait, there’s one word I’d like to highlight in that first sentence: “internet”. Anyone with an internet connection could access your database. Now that’s no cool. So how does Microsoft keep your database safe? The answer is a multipronged approach of using encryption, authentication, authorization, and firewalls.

All connections to Azure SQL Database use SSL/TLS to protect your data while “in transit”, and you can use Transparent Data Encryption (TDE) to protect your data “at rest”. Authentication and authorization are no different from the on premise version of SQL Server. Authentication just means you must have a valid login to SQL Server, and authorization means you must have permissions on an object; for example, SELECT permission on a TABLE.

The other way Azure protects your data is by use of a firewall. It works like any other firewall; it blocks unauthorized traffic from passing through. By default, Azure blocks ALL traffic to your database. This may sound a bit crazy but it’s no different from the way the Windows firewall works; you must allow access through a port. In Azure SQL Database, it always listens on port 1433, so Azure uses the client’s IP address to authorize access. For example, if your workstation IP address is 192.168.1.5, then you would need to explicitly allow access to your database from that IP.

Azure SQL Database has several ways to configure the firewall. Access can be granted at the server-level or at the database-level. Each level can be managed through the Azure Portal, TSQL, PowerShell, or Rest API. Let’s take a closer look at the Azure Portal and TSQL options.

Configuring the firewall through the Azure Portal offers you two options; one will allow access from any Azure service, and the other will define server-level entries. The first is the “Allow access to Azure services” button. When this option is turned on, it allows any traffic from services within your Azure subscription to pass through. This is usually on by default when your SQL Server is first created.

AzureFirewall1

The second way to manage the firewall through the Azure Portal is by defining server-level entries. On the Firewall settings page, you just need to give the setting a name, and then list the starting and ending range for the IP address. If it’s just a single IP address, then you would use the same value for both. The picture below shows a single entry and a range of IP addresses that are allowed to connect.

AzureFirewall2

When defining the rules through the Azure Portal, you are just creating server-level rules under the hood. If we run a TSQL query against sys.firewall_rules on our SQL Server, we should see 3 entries.

AzureFirewall3

There are the two entries we created: IP Range and Single IP. But there is also a third. That’s the entry for having “Allow access to azure services” set to ON. If we turn that option off and then rerun the TSQL query, we can see that entry for 0.0.0.0 is removed.

AzureFirewall4

If we wanted to create a server-level firewall from TSQL, then we would use the system stored procedure sp_set_firewall_rule.

EXEC sp_set_firewall_rule
     @name = N'TSQL Server Level'
    ,@start_ip_address = '192.168.10.10'
    ,@end_ip_address = '192.168.10.10';
GO

AzureFirewall5

To delete a server-level firewall rule, you can use the Azure Portal or the system stored procedure sp_delete_firewall_rule.

EXEC sp_delete_firewall_rule
     @name = N'TSQL Server Level';
GO

To create the rule, it’s as easy as running a query using the system stored procedure, sp_set_database_firewall_rule, in the context of the user database.

EXEC sp_set_database_firewall_rule
     @name = N'TSQL Database Level'
    ,@start_ip_address = '192.168.100.55'
    ,@end_ip_address = '192.168.100.55'
GO

We can query the sys.database_firewall_rules DMV to verify we have successfully created the rule.

AzureFirewall6

Notice, I have added this rule to the DEMO database, which will give the IP address access to only that database. Now let’s try to connect to the database.

What’s this error? My IP address does not have access to the server? But I just added it as a database-level rule.

AzureFirewall7

This error is because I’m attempting to connect to the default database, master, instead of the database, Demo, that I have explicit access. If I change my connection properties, then I will be allowed to connect to the Demo database.

AzureFirewall8

After connecting, the first thing you will notice is there is only one database listed. Because I only have explicit firewall access to the DEMO database, I can’t even see DEMO2 on the list of databases.

AzureFirewall9

To delete a database-level firewall rule, you can use the system stored procedure sp_delete_database_firewall_rule.

EXEC sp_delete_database_firewall_rule
     @name = N'TSQL Database Level';
GO

In the event you lose all access to your database, either by entering the wrong IP address or removing all of them, there is an easy way to get it back through the Azure Portal. On the Firewall settings page, click the “Add client ip” button and click Save. This will add the IP address of your current client to the server-level rules list. Once you have server-level access, you can then connect to the server and correct the database-level rules.

AzureFirewall101

 

The one thing to remember from all of the firewall settings, is these settings only open ports. There is no setting to deny an IP address.

As you can see, Azure provides several options for configuring the firewall settings for a SQL Database. I also think this is great example of how the Azure Portal can be viewed as nothing more than a different version of Management Studio. It’s just a graphical interface for managing SQL Server databases.

Additional resources:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure

Oct 162015
 

MSOver the past few months, I have been working diligently to learn more about Azure. As a result of my studies, I have successfully passed the Implementing Microsoft Azure Infrastructure Solutions certification exam (70-533). By far, this is the hardest exam I have taken to date.

Over the past 15+ years I have worked very hard to learn as much as possible about SQL Server and the Windows operating systems that it runs on, but that knowledge only took me so far within Azure. I had to look at Azure as an entire suite of products that seamlessly work together, and to successfully pass the exam, I had to learn about each one of them. I found the content around websites to be the toughest to understand. I’m pretty comfortable with how websites are hosted, but I needed to know more on the internals of features such as application settings, diagnostic logs, and monitoring. And of course we can’t forget PowerShell. It wouldn’t be an accurate test without a few PowerShell syntax questions.

The skills we learn over the years tend to be forgotten if we don’t use them. That’s why I’d like to welcome you to the Azure edition of Everyday SQL. I thought the best way to keep my skills up-to-date would be to host a portion of my blog within Azure. Going forward, I plan to post more articles about using SQL Server and other features within Azure.

Before I end this article, there is one thing that does bug me from time to time. It’s the correct way to pronounce “Azure”. It’s a little hard to type out, so I’ll provide you will a link to the Cambridge Online Dictionary where you can play the US version of the pronunciation.