Azure Resource Locks are Your Friend in Development

One of the great advantages of the cloud computing is the ability to power off resources that are not in use to save some money. Sure, your production database servers should be running 24×7, but that VM, or SQL Data Warehouse you are developing against during the week? You can shut it down at 7 PM (1900 for the Europeans reading this) and not start it up. Azure even recently introduced an auto-shutdown feature for VMs.

Screen Shot 2017-03-24 at 8.55.37 AM

Unfortunately, there is no auto-startup feature, but that is easy enough to code using an Azure automation job.

This sounds great, can it walk my dog, too?

Unfortunately, there’s one problem with our awesome budget saving proposal. Sometimes developers have jobs that run beyond the time they leave the office. For example, last night at one of my clients a developer had an SSIS package running after he left, and it got killed when the SSIS machine auto-shutdown at 7. That isn’t good.

The solution for this is Azure resource locks—you can put a lock on any resource in Azure. A lock can do one of the two things—first there are delete locks which simply keep a resource from being deleted. It is not a bad idea to put a delete lock on all of your production resources to prevent any accidental deletion from happening. The second type of lock is a read-only lock, and these are a little more aggressive. You can’t do anything to a resource with a read-only lock—you can’t add a drive to a VM, you can’t resize, and most importantly, you can’t shutdown the resource.

You can use the portal, PowerShell, or CLI to create a lock. It’s a fairly simple construct that can be extremely beneficial. You can get current details for lock creation from the Azure Documentation.

My developers have access to the portal (thanks to role based access control and resource groups), so I’ve instructed them on how to place locks on resources, and how to remove them. As an administrator, you probably want to monitor for locks, to ensure that they aren’t left in place after they are needed.

You’re Speaking…and You Don’t Have Slides

I had this dream that other week. I was in the big room at PASS Summit, sitting in the audience. I was relaxed, as I thought I was presenting later in the day, when I quickly realized, due to the lack of speaker on the stage, that I was the next speaker, and the room was full. And I was playing with my laptop and I didn’t have a slide deck. In my dream, this talk was a 300 level session on troubleshooting SQL Server, something I feel like I could do pretty easily, you know with slides. Or a whiteboard.

criando-bons-slides-prof-jiani-cardoso-1-728

http://tse3.mm.bing.net/th?id=OIP.mek7WCJGE_T7QapryUckqQEsDh&pid=15.1

I woke up, before I started speaking. So, I’m not sure how I would have handled it—interpretive dance? I’m a pretty bad dancer. One thing, I will mention, and I saw my friend Allan Hirt (b|t) have to do this last month in Boston—really good (and really well rehearsed) speakers, can do a very good talk without their slides. Slides can be a crutch—one of the common refrains in Speaker Idol judging is don’t read your slides. It is bad form—do I sometimes read my slides? Yeah, everyone does occasionally. But when you want to deliver a solid technical message, the best way to do that is telling stories.

I’m doing a talk next month in Belgium (April 10, in Gent), right before SQL Bits. It’s going to be about what not to do in DR. My slide deck is mostly going to be pictures, and I’m going to tell stories—stories from throughout my career, and some stores from friends. It’s going to be fun, names will be changed to protect the guilty.

So my question and guidance for you dear readers, is to think about what you would do if the projector failed and you did not have a whiteboard. I can think of a number of talks I can do without a whiteboard–in India last year, another instructor and I demonstrated Azure networking by using our bodies as props. What would you do in this situation?

Monitoring Availability Groups—New Tools from Solarwinds

As I mentioned in my post a couple of weeks ago, monitoring the plan cache on a readable secondary replica can be a challenge. My customer was seeing dramatically different performance, depending on whether a node was primary or secondary. As amazing as the Query Store in SQL Server 2016 is, it does not allow you to view statistics from the readable secondary. So that leaves you writing xQuery to mine the plan cache DMVs for the query information you are trying to identify.

My friends at Solarwinds (Lawyers: see disclaimer at bottom of post) introduced version 11.0 of Database Performance Analyzer (DPA, a product you may remember as Ignite) which has full support for Availability Group monitoring. As you can see in the screenshot below, DPA gives a nice overview of the status of your AG, and also lets you dig into the performance on each node.

image

There are a host of other features in their new releases, which you can check out some of their new hybrid features in their flagship product Orion. Amongst these features, a couple jumped out at me—there is now support for Amazon RDS and Azure SQL Database in DPA, and there is some really cool correlation data that will let your compare performance across your infrastructure. So, when you the DBA is arguing with the SAN, network, and VM teams about where the root cause of the performance problem, this tool can quickly isolate the root cause of the issue. With less fighting. These are great products, give them a look.

Disclaimer: I was not paid for this post, but I do paid work for SolarWinds on a regular basis.

SQL Clone—Win Fabulous Prizes!!!

Want a chance to win a really cool prize (5 Amazon Dots, and a copy of SQL Clone), while learning about a cool product from Redgate? My friends at Redgate are in the process of releasing a really awesome tool call SQL Clone. So what is SQL Clone?

Screen Shot 2017-03-06 at 10.29.34 AM

One of the challenges I’ve faced through my career as a DBA is the ability to have a reliable copy of a production database to test against. Sure, when I worked at Comcast we had dev, qa, test, and prod environments, but many of the smaller organizations I worked for and consult with, don’t have that luxury. Even when I was at Comcast, the overwhelming cost of storage really limited our ability to maintain consistent development platforms.

So what is the solution? SQL Clone offers a virtual copies of your  database, in seconds, whilst only taking up 40 MB of disk space. How does it do it? Redgate stores pointers to your database in the copy. This means you can have one copy of your database and multiple clones, so that different parts of your dev team can work together in harmony. This data will grow a bit as each developer queries the data, but if you dealing with very large (> 5TB) or even just pretty big databases, this can greatly reduce your storage cost and increate developer productivity.

To learn more about Red Gate SQL Clone, check out this video:
  
 https://youtu.be/1zI2Oj9EZM8

You can see Grant Fritchey yell at an Amazon Alexa to create a clone of a database. It’s that easy.

So for the contest:

I’d like you to comment on what skill you would build using the echo dot. This can be anything, but database related ideas are likely better, as Redgate will be the judge. Here are some of my ideas:

  • Build a database using SQL Clone Smile
  • Rebuild the statistics on the data warehouse
  • Yell at Joey to go ride his bike
  •  

    For legal purposes you can read the terms and conditions here.

http://www.red-gate.com/products/dba/sql-clone/entrypage/competition-terms-and-conditions

SQL Bits Precon: SQL Server on Linux—A Brave New World

My first European trip this year includes two of my favorite bike races, the Tour of Flanders (or as my Belgian friends call it, the Ronde van Vlaanderen) and Paris-Roubaix. In the week between on Wednesday, I will be doing a full day of training at SQLBITS in Telford. I’ve had the good luck to be working with SQL Server on Linux since very early days of the development process, and am looking forward to sharing that knowledge with attendees.

 

insert-promptI

In this full day training session you will learn about the Linux operating system. Some of the topics we’ll talk about include:

  • Operating system architecture
  • Security Model
  • File manipulation
  • Common commands and using them together
  • Bash and Korn shells
  • Shell scripting
  • File systems and Volume Managers
  • Clustering in Linux
    You will also lean about technologies in SQL Server.
  • Monitoring O/S performance 
  • Proper SQL Server configuration
  • Automating deployment
  • Deploying High Availability and Disaster Recovery
    Whether you are new to Linux and UNIX and want to get started, or if you can awk and sed your way through a maze, this precon will have something for you.

DBCC Clonedatabase and Very Large Databases

One of the recent feature introductions to SQL Server is dbcc clonedatabase, a feature that lets you create a “data-less” clone of you database. All of the statistics and objects come into your cloned database, however none of the data does. This is perfect for development or performance tuning exercises, where you want all the metadata, but do not want the security risk of dealing with production data.

Recently I had the opportunity to use clonedatabase on a very large database. I was concerned about the size of the data files and how this would impact space on my volumes. Books Online is fairly clear, but I wanted to see for myself.

Note All files in the target database will inherit the size and growth settings from the model database. File name convention: The file names for the destination database will follow the source_file_name _underscore_random number convention. If the generated file name already exists in the destination folder, DBCC CLONEDATABASE will fail

So my thought in reading that, is that the same number of data files will be created in the clone, just with the settings in model. Let’s test that out.

The first thing I did was create a new database, and then add a few data files to it. I made them 20 MB, which is a different size than model—just for testing purposes.

image

Next, I ran the clone database command.

image

Then connect to the clone and look at the data files

image

I can see that all of the files were created, in the same location as the files on the source database, except with the size settings of model. While this shouldn’t be a big deal for most, if you do like I recommend and make model a reasonable size for your environment, and you happen to be tight on drive space, you could fill up a volume. So just be aware when using clonedatabase particularly with databases that have a lot of data files in them.

SQL Server on Linux Clustering—A Few Other Notes

So I was chatting with fellow MVP Allan Hirt (b|t) about the cluster build that I wrote about yesterday, and I had a few more realizations about the Linux HA process as it stands right now. I haven’t talked to the the Linux product team at Microsoft about this, but I hope to in the near future to get a better idea of where things are headed. So these are my notes as of now, strictly relating to failover cluster instances (FCI), AlwaysOn Availablity Groups are coming, but are not in the latest CTPs of SQL Server on Linux.

It was faster than building a Windows cluster

It took me a while, I laughed, I cried, I cursed a lot, but if I look at the time it took for me to actually build the cluster and install SQL Server, it was a much faster process. Much of this comes down to the efficiency of the SQL Server installation process on Linux, which is as simple as running yum install mssql-server (mostly). Which leads me to my next point..

Installation options would be nice

The cluster building process is a little kludgy. Basically, you install two standalone instances of SQL Server, and then remove the data files from one them, and copy them into your NFS share. Having the option to do the equivalent of an “Add Node” install, would mean you wouldn’t need to worry about cleaning up your second node.

There’s no cluster validation, explicitly

This is a bit scarier, or easier depending on your view point. There are tests at various parts of the process to make sure things are working. For example, the first step of building your Linux cluster is to authorize the nodes to take part in the cluster, which validates certain security and network settings. However, the storage validation consists of starting and stopping SQL Server on each node to make sure it can talk to the storage and startup. Given that Microsoft doesn’t own the clusterware for this solution, I’m not sure how much they can enhance that, or if they will. This is a good open question.

There’s no dns

(Happy Late Birthday Kris!) One interesting thing I realized after talking to Allan was that I did all of my networking setup through the /etc/hosts file on each individual node. I remember doing this for RAC, and I think it may be a requirement of Pacemaker, but you will still want to make a DNS entry for your cluster identifier. When you do this on Windows, if you are using Active Directory for DNS, the installation does this for you. Not in Linux, you will need to do this yourself.

Screen Shot 2017-01-04 at 11.40.26 AM

Get comfortable with command line and scripting

There’s no cluster wizard to get you through the process. I think this isn’t a huge deal—Denny and I were talking yesterday about how relatively easy it would be to script the whole process in bash (I’m holding off until I find out if Microsoft is doing this), and most Linux sysadmins are really comfortable with writing bash scripts. But if you aren’t comfortable with Linux and the command line, now is the time to brush up, before things go prod.

Summary

We are in the very early days of this process, there is much that will likely change. From a functional and conceptual perspective, this is very similar to the way a SQL Server Failover Cluster works in Windows, but the implementation is quite different. I’d like to see things resemble Windows a bit more, at least from a SQL Server perspective, but we’ll see where the product heads.

SQL Server on Linux–Clustering

First of word of warning on this post—if you are reading it and it isn’t January of 2017, I suspect things may have changed significantly in the months going forward.

Screen Shot 2017-01-03 at 3.47.08 PM

So I did It, I built a SQL cluster on Linux. The process is documented here on BOL, I’m not going to walk you through it, I’ll probably do that in a later post, I just wanted to mention some things I ran into during this build process. First, I did this using VMWare Fusion on my Mac, but I think any virtualization platform that allows virtual networks should work. Secondly, even though BOL says you need Red Hat Enterprise Linux (and you do if you are doing this in prod and require support), I was able to do all of this on CentOS, which is the free as in beer version of RHEL.

In my scenario, I built 3 VMs, one to serve as an NFS server, the other two to be my SQL Servers. Currently, there is no cluster version of the install, it’s the standard installation for standalone SQL on Linux, you then point SQL Server at the NFS mount you created which serves as your shared storage. I had an initial permissions problem on writing my data files there—I did a bad thing on the NFS server and opened up the directory to the world (777), and was then able to copy files there. I’ll follow up on that.

One other thing that wasn’t in BOL, that I had to troubleshoot my way through is that just like a cluster on Windows, you have a cluster identifier and floating IP address. I had to add that to /etc/hosts on each of my nodes to get it to resolve. The article mentions turning off fencing for non-prod environments—I had to do that in order to get failover working correctly in my environment.

Finally, failover was a bit wonky at first, and I had to spend too much time troubleshooting an odd problem. I wrote a connect item for it., but select @@servername and select name from sys.servers returns the name of the host, and not the cluster name. I’m sure the team will fix this in the near future.

Dear Colos: Up Your Game: aka How the #$%^ do you not have fibre in stock?

Many companies use co-located data centers to store their hardware. In some cases (like the colo we at DCAC use) you pay for power, cooling, and a connection to the internet. There is no expectation of added services other than those three things. In other cases, companies like Rackspace or Level 3 are what are known as managed service providers (note: I’m not talking about Rackspace or Level 3 in this post, I’m not going to name the guilty party, but if you want to know, you can reach me privately). Managed service providers offer solutions like shared storage, network management, and other value added services beyond just a space for your servers.

Enter the Cloud

So Microsoft and Amazon are effectively playing in this space with their IaaS offerings. There’s a big difference, however, as the cloud providers have invested a great deal of money in automation. The same customer I’m talking about in today’s post has some Azure VMs that we are deploying. I built and VM and allocated 3 TB of SSD storage in about 10 minutes this morning. Pretty slick operation–I’m fairly certain when I ran the PowerShell to deploy the VM, there was no person who got up to do anything. When I added the storage, I’m pretty sure no SAN zoning took place, and if it did, it was a few lines of code. We had previously stayed away from Azure because it’s not the most cost effective solution for very large workloads (Colo’s tend to have slightly better pricing on big boxes, but you get nickled and dimed on other things.

When Your Colo Sucks

So I have two different work streams going with the colo right now. One of which is to configure a site-to-site VPN to Azure. This should be a simple operation, however it took over a week to get in place, and only after I sent the colo the Cisco instructions on how to configure the VPN were they able to tell me that the Cisco device they had didn’t support the latest route-based VPN in Azure.  So we finally get up and running, and then we discover that we can’t get the Azure VMs from certain on-prem subnets. We ask them to make a change to add those subnets and they completely break our connection. Awesome.

The other workstream is a cluster upgrade. I wanted a new cluster node and storage, so we didn’t have to do an in-place upgrade. We started this process like 3 weeks ago, hoping to do the migration on black Friday. We had a call today to review the configuration. Turns out they had nothing in place, and aren’t even sure they can get a server deployed by NEXT FRIDAY (YES–10 days to deploy a server, your job is deploy servers). I heard lots of excuses like, we aren’t working Thurs/Fri, and we have to connect to two different SANs, we might not have that fibre in stock. It wasn’t my place to yell WHAT THE EVERLOVING $%^^ on the call, so I started live tweeting. Because that’s ridiculous. Managing and deploying infrastructure was what I did for a living, and I wouldn’t have a job if it took 10 days to deploy a server, and that wasn’t my only job. That really is the colo’s only job. How the #$%^ do you not have fibre in stock? Seriously? My lab at Comcast had all the fibre I could possibly need.

Edited to add this:

This is after last month when they confused SAN snapshots with SAN clones (when it takes 4 hours to recover from a “snapshot” it’s a clone) and presented production cluster storage (that was in use) to a new node. Awesome!!!

Why the Cloud will Ultimately Win

Basically, when it comes to repetitive tasks like deploying OSs and setting up storage, software is way better than humans. Yeah, you need smart engineers and good design, but Azure and AWS are already 90% of the way there. Also, there service levels and response times are much better, because everything is standardized and makes troubleshooting and automating much easier.

 

 

 

SQL Server v.Next—Linux Preview and Ola Hallengren’s Jobs

If you watched Scott Guthrie’s keynote at Microsoft Connect() this morning, your mouth is probably still on the floor. There was lot of big news:

  • Nearly all enterprise edition features in SQL Server 2016 SP1 are in standard edition
  • There is going to be a v.Next of SQL Server and you can play with CTP1 of it today
  • SQL Server on Linux CTP1 can now be downloaded and installed

The biggest news of the day is the standard edition news—this is going to be huge for independent software vendors who build their applications on top of SQL Server. While this is amazing news, I wanted to talk about something a little more near and dear to my heart—SQL Server on Linux. You can learn how to install SQL on Linux here.

So SQL on Linux

I’ve had the good fortune of being involved in the private preview for a good while now. Here’s the requisite screenshot of @@version you’ve seen in so many demos.

screen-shot-2016-11-16-at-9-53-50-am

In this case I’m running a VM on my Mac running CentOS. I also have a VM running Ubuntu and SQL Server running in a Docker container. If you want to go full native, you can use Visual Studio code and run without Windows at all. Aaron Bertand has a great post on how to make this work.

Management of SQL Server on Linux

Aside from a couple of DMVs that show you Linux specific performance information, everything in SQL Server on Linux is the same. Some of the HA and DR functionality is not complete, and the SQL Agent is not done, however you can use cron (and if you’re familiar with Linux, you should learn about cron—I’ll have another post on that next week).

Ola Hallengren’s Jobs on Linux

Many DBAs use Ola Hallengren’s jobs to manage backups and maintenance on their servers. The first thing you’ll want to do is download Ola’s scripts to your machine. You can do this using the CURL command in Linux. In this scenario I’m redirecting the output of the CURL command to a file called ola.sql

curl https://ola.hallengren.com/scripts/MaintenanceSolution.sql> ola.sql

Because of the behavior of the SQL Agent (currently) you will need to set the CREATE_JOBS parameters in Ola’s scripts from Y to N. I used VI to do this—you can read a primer on VI here.

After that—you’ll want to install Ola’s scripts on your SQL Server instance. You have sqlcmd on your Linux install and here you will use the input file flag.

sqlcmd -S . -Usa -Pp@ssw0rd! -iola.sql

You can do this from Management Studio on your Windows machine, or you can just do this from the command line—the nice part about the command line is that automation should be easy and straightforward.

The next thing we want to do is put a backup command in a shell script. In this case I’m just going to grab the backup example from Ola’s site. Use your favorite text editor—mine is VI because I hate myself and create a file called userbackup.sh

sqlcmd -S localhost -U SA -P p@ssw0rd!! -d master -Q “execute [dbo].[databaseBackup] @Databases=’USER_DATABASES’,@BackupType=’FULL’, @verify=’Y’, @CleanupTime=48, @CheckSum=’Y’, @LogToTable = ‘Y'” -b

After you save this file, you’ll want to make it executable. I’m going to use the chmod command to do that.

chmod 770 userbackup.sh

Now this file can be executed. You can do this using the ./ syntax. The output will be returned to the screen, if you are automating the process you can redirect the output to file which you can check for errors.

Date and time: 2016-11-16 10:22:47

Command: BACKUP DATABASE [TestingQuerystore] TO DISK = N’C:\Data\helsinki\TestingQuerystore\FULL\helsinki_TestingQuerystore_FULL_20161116_102247.bak’ WITH CHECKSUM, NO_COMPRESSION

Processed 2032 pages for database ‘TestingQuerystore’, file ‘TestingQuerystore’ on file 1.

Processed 2 pages for database ‘TestingQuerystore’, file ‘TestingQuerystore_log’ on file 1.

BACKUP DATABASE successfully processed 2034 pages in 0.090 seconds (176.562 MB/sec).

Outcome: Succeeded

Duration: 00:00:00

Date and time: 2016-11-16 10:22:47

This is quick primer on getting started with Linux—in the coming months, you’ll be learn more about being a DBA on Linux.

 

 

 

 

%d bloggers like this: