Category Archives: SQL

Speaking at SQL Saturday Pensacola!

I’m proud to announce that I will be speaking at SQL Saturday Pensacola on June 3rd 2017! Check out the amazing schedule!

If you don’t know what SQLSaturday is, it’s a whole day of free SQL Server training available to you at no cost!

If you haven’t been to a SQLSaturday, what are you waiting for! Sign up now!

My presentation is Designing High Availability Database Systems using AlwaysOn Availability Groups” 

Abstract:

Are you looking for a high availability solution for your business critical application? You’re heard about AlwaysOn Availability Groups and they seem like a good solution, but you don’t know where to start. It all starts with a solid design. In this session we introduce the core concepts needed to design a Availability Group based system. Covering topics such as recovery objectives, replica placement, failover requirements, synchronization models, quorum, backup and recovery and monitoring. This session is modeled after real world client engagements conducted by Centino Systems that have lead to many successful Availability Groups based systems supporting tier 1 business critical applications.

Learning Objectives: 

This session highlights the importance of doing thorough design work up front. Attendees will learn core concepts needed for successful Availability Group based systems. This includes, recovery objectives, replica placement, failover requirements, synchronization models, quorum, backup and recovery and monitoring. From this session attendees will have a firm footing on where to start when they start designing their AlwaysOn Availability Group based systems.

Why Did Your Availability Group Creation Fail?

Availability Groups are a fantastic way to provide high availability and disaster recovery for your databases, but it isn’t exactly the easiest thing in the world to pull off correctly. To do it right there’s a lot of planning and effort that goes into your Availability Group topology. The funny thing about AGs is as hard as they are to plan…they’re pretty easy to implement…but sometimes things can go wrong. In this post I’m going to show you how to look into things when creating your AGs fails.

When working at a customer site today I encountered and error that I haven’t seen before when creating an Availability Group. So I’m going to walk you through what happened and how I fixed it. So if your AGs fail at creation, you can follow this process to dig into why.

First, let’s try to create our Availability Group

But, that fails and we get this error…it tells me what happened and to go look in the SQL Server error log for more details.

OK, so let’s look in the SQL Server error Log and see what we find.

Clearly something is up, the AG tried to come online but couldn’t.

The error here say check out the Windows Server Failover Clustering log…so let’s go ahead and do that. But that’s not as straightforward as you think. WSFC does write to the event log, but the errors are pretty generic for this issue. Here’s what you’ll see in the System Event Log and the Cluster Events section in the Failover Cluster Manager

Wow, that’s informative, right? Luckily we still have more information to look into.

Let’s dig deeper with using the WSFC cluster logs

The cluster logs need to be queried, they’re not readily available as text for us. We can write them out to file with this PowerShell cmdlet Get-ClusterLogs. Let’s make a directory and dump the logs into there.

Now we have some data to look through!

When we look at the contents of the cluster logs files generates by Get-ClusterLogs, we’re totally on the other side of the spectrum when it comes to information verbosity. The logs so far have been pretty terse and haven’t really told us about what’s causing the failure…well dig through this log and you’ll likely find your reason and a lot more information. Good stuff to look at to get an understanding of the internals of WSFCs. Now for the the reason my Availability Group creation failed was permissions. Check out the log entries.

Well that’s pretty clear about what’s going on…the process creating the AG couldn’t connect to SQL Server to run the very important sp_server_diagnostics stored procedure. A quick internet search to find a fix yielded this article from Mike Fal (b | t) which points to this Microsoft article detailing the issue and fix.

For those that don’t want to click the links here’s the code to adjust the permissions and allow your Availability Group to create.

So to review…here’s how we found our issue. 

  1. Read the error the create script gives you
  2. Read the SQL Server error log
  3. Look at your System Event log
  4. Dump your Cluster Logs and review

Use this technique if you find yourself in a situation where your AG won’t come online or worse…fails over unexpectedly or won’t come back online. 

Using dbatools for automated restore and CHECKDB

OK, so if you haven’t heard of the dbatools.io project run by Chrissy LeMaire and company…you’ve likely been living under a rock. I strongly encourage you to check it out ASAP. What they’re doing will make your life as a DBA easier…immediately. Here’s an example…

One of the things I like to do as a DBA is backup my databases, restore them to another server and run CHECKDB on them. There are some cmdlets in the dbatools project, in particular the Snowball release, that really make this easy. In this post I’m going to outline a quick solution I had to throw together this week to help me achieve this goal. We’ve all likely written code to do this using any number of technologies and techniques…wait until you see how easy it is using the dbatools project.

Requirements

  1. Automation – Complete autopilot, no human interaction.
  2. Report job status – Accurate reporting in the event the job failed, the CHECKDB failed or the restore failed.

Solution

  1. Use dbaltools cmdlets for restore and CHECKDB operations
  2. Use SQL Agent Job automation, logging and alerting

So let’s walk through this implementation together.

Up first, here’s the PowerShell script used to restore and CHECKDB the database. Save this code into a file named restore_databses.ps1

Let’s what through what’s going on here. First the line with $ErrorActionPreference = “Stop” that’s crucial because it will tell our script to stop when it encounters and error. Yes, that’s what I want. The job stops and the error from the cmdlets will reach the SQL Agent job we have driving the process. Using this, the job will fail, and I’ll have a nice log telling me exactly what happened.

Next we have some variables set, including the backup path and the location of the data and log files on the destination system.

Now, here’s the Restore-DbaDatabase cmdlet from the dbatools project, this cmdlet will traverse the backup path defined in -Path parameter, find all the backups and build the restore sequence for you. Yes…really! If you don’t define a parameter defining a point in time it will build a restore sequence using the most recent backups available in the share. The next few parameters define the destination data and log directories and tell the restore to overwrite the database if the database exists on the destination server. That next parameter tells the job to ignore using log backups. This is sufficient in my implementation because I’m running full backups daily, I don’t need the point in time recovery. You might, so give it a try. CHECKDB can take a long time…the final parameter, tells Invoke-SqlCmd2 not to timeout while running its query.

Now, I need to run some T-SQL to clean up the databases, for example, I change the recovery model, then shrink the log. This is so I don’t have a bunch of production sized log files laying around on the destination system I do this after each restore, this way I can save a little space. And finally, I run CHECKDB against the database.

If you want to do this for more than one database, you could easily parameterize this code and drive the process with a loop. You’re creative…give it a try.

Now, I take all this and wrap it up in a SQL Agent job.

SQL Agent Job Step

 Figure 1: SQL Agent Job Step Definition

Using a SQL Agent job, we get automation, reporting and alerting. I’ll know average run times, if the job fails and have a log of why and it sends me an email with the job’s results.

The SQL Agent job type is set to Operating system (CmdExec), rather than PowerShell. We run the job this way because we want to use the latest version of PowerShell installed on our system. In this case its version 5.1. The SQL Agent PowerShell job step on SQL 2012 I believe uses version 4 and when I used it, it wasn’t able to load the dbatools modules.

We need to ensure we install the dbatools as administrator. This way the module is available to everyone on the system, including the SQL Agent user, not just the user installing the module. Simply run a PowerShell session as administrator and use Install-Module dbatools. If you need more assistance check out this for help.

From a testing standpoint I confirmed the following things…

  1. When a restore fails, it’s logged to the SQL Agent job’s log, I get an alert.
  2. When one of the Invoke-SqlCmd2 calls fails, it’s logged to the SQL Agent job’s log and I get an alert.
  3. When CHECKDB finds a corruption in a database, it’s logged to the SQL Agent job’s log, the SQL Server Error Log and I get an alert. For testing this I used Paul Randal’s corrupt databases which he has available here.

So in this post, we discussed a solution to common DBA problem, backup, restore and CHECKDB a set of databases. Using dbatools, you can do this with a very simple solution like I described here. I like simple. Simple is easier to maintain. Certainly there are some features I want to add to this. Specifically, I’d like to write some more verbose information into the SQL Agent job’s log or use the job step’s ability to log to a file. Using those logs I can easily review the exact runtimes of each restore and CHECKDB.

Give dbatools a try. You won’t be disappointed…really go there now!

Speaking at SQLSaturday Chicago – 600!

Speaking at SQLSaturday Chicago!

I’m proud to announce that I will be speaking at SQL Saturday Chicago on March 11th 2017! And wow, 600 SQLSaturdays! This one won’t let you down. Check out the amazing schedule!

If you don’t know what SQLSaturday is, it’s a whole day of free SQL Server training available to you at no cost!

If you haven’t been to a SQLSaturday, what are you waiting for! Sign up now!

My presentation is Networking Internals for the SQL Server Professional” 

NewImage

Here’s the abstract for the talk

Once data leaves your SQL Server do you know what happens or is the world of networking a black box to you? Would you like to know how data is packaged up and transmitted to other systems and what to do when things go wrong?  Are  you tired of being frustrated with the network team? In this session we introduce how data moves between systems on networks and TCP/IP internals. We’ll discuss real world scenarios showing you how your network’s performance impacts the performance of your SQL Server and even your recovery objectives.

Friend of Redgate – 2017

I’m excited to announce that I have been named a Friend of Redgate for 2017. The program targets influential people in their respective technical communities such as SQL, .NET and ALM and enables us to participate in the conversation around product and community development.

As a multi-year awardee in the program I get to see first hand the continuing dedication Redgate has to the SQL community and to making great software. I met a ton of really cool, very dedicated people along the way. Thanks for the recognition and I look forward to another great year!

Redgate makes outstanding products! While I focus mainly on the DBA side of things such as SQL Monitor, SQL Backup and SQL Prompt there are many more. I’ve used these tools for years and let’s just say they’re awesome.

Redgate isn’t just software, they’re committed to community and education. Here are some of the things they do to support technical communities:

  • Online resources – SimpleTalkSQL Server Central, and books and Free eBooks. These resources aren’t marketing fluff, it’s killer content written by real experts
  • Events – hosting events, exhibiting at events and supporting user groups across the world. One word can describe this, engaged
Thank you to Redgate for this opportunity! I look forward to participating in this program, sharing my thoughts and learning as much as I can from all involved.
FoRG 2017
If you need you’d like to talk about Redgate’s products and where they fit into your SQL Server system please feel free to contact me.
 
Follow me on Twitter: @nocentino

Monitoring SLAs with SQL Monitor Reporting

Proactive Reporting for SQL Server

If you’re a return reader of this blog you know I write often about monitoring and performance of Availability Groups. I’m a very big proponent of using monitoring techniques to ensure you’re meeting your service level agreements in terms of recovery time objective and recovery point objective. In my in person training sessions on “Performance Monitoring AlwaysOn Availability Groups”, I emphasize the need for knowing what your system’s baseline for healthy replication and knowing when your system deviates from that baseline. From a monitoring perspective, there are really two key concepts here I want to dig into…reactive monitoring and proactive monitoring.

Reactive Monitoring

Reactive monitoring is configuring a metric, setting thresholds for alerting and reacting when you get the alert. This type of monitoring is critical to the operations of your system. The alerts we configure should model the healthy state of our system…when our system deviates outside of that state, we certainly want to know about that so that we can act…well really react accordingly.

Proactive Monitoring

Proactive monitoring with an alert based monitoring tool is a little harder. What DBAs and architects do is periodically sit down and go through their existing monitoring systems and review the data over some time interval. And if we’re honest with ourselves we try to do this at regular intervals but don’t get to it very often because we’re busy. And when we do finally get in there to look it’s usually to do a post mortem on some sort of production issue…then very often we find that a critical performance attribute had been slowly creeping up over time until it reached a tipping point and caused a production issue. We do our analysis, make our system corrections and move on. Still not exactly proactive. Mostly because there is still a person in the process.

Reporting on System State

With our Reactive Monitoring model, we already define what a health system state is. And let’s take people out of the equation. In Redgate’s latest release of SQL Monitor they added a reporting module. In here you can define reports that will represent the state of your system and you can get a snapshot view of what’s critical to you about your SQL Server environment. So if you are running Availability Groups, like I mentioned above, you can configure your report to have those critical performance metrics already set up so you can quickly get to them via the SQL Monitor interface. But better yet, you can schedule the report to be delivered right to your inbox. Yes, another email. But the report is a simple PDF…you give it a glance, process the data and move on. This saves you from having to go into SQL Monitor’s web interface to view the state of your system. Find something odd, jump into the Web UI and start digging for the root cause.

Reporting gives us two key advantages

  1. Point in time snapshots of our system’s health – we’ll get that report dropped into our mailbox and on that report is the most critical performance metrics that define the health of our system.
  2. Ability to see slowly changing trends – reporting helps us focus on trends. It allows us to zoom out a bit and view our system over time. Less reacting, more “proacting”

OK and one more…for me, as a consultant, I can define reports for clients and have them emailed to me periodically to review. Let’s take a second to build a simple report together

Creating Your Own Reports

Now normally, I’d show you a bunch of screenshots on how to create a report in SQL Monitor…but rather than do that…go head and click on the menu below and start trying out the reporting interface yourself using Redgate’s publicly available SQL Monitor Demo Site! 

Screen Shot 2017 02 06 at 11 47 32 AM

A couple reports I think you should look at right away are

  1. Example Report – this is the landing page on the demo site and as you can see we get a snapshot of our servers’ performance.
  2. SCC – Custom Metrics – in SQL Monitor you can add your own custom metrics, these are things that you think are business critical…the report in the demo site shows you SQLServerCentral custom metrics of Emails Sent per Hour and Forum Posts per Hour.
  3. Storage – here you’ll find things like fastest filling disk, disk space used, and database sizes. 
  4. Create Your Own – Download the trial version and create your own report. What are the things that are important to you that you want to have right at your fingertips when you log in or have a report land in your inbox with that data?
In closing, know that your monitoring strategy should take into account both types of monitoring, reactive and proactive so you can ensure you’re meeting your service levels. Start pushing things into the proactive category as much as possible. Leverage SQL Monitor’s reporting to better understand the state of your system. 

Understanding Network Latency and Impact on Availability Group Replication

When designing Availability Group systems one of the first pieces of information I ask clients for is how much transaction log their databases generate. *Roughly*, this is going to account for how much data needs to move between their Availability Group Replicas. With that number we can start working towards the infrastructure requirements for their Availability Group system. I do this because I want to ensure the network has a sufficient amount of bandwidth to move the transaction log generated between all the replicas . Basically are the pipes big enough to handle the generated workload. But bandwidth is only part of the story, we also need to ensure latency is low. Why, well we’re going to explore that together in this post!

Network Latency Defined

First, let’s define network latency. It’s how long it takes for a piece of information to move from source to destination. In computer networks, latency is often measured in milliseconds, sometimes microseconds on really fast networks. The most common way we measure network latency is with ping. The measurement ping provides measures from the time the ICMP request is send until the time it was replied to. This is how long it takes to move a piece of information from source to destination. But, the size of the data sent by default is only 64 bytes…that’s not very much data. So really ping isn’t a good way to measure latency for data intensive applications. As you add more data to the transmission, your latency will increase due to fact that the payload being transmitted is larger and those bytes have to be placed on the wire and read from the wire on the other side. This all contributes to your network latency. So we really want to measure what our network latency is with our data transmission size. 

server0:~ demo$ ping 192.168.1.1

PING 192.168.1.1 (192.168.1.1): 56 data bytes

64 bytes from 192.168.1.1: icmp_seq=0 ttl=64 time=0.072 ms


Network Latency and Availability Groups

Now let’s talk about Availability Group replication and network latency. Availability Groups replicate data over your network using Database Mirroring Endpoints which are TCP sockets used to move data between the primary and it’s replicas. When designing Availability Groups, we often think about things in terms of bandwidth…how much data do I need to move between my replicas. But there’s another design factor your need to consider, network latency. Why? Hint, it’s not going to have anything to do with synchronous availability mode and HADR_SYNC_COMMIT waits. Let’s talk about some fundamentals of TCP for a second.

How TCP Moves Data

The unit of transfer in TCP is called a TCP Segment. TCP requires positive acknowledgement. This means each segment sent, must be acknowledged by the receiver by sending an acknowledgement back to the sender confirming receipt of the segment. From that acknowledgement, TCP tracks how long it takes for a receiver to acknowledge each segment. How long? That’s where network latency comes in…acknowledgements will take at least as long as your network’s latency.

Now if TCP waited for each and every TCP segment to be acknowledged before sending the next segment, our system would never fully realize the network link’s capacity. We’d only consume the bandwidth of one segment at a time, which isn’t very much. To overcome this, TCP has a concept called “Congestion Window” what this means is that TCP can have several unacknowledged segments in flight at a point in time and thus consume more of our network connection’s capacity. The number of unacknowledged segments in-flight depends on our network’s conditions, specifically latency and reliability. If our network latency is high…TCP will reduce the number of segments in the Congestion Window. Which means our effective bandwidth utilization will be low.  If the network latency is low, TCP will increase the number of unacknowledged segments in our Congestion Window and our effective bandwidth utilization will be high. If the link isn’t very reliable TCP will decrease the Congestion Window’s size in response to unacknowledged segments, i.e. “dropped packets” so if your network line is dropping packets for some reason…expect a reduction in throughput. The Congestion Window is also variable, as conditions change TCP will increase and decrease based on changing network conditions.

Determining your maximum throughput is pretty easy, check out this calculator here.  Enter your physical link bandwidth, your round trip latency time, and the size of your TCP Window and you’ll get the maximum throughput of a single TCP stream, in other words…how fast a single TCP connection and transmit data.

Here’s a practical example, if you have a 1Gb link to your DR site and it has 40ms of latency a single TCP stream will only use 13Mb/sec. Wow…huh? CIOs everywhere just cringed reading that.

Now this is just the maximum for a single TCP stream, we can of course potentially have multiple streams sending, In AGs each database mirroring endpoint uses a TCP stream between the primary and each secondary replica in a standard Availability Group (not Distributed)

How Does Network Latency Impact My Availability Group’s Availability?

  1. Effective bandwidth can decrease – As network latency increases, effective bandwidth decreases…this means our transaction log throughput is reduced.
  2. Availability Group Flow Control – AGs track the number of unacknowledged messages sent from primary to secondary and if this gets too high the primary will enter flow control mode and will slow down or stop sending messages to the secondary. What can cause the number of unacknowledged AG messages to increase…network latency. A secondary can initialize flow control too, if it’s experiencing resource contention, it will message the primary and say slow down. 

In both of these cases our ability to move transaction log between replicas is reduced due to network latency and this can impact availability.  If you want to know more about how AGs move data check out this post and this one. Also it’s important to note, these issues are present regardless of AG availability mode. Synchronous-commit or asynchronous-commt this will impact your replication latency. In fact this just doesn’t apply to AGs, it’s ANY single TCP stream.

Where do we go from here?

Well, if you’re designing or operating high availability systems using Availability Groups, make sure you understand your network infrastructure and its performance characteristics. If you have AG replicas in a remote data center and it’s across a high latency link…test your workload to ensure you’re getting the throughput you need for your transaction log volume. You may have to tune your TCP settings on your operating system to better handle high latency connections or even have to make network changes to support your workload. 

If you want to dive deeper into how TCP works check out my Pluralsight course – LFCE: Advanced Linux Networking. While it’s not a SQL Server course, the TCP concepts are the same.

Want more SQL goodness like this sent straight to your inbox? Sign up for our newsletter here!

Microsoft Most Valuable Professional – Data Platform

Today, I’m proud to announce that I have been named a Microsoft MVPData Platform.  This is an exceptional honor and I’m humbled to be included in this group of exceptional data professionals. I really look forward to working with everyone in the MVP community and continuing to contribute to our unmatched SQL Community!

MVP Logo Horizontal Secondary Blue286 CMYK 300ppi

What is an MVP?

Here’s the definition according to Microsoft

Microsoft Most Valuable Professionals, or MVPs, are technology experts who passionately share their knowledge with the community. They are always on the “bleeding edge” and have an unstoppable urge to get their hands on new, exciting technologies. They have very deep knowledge of Microsoft products and services, while also being able to bring together diverse platforms, products and solutions, to solve real world problems. MVPs are driven by their passion, community spirit and their quest for knowledge. Above all and in addition to their amazing technical abilities, MVPs are always willing to help others – that’s what sets them apart.

For 2017, I have been named a Data Platform MVP, which means my technical specialization is on data products like SQL Server. The group of people that have received this award is quite small…by my count 403 worldwide and 100 in the US. I’m honored to be in this group of extremely talented professionals.

Why I’m excited to be an MVP?

Honestly, the primary reason I’m excited to be an MVP is to give back (more), I’ve learned so much from other MVPs and receiving this award will help me build relationships with other MVPs and Microsoft employees to further help develop the Data Platform itself and the community that surrounds that platform.

At the start of 2016 I had set a goal of being an MVP in 5 years. I don’t know why I picked that number, but what I figured was…MVP would be validation of consistent, quality work for our community and being recognized for the work that I’ve contributed. Things like blogging, social medial, public speaking and more. You learn a ton by teaching!

People that have helped along the way

I’d like to thank some folks that have helped me along the way…

  • My wife and family – I certainly couldn’t have done this without their support.
  • Other MVPs – you folks give your time freely and people like me consume what you produce to enrich ourselves. Thank you!
  • Paul Randal – I was in Paul’s 2015 mentoring class, he helped me set the direction of my community involvement. Invaluable guidance!
  • Brent Ozar – without his career blog and I’d have to figure our a lot of stuff on my own. Thanks bud!
  • Steve Jones – him and SQLServerCentral.com have really help give my blog a larger audience. I’ll never forget that first time I got an email about being on the front page of his site :)
  • Microsoft – thanks to you for this recognition!

Speaking at SQLSaturday Nashville!

Speaking at SQLSaturday Nashville!

I’m proud to announce that I will be speaking at SQL Saturday Nashville on January 14th 2017! This will be my first speaking event this year and I look forward to seeing you there! 

If you don’t know what SQLSaturday is, it’s a whole day of free SQL Server training available to you at no cost!

If you haven’t been to a SQLSaturday, what are you waiting for! Sign up now!

My presentation is Performance Monitoring AlwaysOn Availability Groups (which is one of my favorite sessions)

This is an updated session including new Availability Group Monitoring Extended Events and SQL 2016!

NewImage

Here’s the abstract for the talk

Have you deployed Availability Groups in your data center? Are you monitoring your Availability Groups to ensure you can meet your recovery objectives? If you haven’t this is the session for you. We will discuss the importance of monitoring and trending Availability Group Replication, how AGs move data between replicas and the impact replication latency can have on the availability of your systems. We’ll also give you the tools and techniques to go back to the office and get started monitoring and trending right away! 

SQL Server on Linux – How I think they did it!

OK, so everyone wants to know how Microsoft did it…how they got SQL Server running on Linux. In this article, I’m going to try to figure out how.

Update: Since the publication of this post, Microsoft has published a blog post detailing the implementation here

There’s a couple of approaches they could take…a direct port or some abstraction layer…A direct port would have been hard, basically any OS interaction would have had to been looked at and that would have been time consuming and risk prone. Who comes along to save the day? Abstraction. The word you hear about a million times when you take Operating Systems classes in undergrad and grad computer science courses. :) 

Well things are finally starting to come to light on how it was done. I had a Twitter conversation this weekend with Slava Oks, who is a leader on the project team and several other very active people in the SQL Community Klaus AschenbrennerEwald Cress, and Lonny Niederstadt. This got my gears turning…to find out…how they did it!

What do we know so far?

So here’s what we know, there’s some level of abstraction going on using a layer called SQL Platform Abstraction Layer (SQLPAL) and also some directly ported code via SQLOSv2. From a design standpoint this it a pretty good compromise. Check out Figure 1, here you can see SQLPAL sits between the Database Engine and the underlying operating system. Whichever one it may be, Windows, Linux and oh yeah “other OS in Future” :)

SQLServer on Linux

Figure 1 – SQL Server on Linux – source @SQLRockstar

Background information

So to understand how we got here, it’s worth looking at the Drawbridge project from Microsoft Research. Drawbridge is basically application, or more specifically, process virtualization with a contained OS inside that process space. This is called a picoprocess. Since the process is abstracted away from the underlying operating system, the process will need some part of an OS inside its address space. This is called the Library OS. With that abstracted away…each process has a consistent view of it’s own operating environment. In figure 2, you can see the Library OS and it’s roots into ntoskrnl.dll, which is an NT user-mode kernel. This provides a consistent OS interface for the application. Essentially program code doesn’t need to change.

Now it’s up to the picoprocess as a whole to provide some abstraction back to the actual operating system and that’s where the Platform Abstraction Layer (PAL) comes in. All that’s left is to provide an application binary interface for the picoprocess and you have a completely self-contained process without the need to interact directly the host operating system. This is amazing stuff!

Drawbridge

Figure 2 – Drawbridge Architecture – Source MS Research

 

SQLPAL – SQL Server Platform Abstraction Layer

So, I wanted to see this in action. In the Windows world, hard core SQL people are familiar with attaching a debugger to a SQL process and loading debug symbols to get a view into what’s going on inside of SQL Server. Well in Linux, we can do the same, and it’s a LOT easier. On Linux, there’s a tool called strace, which will give you a view into your programs execution and any interactions it has with the OS. So I launched SQL Server and strace and here’s what I found.

So to launch strace and SQL Server, we add the SQL Server binary as a parameter to strace. Caution, do not do this as root as it may cause a permission issue with log files generated by the sqlservr process. Use sudo to change to the msssql user.

[mssql@rhel1 ~]$ strace /opt/mssql/bin/sqlservr


The first thing you’ll see is a call to execve, which is a LINUX system call to start a new process. A regular old Linux process. So that means that sqlservr is a program binary compiled for Linux.

execve(“/opt/mssql/bin/sqlservr”, [“/opt/mssql/bin/sqlservr”], [/* 24 vars */]) = 0


At this point we see it loading all the local natively compiled libraries required for the program. Here’s one example, open is a system call to open a file, subsequent reads will occur when needed. There are many more libraries loaded.

open(“/lib64/libstdc++.so.6”, O_RDONLY|O_CLOEXEC) = 3


Now, we see something interesting, a load of a library called libc++abi.so.1. This file is in the /opt/mssql/lib/ directory and is shipped in the SQL Server package. So my guess is that this is the application binary interface for SQL Server’s picoprocess.

open(“/opt/mssql/bin/../lib/libc++abi.so.1”, O_RDONLY|O_CLOEXEC) = 3


Now we see a transition into Drawbridge like functionality, with the system.sfp open. This looks like it’s responsible for setting up the OS like substrate for the application’s execution environment. 

open(“/opt/mssql/lib/system.sfp”, O_RDONLY) = 3


During the load of system.sfp, we see several libraries, registry and DLL loads that look like they’re responsible for setting up the kernel level abstraction.

pread(3, “Win8.dbmanifest\0”, 16, 4704) = 16


Reading in the registry? Man that’s never going away :)
 

pread(3, “windows.hiv\0”, 12, 4753)     = 12


Reading in the NtOsKrn.dll, the NT user-mode kernel

pread(3, “NtOsKrnl.dll\0”, 13, 5123)    = 13


Next SFP we see load is system.common.sfp. This looks to be a second stage boot process, perhaps Drawbridge’s library OS? 

open(“/opt/mssql/lib/system.common.sfp”, O_RDONLY) = 4


During this phase we see many other DLLs loading. Looks like we’re setting up an environment…here’s an example of something loaded at this time. Clearly higher level OS provided functionality.
 

pread(4, “kerberos.dll\0”, 13, 15055)   = 13

 
After a few more SFP files are opened for certificates and NetFX4, and then we end up at sqlservr.sfp. And inside here, it loads things familiar to deep dive SQL Server pros…first we see the program binary load sqlservr.exe, SqlDK.dll, sqllang.dll, SQLOS.dll, and sqlmin.dll. I omitted some output for readability.

open(“/opt/mssql/lib/sqlservr.sfp”, O_RDONLY) = 7

…omitted

pread(7, “sqlservr.exe\0”, 13, 13398)   = 13

…omitted

pread(7, “SqlDK.dll\0”, 10, 14079)      = 10

…omitted

pread(7, “sqllang.dll\0″, 12, 14382)    = 12

…omitted

pread(7, “SQLOS.dll\0”, 10, 14418)      = 10

…omitted

pread(7, “sqlmin.dll\0”, 11, 14511)     = 11


And finally, we end up with application output, something we’ve all seen…SQL Server starting up.

nanosleep({999999999, 0}, 2016-11-17 14:11:37.53 Server      Microsoft SQL Server vNext (CTP1) – 14.0.1.246 (X64) 

Nov  1 2016 23:24:39 

Copyright (c) Microsoft Corporation

on Linux (Red Hat Enterprise Linux)


Oh, and now it makes much more sense why SQL Server on Linux is using Windows like file pathing inside the application, right? Well, think it through, SQL Server is interacting with an operating system that it thinks is still Windows, via the platform abstraction layer.
 

2016-11-17 14:11:37.53 Server      Logging SQL Server messages in file ‘C:\var\opt\mssql\log\errorlog’.

SQLOSv2

So in that Twitter conversation I had with Slava and others, we learned it’s not straight PAL, but a SQL Server specific PAL. This allows the product team to provide another path to the underlying OS for performance sensitive code. Look back at figure 1, you’ll see two paths from SQL Sever into SQLPAL. One uses the Win32 APIs, likely provided by Drawbridge (or some variant), and the other is perhaps natively compiled code…really that’s just a guess on my part. 

Final thoughts

All, this is a pretty awesome time we’re getting into…Microsoft embracing Linux, SQL on Linux, PowerShell on Linux. I’ve said this many times…Windows, Linux…it’s just an OS. I would like to thank Slava for his insight and also the product team for a fantastic preview release. It’s amazing how seamless this really is.

In a sidebar conversation with Ewald, he made the point that as SQL Server professionals that our investment in the understanding of SQL Server’s internals will persist with this implementation. Which I think is a huge relief for those that have invested years into understanding it’s internals! 

Please leave some comments on what your thoughts are on how this works. If you want to contact me directly, you can reach me at aen@centinosystems.com or @nocentino

 

Disclaimer

Well, if you made it this far…awesome! I want you to know, I don’t have any inside knowledge of how this was developed. I basically sat down and traced the code with the techniques I showed here.  

References 

https://www.microsoft.com/en-us/research/project/drawbridge/

https://blogs.msdn.microsoft.com/wsl/2016/05/23/pico-process-overview/