Running maintenance on Azure SQL databases

This article was recently published on dev.getroadmap.com:

 

To keep your data healthy and topfit, we all know you need to run something called database maintenance on your databases. This prevents things like performance problems or unrecoverable data from happening, and that is sort of the core business of DBA’s. And when we look at how this can be performed on a regular basis, an on-premise instance is a bit easier to maintain than an Azure SQL database. That’s why I want to share our experience with you on that, hoping that it can save you some time when you start implementing this.

 
Platform setup
In order to get a better understanding of why certain choices were made, I want to share a very basic version of the setup of our environment.

Our platform consists of 2 mayor parts: a private cloud part (that we see as “on-premise”), and an Azure part. Those 2 parts combined make our platform, and this platform hosts all of our services, applications, databases, etc. The development approach we use is everything should be designed with cloud-first in mind, but only if it’s the right tool for the job, and with the exclusion of some data.

The databases we use in Azure are all what Microsoft calls “Azure SQL databases”. This means that there are no virtual machines of any kind are running on our Azure-part of the platform, also known as DBaas (Database as a Service).

When I draw this platform, and only focus on the data part, it will look something like this:

 
One of the advantages of this setup is that we can leverage the power of both parts of the platform. As you’ll see later on in this blog, there are some things that we (need to) run from the on-premise instances and some things fully on Azure.

 
Big shoutout to Ola
Before I’m going into detail, I want to give full kudos to Ola Hallengren (Website | @olahallengren). He has spend a lot of his time to build a SQL Server Maintenance Solution that is completely free for everyone to use. And he did such an excellent job a lot companies (also huge companies) use his solution to run maintenance tasks on their databases.

None of the scripts below are written by me, but only small changes are made in order to make things more clear when the solution is deployed to an environment. The original scripts can be downloaded via the download page on Ola’s website.

 
Backups & Integrity check
Taking backups of your database and making sure there is no corruption in the datafiles is an essential part of the maintenance solution written by Ola. But because Azure SQL databases have a build-in maintenance solution (for example backups: full backups weekly, differentials hourly, and transaction log every 5 minutes, source), we don’t need to worry about that ourselves.

 
Index maintenance & Update Statistics
Indexes and statistics are the core of your performance-based maintenance processes. These make sure your queries run fast, and should provide you with a stable and predictable performance. This is especially needed on an Azure database, since you can’t monitor it like you would with an on-premise database.

Because Azure SQL databases are run on shared hardware that you can’t monitor yourself, Microsoft provides us with a number of different performance counters that we can use to check the status/health of our databases. The most important counters are CPU usage, Data IO, Log IO and DTU usage (a combination of the previously mentioned counters). The DTU counter is the most abstract (to me at least), because it’s explained by Microsoft as:

 

The Database Transaction Unit (DTU) is the unit of measure in SQL Database that represents the relative power of databases based on a real-world measure: the database transaction. We took a set of operations that are typical for an online transaction processing (OLTP) request, and then measured how many transactions could be completed per second under fully loaded conditions.

For example, a Premium P11 database with 1750 DTUs provides 350x more DTU compute power than a Basic database with 5 DTUs.

 
And for me, who is was used to monitoring physical hardware, that is a bit of a different approach when digging into performance-related issues. And it’s not that index and statistics maintenance isn’t important when you work on a on-premise database, but it’s a slightly bigger challenge to monitor the direct effects of a index rebuild or statistics update.

But because every Azure SQL database is a contained database, you need to deploy the stored procedures from Ola’s solution to every single database. So to keep it clear for everyone which table and stored procedures belong to the maintenance solution, I’ve changed Ola’s scripts slightly to create all objects in a specific schema named “dba”. So first of all, let’s create the schema:

CREATE SCHEMA dba
GO

 
And then create the used to log all of the maintenance commands and their outcome:

CREATE TABLE dba.CommandLog
	(ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_CommandLog PRIMARY KEY CLUSTERED,
	 DatabaseName sysname NULL,
	 SchemaName sysname NULL,
	 ObjectName sysname NULL,
	 ObjectType CHAR(2) NULL,
	 IndexName sysname NULL,
	 IndexType TINYINT NULL,
	 StatisticsName sysname NULL,
	 PartitionNumber INT NULL,
	 ExtendedInfo XML NULL,
	 Command NVARCHAR(MAX) NOT NULL,
	 CommandType NVARCHAR(60) NOT NULL,
	 StartTime DATETIME NOT NULL,
	 EndTime DATETIME NULL,
	 ErrorNumber INT NULL,
	 ErrorMessage NVARCHAR(MAX) NULL)
GO

 
Now that these are created, you can create the stored procedure that execute the actual index and statistics maintenance. They are too long to post here as code snippet, but you can download the CommandExecute script here, and the IndexOptimze script here.

But because we want to schedule these procedures later on, I decided to create an additional stored procedure in every database, that is deployed alongside the maintenance objects:

CREATE PROCEDURE dba.ExecuteMaintenance
AS


EXECUTE dba.IndexOptimize
 @Databases = '<Insert database name>',
 @FragmentationLow = 'INDEX_REORGANIZE',
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @UpdateStatistics = 'ALL',
 @LogToTable = 'Y',
 @Execute = 'Y'

GO

 
This allows us to run maintenance with specific options on the different databases.

Scheduling
After this was all set up, I needed to come up with a way to run these procedures on a schedule. And as you might know, Azure SQL databases don’t have a SQL Server Agent so that’s were our on-premise platform comes in handy. Just for this I created a new virtual machine in our private cloud, and installed SQL Server on that machine to utilize the SQL Server Agent. This server (operations server) runs all of the scheduled operational jobs (including maintenance and some monitoring jobs) on our Azure environment.

But to run a proces from this operations machine on one of our Azure databases I needed to create a linked server first:

EXEC sp_addlinkedserver 
 @server=N'ServerName__DatabaseName', 
 @srvproduct=N'Azure SQL Db',
 @provider=N'SQLNCLI', 
 @datasrc=N'<SERVERNAME>,1433',
 @catalog='DatbaseName';
 GO

EXEC sp_addlinkedsrvlogin 
 @rmtsrvname = 'ServerName__DatabaseName', 
 @useself = 'FALSE', 
 @locallogin=NULL,
 @rmtuser = '<USERNAME>',
 @rmtpassword = '<PASSWORD>'
 GO

 
And all that’s left now is to create a SQL Server Agent job, that executes the “ExecuteMaintenance” stored procedure on the Azure database:

EXEC [<LinkedServerName>].<DatabaseName>.dba.ExecuteMaintenance

 
The reason I choose this approach is that I didn’t want to lose any time on figuring out how the Azure Scheduler or Automation works, at the moment I was implementing the maintenance solution.

But if you want to have a good resource on that, please check out Pieter Vanhove (Blog | @Pieter_Vanhove) blog, because he wrote a great blog post about that.

 
Conclusion
When I look at how this solution is set up, I’m the first one who admits that this isn’t a perfect or ideal solution. But in the end, this gives the rest of the team a clear solution when they need to start or restart the maintenance process: Just log in to the operations server, start the SQL Server Management Studio (SSMS), open the SQL Server Agent jobs, find the job associated with the database they want to run the maintenance on, and that’s it. But for future scalability and maintainability, we might need to implement another solution to do this.

T-SQL Tuesday #54 – An Interview Invitation

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “An Interview Invitation “. If you want to read the opening post, please click the image below to go to the party-starter: Boris Hristov (Blog | @BorisHristov).

 
This months topic is about job interviews. Looking back, I can say I had a few interview over the last few years. Not only to get myself at a company, but also to hire new employees for the companies I worked for. Both types of conversations can be very interesting, or get on your nerves very quickly. I’ve seen both…

 
Hiring people
When you work for a company, and you hire new people, you want to make sure you’re talking to the right type of people. Whether or not I’m hiring you, isn’t just bases on technical skills. The way I see it is that technical skills is mostly build from experience and insight, and knowledge can be build up by reading a book, watching a video, or talking to and learning from other people with a similar skill set. The thing I’m personally looking at in the interview is personality, passion for what a person does, and what he or she wants to achieve in the next few years. That will tell you something about the person. I’m not saying the technical skills aren’t that important, but it’s not the only thing I look for. One of the last companies I worked for looks for people they want to drink a beer with on a Friday night. So again, the personality is in some cases as important as technical skills.

 
Interviewing people
Talking to other database professionals can be either very interesting, or very frustrating. One of the interviews I had in the past few years, was to hire a new colleague for a project I was working on. His resume looked good, he had a few years experience on equal projects, and I thought he would make a good addition to the team. When I started the interview, he seemed to be a little nervous. I tried to make him feel a little more comfortable, by asking him about his experience, past projects, and personal life. When he calmed down, we slowly moved the conversation to his technical skills.

Talking about the project, I started to notice he wasn’t giving me the answer I would’ve expected from someone with his experience. Okay, maybe he misunderstood the project. We moved on to common issues we all ran into when working on projects. Every question was answered by “it depends…”, and “I’ve read that…”. Okay, that’s no good. Theoretical knowledge isn’t what I expected. And not long after that, I ended the interview. If you’re too scared to even make a theoretical decision, and defend that point of view, I don’t want you to work on my environment. That shows me that if you make a mistake, and that can happened to even the most experienced employee, you won’t have the guts to tell me you did something wrong. And I want you to tell me that, so we can solve it together, and learn from it.

Another interview I had with a database professional, turned out to be a dull conversation. The person came in to apply for a senior function, and he definitely had the experience for it. His personality was good, and would fit the team perfectly. But every answer we asked him, he answered with a lot of needless words, that ended up in a best-practice answer. The reason I didn’t want to hire him, was his lack of creativity. If you’re spilling answers you read on the internet, or in books, it shows me you’re probably not creative enough to solve the problems you’re encountering. If you’re in a stressful situation, and you need to read a book to think of a solution to the problem, you’re probably not the DBA I’m looking for.

 
Being interviewed
One of the most interesting interviews I had in the last few years, was one of my last job interviews. The first interview went well, where we talked about the company, the team, etc. The IT manager and team leader wanted to talk to me a second time, and that was the most interesting interview I had until now.

The interview started out nice. I talked to the team leader again, and another team member joined the interview. We had a good conversation about technical stuff, the team, projects, etc. When we were approaching the end of the interview, the most interesting part started. I knew the company worked with a consultant company from England, and that the lead consultant visited the office every few weeks. Big surprise, he was there that day. So the moment he walked in, I started to struggle. I needed to switch from my native language Dutch to English, and started an interview with an MCM and MVP. This made me even more nervous, and because of that I stared to doubt every answer I gave. But in the end, I did a reasonable job apparently, because the company hired me. Now I see him every month, and we have some good conversations, even though I’m still afraid to ask him a question sometimes, worrying I’m just asking a dumb question.

 
Maybe the most annoying person is the perfect candidate…
Thinking about hiring a new colleague, you might end up with a pretty big dilemma. The person you want to hire because of his personality, might not be the best choice. The one with the best technical skills might be a better fit, but you don’t like him at all. So when looking for a new colleague or team member, you might end up wondering what’s best for the company, and set your own feelings aside. But when you do, it’s always a gamble with new people. At first, the new colleague had perfect technical skills and was very nice, but in the end his skills aren’t that good or his personality is slightly different than you thought. Hiring people is still a combination of asking the right questions and a gut feeling.

T-SQL Tuesday #45 – Follow the Yellow Brick Road

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “Follow the Yellow Brick Road”. If you want to read the opening post, please click the image below to go to the party-starter: Mickey Stuewe (Blog | @SQLMickey).

 
When I read this months subject, I didn’t know what to write about, until I read one of the other blogs. So I’m sorry Mickey, but I’m using the subject this month as a guideline, and extend it a little bit. I’m glad I know Mickey, and I’m guessing she doesn’t mind me getting a little creative.

The writer of the post I read, talked about index fragmentation and data growth, and then I remembered a situation I encountered a while back.

When you come in to a company, you’d really like to see (and maybe even expect) to walk into a well organized team. A team that has procedures that work for them, and team members that agreed to solve problems in a specific way. But sometimes, it’s not…

 
Here you go!
On my first day as DBA at the company, I walked in and after a few conversations booted up my computer. Once it was booted, they gave me the name of a server, and wished me good luck. No documentation, no explanation of the databases, and no mention of who worked on the server. The only thing they said when they “handed over the key”, is “Here you go! From now on it’s your responsibility to keep the environment running and healthy!”.

So, there I was… No documentation, no health check, no one to tell me what the status of the server was…

 
Taking that first step is always the hardest
So the first thing I did was check out which databases were on the server. There were a lot: 70 in total. Then I checked the user permissions: most users were a member of the db_owner role. Great! Asking which users really needed all that permissions was useless. Everyone needed full access to everything.

So then I took the step to tell them that I was in charge of the server from that moment on, and that if they did something without my permission, I’d remove their access to the environment. Looking back, that was easier said than done.

But the first step that was really hard to accomplish, was reporting of the server health. No one thought that we needed that, and if there was something went wrong we would notice in an instant. And because I knew we really needed that, I just cleared my schedule, and created a few reports.

 
Being right is always a fun thing
A few weeks later, they finally saw I was right. One of the databases filled up a disk, and now we had issues. The only way we could see what went wrong, was looking at the data growth over the previous weeks in my reports. So now I could show them how wrong they were. It’s nice to be right once in a while!

 
Audit
This is were this months topic comes into play. A clear indication that you lack an audit trail, is when there’s an issue with one of your processes, databases, or even as small as a specific dataset, and you don’t know where the issue came from. Following your data flows from beginning to end is always a good idea. And if you work at a larger company, you’re (at least in the Netherlands) required to document your data flows for a financial audit every year.

But not only your datasets need audit trails. Your databases are in dying need of an audit trail as well. Because what happens if your database increases 50% in size over the course of a week, and you don’t track those kind of numbers? Your disk is filling up, your database stops working (if you haven’t got the space to extend your datafiles), and eventually your world is becoming a dark and evil place if you can’t fix it.

 
Conclusion
Even if you don’t have a full front-to-back audit trail of your data and processes, at least try to monitor and audit the key point in your data flow. That helps you debug excessive data growth, and helps you when (for example) a user creates a new database without asking your permissions. Even small audits help you out when you’re in a world of pain and trouble.

T-SQL Tuesday #44 – The second chance

T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.

This month the subject is “The second chance”. If you want to read the opening post, please click the image below to go to the party-starter: Bradley Ball (Blog | @SQLBalls).

 
This months topic isn’t easy for me. Even while I’m writing this, I’m still thinking about what that means to me personally. Second chances sound to good to be true. Doing something for a second time, and trying a different approach then the first time, in order to succeed…

Normally I try not to be a person that looks back at previous mistakes, but sometimes that’s easier said than done. But I must say, there are not a lot of things I regret in my life. Only one that’s really close to my heart, and nobody probably knows about. But I won’t bother you with that story…

 
Deserving a second chance
People always say: everybody deserves a second chance. But I don’t completely agree with that. Everyone makes mistakes, and that’s not something to be ashamed of. But if you make the same mistake twice, you need to start wondering if there’s something you could have done to prevent it. But even then, you deserve a second chance.

The people that actually know me, know I’m a nice guy, I’m honest (sometimes even a little bit too honest), and normally you can’t get me angry. But if you screw the same things up over and over again, I’m not that friendly anymore. Let me explain that with an example.

 
No, that wasn’t me!
A while ago I worked with someone who thought he was really good at his job. Personally, I had some issues with that opinion, but I gave him the benefit of the doubt. On a number of occasions he screwed things up, and I thought he should have known that what he was doing was never ever going to work. But still, I was willing to give him that second chance. But then he got me angry. And you won’t like me when I’m angry.

There were a number of SQL Server Agent Jobs running, and they locked up some of our tables and databases. When I asked him to look at that, he said he didn’t run those jobs, and focused on his screen again. So I asked him again, nicely, to look at it. He replied with the same answer.

A little bit angry, I told him the jobs were started on the server, and that he was the only one that was logged on to the server. Then he “suddenly” remembered he started the jobs, and said the locking wasn’t that bad. As a DBA, I took a deep breath, and counted to 10, and waited for him to fix the issue. But if you’re that stubborn, you’re clearly lying to me, and don’t even have the courage to tell me you screwed up, you don’t deserve a second chance in my opinion. At least be honest with yourself and to your colleagues!

 
Honesty get’s you a second chance
At this and previous companies I worked for, I always tried to teach the student and interns they need to be honest and listen to people with experience. Even if things go wrong, and you’re the one to blame, at least tell the truth. For me, that’s the difference between fixing the issue together and moving on, or letting him take the fall all on his own. But this is also an experience I got handed down to me by my colleagues a few years back. This is what happened to me, as I remember it:

When I started my first job in IT, I was offered a job as SQL Server Consultant. That meant that I was responsible for data conversions from different systems to our core system. When I took the job, I had never written a query before. But by listening to colleagues and my mentor (a good friend of mine who worked for the same company), I made it into a development team about 1.5 years after I started my first job.

That meant I was able to access the production system (yes, that’s where the problems began!). These permission were given to me, so I could solve data related issues in production. Until the day they asked me to update 5 rows in production. I checked and double checked the T-SQL statement I wrote, asked a colleague to take a look at it, and then took a break away from my computer so I could totally focus on this task when I got back.

I sat down again, looked at the query one last time, and pressed F5… One minute passed… Two minutes passed… And then the query finished… 50.000 rows affected… I slightly panicked, and noticed I only selected the update, a half “WHERE” clause, and no “BEGIN TRAN”… My heart started racing, and I picked up the phone and asked the system administrator (a good friend of mine, who worked at a different location) if he could restore the last backup for me, because I screwed up. After some questions, and some explanations about my mistake, the last thing he said, before he hung up the phone in anger, was “Which backup? The one that didn’t ran for the last few weeks?”.

I didn’t know what to do. How could I ever fix this? Almost every record in the table was updated, and there was no way of knowing what he old values of the updated records were. So it took all my courage to pick up the phone, and ring the system administrator again. All I heard on the other side of the phone was his evil laughter. Before I could ask him what was going on, he told me: “I’m glad you were honest to me. But don’t worry, I’m restoring the backup that was taken an hour ago. There’s no data lost”.

At that moment, I didn’t know what to think or feel. At first I wanted to slap him silly, but a few minutes later I wanted to thank him for his wonderful help. He probably saved my ass, and he never told anyone except my mentor (who also was my direct manager back then, and also a good friend of us both). A few days later, the three of us talked about it face to face, and eventually all laughed about the situation.

 
A wise lesson
But if I learned anything from that situation, besides never running an update without transaction or “WHERE” clause, is to be honest. Even though you might think the company will fire you for the mistake you made, it’s always better to tell them then letting them find out themselves. And that’s what I try to tell the students, interns, and junior colleagues I work with. Be honest, and then you earn a second chance…

What do you want to be when you grow up?

Last week I told you that I’m about to change jobs. Because the contract wasn’t signed yet, I could tell you what my next challenge was going to be. But now I’m glad I can tell you where I’m going to end up next: I’m going to become a DBA!

I’m so glad I can finally tell you the good news! I’m so excited to start my next challenge! From next week, I’ll be a Microsoft SQL Server DBA. And if all things work out in the first few months, the planning is that my job will also include Oracle, MySQL, and MongoDB later on.

But for now, my challenge is to become a DBA with expert knowledge of Microsoft SQL Server, and all the features that ship with it. As far as I know now, my planning for the upcoming year is to build a test environment for the SQL Server environment they have now, and a migration of the system to SQL Server 2012. That sounds like a hard work for my first year, but I’m really excited!

Until now, I only worked as a SQL Server developer with a few DBA tasks. The last years I started thinking about my future, and what I really wanted. The only thing I wanted to try out but never had the chance to, was becoming a DBA. And now I get a chance to prove myself as a fulltime DBA. This might end up getting my head blown off the first few months, but I’m excited to test my knowledge and skills, and to expand them.

Thinking about next week makes me both nervous and happy. Feeling happy because I can prove myself (and others) that I can actually do the things people told me I would never accomplish, and nervous because I’m stepping out of my comfort zone. But I’m totally convinced I get all the help that I need to succeed from my new colleagues, and my SQL Family.

Last year I experienced the commitment and dedication to help one another in the SQL Server community. That’s why they call it the SQL Family. Because it’s a hardworking community, that’s always willing to help you if you need them. They helped me out on several occasions, and I try to help them as much as I can. And with them on my side, the upcoming period is going to be a success! 🙂

Without pain, without sacrifice, we would have nothing…

The last few days flew by for me. After 2.5 years of working for my previous employer, I decided it was time for a new challenge. In the past period I’ve learned a lot, and had the opportunity to learn and research a lot of new things and techniques.

In 2010 I started working for the company, and began as a Data Warehouse (DWH) developer. Before my first day on the job, I had pretty much no experience with a DWH. That was quite a challenge, especially because there was no documentation at all. So I needed to find out everything myself, because there wasn’t a real DWH developer before I started there.

This also gave me a chance to dive into Reporting solutions. The company ran a Reporting Services machine with tons of Management-, Marketing-, Sales- and Development-reports. These reports varied from showing turnover information, to showing the IT department the (partial) morning check.

Eventually I ended up building reports, cubes, and developing and maintaining the DWH. This was my daily job for almost a year, next to helping out my team, consisting of about 10 to 15 .NET developers.

After about a year, management decided to let an external company develop our DWH. That gave me more time to focus on building reports, and doing more development work for our test- and production environments. That year I started with spatial data in SQL Server. This was one of the coolest things I got go do in the 2.5 years I spent there.

The hardest thing to give up wasn’t the company or the job, but it was the team. It was a pretty young team of developers, and most of the guys (and the one gal we had) were pretty awesome. There was always someone who could help you out, and the team members had no problems with working late to help a colleague out. The team was amazing!

But like I said, after more then 2 years, it was time for something new. While reading a blog post by SQLRockstar (Blog | @SQLRockstar), I read a beautiful quote that convinced me I made the right decision:

“Without pain, without sacrifice, we would have nothing”

This is a quote from the movie Fight Club. It tells us that nothing comes without sacrifice. You can’t achieve anything without taking risks, and sticking out your neck. This is what I’m about to do…

I can’t tell you yet what my next challenge will be, but I’m very excited to start my new job. I’m hoping to write a blog post next week, telling you more about that… 🙂