T-SQL Tuesday #82 – To the cloud… And beyond!!!

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 “To the cloud… And beyond!!!”. If you want to read the opening post, please click the image below to go to the invitation.



 
As I mentioned in the invitation, Azure allows us to easily host and scale our platform. But it also comes with a whole new set of challenges. Some of the observations I made in the last few months:

 
Agent is missing in action…
One of the biggest challenges for me when I started migrating stuff to Azure was the missing SQL Server Agent. I never thought about how often I actually used it until I missed it! There’s no more “let me quickly schedule this script for tomorrow morning” anymore.

And although there are multiple solutions to this problem available in Azure (Scheduler, Automation) I picked the easy way out. Because we’re running 2 platforms in parallel (an on-premise private cloud and Azure), I decided to create a VM in the private-cloud platform to run operational tasks like scheduled maintenance for example. This is not a solution I would like to keep for the upcoming years, but it allows us to easily run scheduled tasks, without losing time on figuring out how Azure Automation or Scheduler works.

For more information on this, please read this post.

 
Contained datastore
A thing that can make migrations to the cloud a bit more difficult, is that Azure SQL databases are basically a contained datastore (you would call it a “contained database” when you run it on-premise). This means that you (by default) can’t connect from one database to the other. This could mean that you need to rewrite your applications or stored procedures, or maybe even redesign your entire database/application/domain model.

This also means that running a stored procedure from the Ola Hallengren’s maintenance solution can only be done on the specific database, and not from the master database like the on-premise version does. These small challenges can be overcome, but it does mean code-duplication in your databases because the maintenance procedures need to be deployed to every single database.

 
Running database maintenance
Just like on-premise databases, Azure SQL databases also need to be serviced every now and then. To make sure I’m not reinventing the wheel I’ve re-used a solution that has proved its worth in practice: Ola Hallengren’s “SQL Server Maintenance Solution”.

But because every Azure SQL database is a contained datastore, I’ve made some minor changes to his solution. You can read more about that here.

 
Performance issues & alerting
One of the biggest differences for me between an on-premise database and an Azure SQL database is how I need to determine the cause of performance problems and solving them. When you’re working on a on-premise database it’s easy to just look at the CPU and RAM used by your instance, and watch the disk IO in the performance monitor in Windows. But you don’t have those in Azure of course. There you need to work with “DTU” percentages, and “Data IO” and “Log IO” counters. But DTU (Database Transaction Unit) makes monitoring performance a bit too abstract if you ask me. Just look at the explanation Microsoft gives us regarding DTU:

 

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.

 
One thing I’m really happy with is that Adam Machanic published a new version of his sp_WhoIsActive for Azure a few months ago. This gives me the opportunity to quickly look at the results to find a cause of the performance issues.

 
When after a while you have a gut feeling about what the performance of your databases, you run into a new challenge with monitoring: the monitoring of DTU usage has a maximum retention of just 1 hour. This gives me another challenge, because I would like to see a longer retention. I definitely don’t want to over-scale my databases, so in some cases a long running process can trigger this alert. And when this happens during the day it’s annoying, but still okay. But it also woke me up in the middle of the night during an on-call rotation. This forced me to set some of the alerts on the maximum value of 45 minutes / 1 hour, and just disable others. In my opinion, this could use some work by Microsoft.

 
Conclusion
So in general I’m REALLY satisfied with the functionality that Azure SQL database provides us with, even though there’s always something to wish for of course. The move to Azure gives me as a DBA a new set of challenges and (in some cases) demands other/new skills, but it also allows me to do more than just manage a bunch of databases. I’m now also a part-time system administrator, network admin, (data)architect, developer, etc. This is something I personally really like, because it’s exactly that that allows me to broaden my horizon, and use a set of skills I didn’t for a while when working on on-premise databases.

So even though some people were afraid the DBA role would disappear because of the cloud-uprise, I’m convinced it will be here to stay except it will be a role with more skills needed than before, and I think that’s a good thing.

Invitation: T-SQL Tuesday #82 – To the cloud… And beyond!!!

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 I’ll be the T-SQL Tuesday host, and I’m really honored! Thanks for inviting me to be the host Adam!

 
The topic
When Adam asked me if I wanted to host another T-SQL Tuesday, I immediately knew a topic I wanted to talk about: The cloud, and (if you want to) specifically about Azure SQL database.

Last time we blogged about the cloud was back in december of 2013, when Jorge Segarra hosted this monthly party. Since then, “the cloud” (to use that buzz-word again) has changed a lot, and I think the possibilities are endless nowadays.

The reason I would like to see you all blog about this topic, is that I’m working with Azure SQL databases a lot now since I switched jobs in December. Currently I’m working for a small start-up that has a cloud-first focus. This means the main (if possible) host for our (data)platform is Azure. And although hosting our platform in Azure makes it easier for us to scale parts of that platform, it also gives us new challenges to overcome. And I’m probably not the only one with that experience…

So with that said, I would like to give you the opportunity to blog about the cloud, in the broadest sense that you can imagine. I’m imagining really interesting blogs about migrating to the cloud, missing features in Azure SQL database, how you’re determining the cause of and solving performance problems, etc. Please surprise us all with your view on the cloud.

 
The rules
– Your post must be published between 00:00:00 UTC and 23:59:59 UTC on Tuesday 13th of September
– Include the T-SQL Tuesday logo in the top of your post, and your post must link back to this one (trackback and comments are moderated, so it might take some time before they’re visible) or tweet about it using the hashtag #TSQL2sDay
– If you like this, check Steve Jones’ (Blog | @way0utwest) blog post that contains the list of topics, and contact Adam Machanic (Blog | @AdamMachanic) if you’d like to host this party yourself

T-SQL Tuesday #66 – Monitoring

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 “Monitoring”. If you want to read the opening post, please click the image below to go to the party-starter: Cathrine Wilhelmsen (Blog | @cathrinew).



 
Monitoring the performance or health of your environment might be the single most important thing in your daily job. But this might be the most important question you need to answer, before you start working on any monitoring or process. You don’t want to find out that your database isn’t working anymore, because someone from the business starts calling you his application isn’t working anymore. You want to know it as soon as possible, so when the business calls you, you can tell them you’re already working on it, or maybe even solved it. But how do you do that?

 
What do you need to monitor?
There are many different things you can and want to monitor, that’s why it’s difficult to explain the different aspects of monitoring. You don’t only want to monitor SQL Server, but also the server it’s running on. If a disk fills up, SQL Server will eventually stop working. But do you want to find out that you have a problem when your SQL Server stops working? I don’t think so…

But even within SQL Server, there are so much things you can monitor, depending on your needs. A number of these things are explained by Kevin Kline (Blog | @kekline) in his SQLBits session: “An End-to-End Troubleshooting Checklist for SQL Server”. He talks about troubleshooting SQL Server, but he mentions a lot of stuff you can start monitoring today. So if this will ever cause problems in the future, you can always look back at the history of a specific setting or configuration. You don’t want to spend time on searching for a previous setting value, when your SQL server isn’t working anymore.

 
Different ways of monitoring
There are different ways of monitoring your environment. Some parts you want to monitor with an automatic monitoring solution, that raises errors when you need to take action. But there are also parts of your environment, that you might want to monitor “manually”. Let me explain that.

 
SCOM
One of the corporate monitoring solutions you could use is System Center (SCOM). This gives you the oppertunity to monitor your whole environment from top to bottom: from server to specific disk. The downside is that it might be pretty expensive when you compare it to your other options.

And SCOM might be to overwhelming for your environment or process. Dealing with SCOM means you need a certain level of knowledge of your environment, and you need to know how to configure SCOM. This learning curve might be too much, when you just want to monitor a specific part of your environment or server.

 
SQL Sentry

SQL Sentry has proven itself as a tool provider with tools like SQL Sentry Plan Explorer. Personally, I use this tool every time I need to do some performance tuning. We’ve blogged about this tool with the SQLCoop team. It’s also featured on the SQL Sentry Plan Explorer page (which I’m very proud of!).

But besides that awesome tool, SQL Sentry is a company that built a number of monitoring solutions for your SQL Server. These can be found on their website. These are created specifically for monitoring SQL Server, and is a better choice compared to SCOM, if you just want to monitor SQL Server. You can use this if you want to monitor your whole SQL Server environment, with a tool build specifically for SQL Server.

 
Manual monitoring
Let’s say you have a production SQL Server instance. On this instance, you run a number of SQL Server Agent jobs. These jobs run on a specific schedule, and you want to monitor the outcome of those jobs. But perhaps you don’t need to take action when a specific job fails. If this is the case, using a big solution like SCOM for example, might be a bit too much.

Having a manual tool to check on job outcome and run time might be a better solution for you. If you want to do that, the SQL Server Agent job history window in SQL Server Management Studio (SSMS) might be the right tool for you. There’s no need for big tools, if your monitoring needs to be as basic as that.

Another tool you could use is SQLjobvis. I’ve mentioned this tool before, and I still use it almost on a daily basis. This can be used to monitor your environment “manually”.

A failed job can also send out a notfication by mail. This can be configured in the SQL Server Agent. This can be used to monitor your job activity as well.

 
Creating your own monitoring solution?
In the years I’ve worked in IT, I’ve seen a lot of companies and different monitoring systems. And there’s always that one company, that want to create its own SCOM implementation. They think the rest of the world is crazy, and that they can build a system better that’s better than everything else. Let me tell you, all custom build SCOM systems I’ve seen so far never lasted longer than a year. After 12 months, they use either another system on the side to fill up the gaps the custom SCOM system has left, or the custom SCOM system is gone entirely.

Maybe you can build a custom monitoring system for your business-specific processes (like checking the outcome of a webservice call), that could work. But when you’re building your own system to check the free space of a server, sorry to burst your bubble, you’re doing it wrong! Do you really think that you can build a custom system that can beat another monitoring solution that has evolved in 10+ years, and that has been tested in thousands of companies? I need to come across a system like that yet…

 
Conclusion
Looking at the different ways to monitor your environment, and the tools you can use for that, we can only conclude you have a world full of options. But in the end, you just fall back to 1 basic rule: if you monitor your environment, you need to act upon it as well. If a SQL Server Agent job fails, don’t just notice it and carry on your job, but act on it! Fix the job, or fix the underlying issue.

It’s basically the same as your backup process: when you’re taking backups of your database, and you never try to see if you can restore it, just stop taking backups! You can test, monitor and backup your entire environment, but if you don’t act on any issues, it’s all a waste of time. And I’ve seen that happening just too often…

T-SQL Tuesday #59 – My Hero

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 “My Hero”. If you want to read the opening post, please click the image below to go to the party-starter: Tracy McKibben (Blog | @RealSQLGuy).



 
Everybody needs a hero. Ever kid needs a champion. Someone you look up to. It doesn’t matter if you’re young or old, everybody needs that. If you don’t have a hero to look up to, it’s hard to dream big.

 
The unsung heroes
Not all heroes are out there like we expect them to be. There are some heroes that like to stay below the radar. But does that make them any less of a hero than a hero that is really out there? I don’t think so. But defining a hero is much easier when it’s a person that is well known by the community.

If you take one of the most known people out there, for example Thomas LaRock (Blog | @SQLRockstar), Brent Ozar (Blog | @BrentO), or Grant Fritchey (Blog | @GFritchey). Do you define those people as heroes? I know I do. They make an effort for the community, help people wherever they can, and they are working to make us all better SQL Server Professionals. But they are out there, and are widely acknowledged as heroes by many people. So defining those people as heroes, even though they are, is easy. It’s praising the unsung heroes that is hard.

 

 
The fallen heroes
There’s a well known saying that says: “All that glitters is not gold”. And that also goes for the heroes in the community. In the end, they are just people like you and me. They have good and bad character traits like everyone else. But some heroes turn villain in the end. They use their fame to get what they want, or mistreat other people for not being the hero they are. Unfortunately I know a few of those “heroes” as well…

But does mistreating people make that hero a little less of a hero? I think it does. Even heroes can fall of their pedestal. And even though people find you a hero, you can’t mistreat other people, or tell them they’re less than you. If you’re the hero, it’s your mission to lead other people to victory. Not to put them down, and convince them they can’t do something.

 

 
Become a hero
A hero can come in many shapes and sizes. And you don’t become a hero overnight (even though that would make things easier sometimes). It takes a lot of effort to become a hero, and to be seen as the hero. And you know what? They make it look so easy, and that’s their strength and weakness at the same time.

So when are you becoming a hero? What are your plans to become a hero? What steps do you need to take now, in a week, in a month, or even in a year? You can’t become a hero you say? Yes you can! Everybody can become a hero! Just try it, by giving back to the community. Answer questions on forums, write blogs, start presenting, anything is possible!

Are you the next hero Gotham needs…?

T-SQL Tuesday #57 – SQL Family and community – RECAP

Last week I had the privilege to host this months T-SQL Tuesday blog party. The subject was SQL Family and community, and a lot of people participated. There were a lot of great blog posts, and I wanted to make a complete list for you that contains all the blog posts of this month:

 
Warwick Rudd (Blog | @Warwick_Rudd) posted: SQL Family and community

Boris Hristov (Blog | @BorisHristov) posted: SQL Family and SQLHangouts

Cathrine Wilhelmsen (Blog | @cathrinew) posted: #SQLFamily – Pay It Forward

Mickey Stuewe (Blog | @SQLMickey) posted: SQL Family to the Rescue of a Local Community

Chris Yates (Blog | @YatesSQL) posted: SQL Family and Community

Ed Watson (Blog | @SQLGator) posted: SQL Family and Tripping the Light Fantastique

Aaron Bertrand (Blog | @AaronBertrand) posted: A SQL _VARIANT Use Case (No Pun Intended)

Jason Brimhall (Blog | @sqlrnnr) posted: SQL Family and
Community

Adam Mikolaj (Blog | @SqlSandwiches) posted: SQL Family and community

Glenda Gable (Blog | @ggable313) posted: TSQL Tuesdays # 57

Andy Yun (Blog | @SQLBek) posted: A #SQLFamily Story

Wayne Sheffield (Blog | @DBAWayne) posted: SQL Family and Community

Steve Jones (Blog | @way0utwest) posted: SQL Family and Community

Kenneth Fisher (Blog | @sqlstudent144) posted: SQL Family

Rob Farley (Blog | @rob_farley) posted: Nepotism In The SQL Family

Ricardo Leka (Blog | @BigLeka) posted: SQL Family and community

Jeffrey Verheul (Blog | @DevJef) posted: SQL Family and community

 
I want to thank all participants for their beautiful posts for T-SQL Tuesday. And thank you Adam, for allowing me to host this month!

If I missed your post, please let me know, and I’ll add it to this post.

T-SQL Tuesday #57 – SQL Family and community

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 “SQLFamily and community”. If you want to read the opening post, please click the image below to go to the party-starter: Jeffrey Verheul (Blog | @DevJef).



 
This months topic is SQL Family and community. The reason I chose this topic is the great SQL Server community. As I mentioned in my opening post, at first I didn’t know what to expect from the community, when I first started working with SQL Server. But it didn’t take long to notice the reason why everyone was to enthusiastic about it. They say the SQL Server community is friendlier and more helpful than all other communities. But it is?

It’s all about helping
A few years back, I attended a developer conference in the Netherlands called TechDays. One of the session I got to see was a session about spatial data by Bob Beauchemin (Blog | @bobbeauch). And yes, here is where the obsession for spatial data started I think. After the conference I started to play around with spatial data, and eventually I ran into a problem. At that moment I couldn’t find any useful resources online, and I knew Bob would know the answer.

After thinking about it a day or 2, I finally decided to mail Bob. This was a big step for me, because I was about to email one of the people I highly respect and look up to. The expectancy wasn’t too high, because he couldn’t possibly have the time to answer my stupid email. So I took the bull by the horns anyway and pressed send.. But to my surprise, it didn’t even take an hour before I received an answer. From Bob. THE Bob. I couldn’t believe it! The big Bob Beauchemin took the time to not only read my email, but even provide me with an answer to my question. And of course the answer was spot on, and precisely I needed to overcome my problem.

After that, I regularly emailed Bob about questions, interesting cases I came across, and many more things. But I didn’t send him an email for too long now, and I still hope I get to thank him for this in person one day. But this is where the community-vibe hit me.

 
From knowledge base to blog
When you get to work on all different kinds of projects, you end up with notepad files all over your desktop, USB hard drives, etc. At least, in my case that’s where it started. In order to create a structure in all these small solutions and notes, I decided to put them online. So basically I wanted to create a small knowledge base and one location where I could save scripts for myself, that I wrote before and might need in the future again. But after a few months, people started to thank me for my posts. I couldn’t understand why, because the posts were so basic, and sometimes totally gibberish in my opinion.

But after a while, I started to like the “blogging” (which was just brain-dumping until then). The blog posts became more coherent, written for a broader audience, and they were easier to follow and implement for readers. So the brain dumps grew into something new: useful information that other people could actually use! One of the things I’m most proud of until now is the spatial data series I wrote (yes, again spatial data). It grew from just 1 post (the basics of spatial data) to a series of 11 posts where I guide readers from the basics to building their own reports. This series was inspired by readers and colleagues that asked questions, and questions I had myself.

 
Online community
One of the greatest discoveries for me was the community on Twitter. Anyone you can imagine from the community is on Twitter, and available within a few clicks. And they all take the time to answer questions! This came as a big surprise to me at first. There are so much interesting people you can follow, interact with, and that can help you with technical challenges you have. In most cases, when you post a question with the hashtag #SQLHelp, you’ll get an answer within minutes. And you answer could come from anybody: An MCM from the UK, MVP from Australia, DBA from the US, a BI guy from South Africa, etc. All kinds of people with different skill sets and technical interests. And the variety of people make it worth while to follow and interact with them.

 
When strangers become friends
After being “active” in the community for a while, I started talking to some people of the SQL Server community online. At one moment I asked one of them to review a blog post, to see what she thought. That was the start of a friendship that brought me a lot the last couple of years. She introduced me to some other SQL Server professionals, that I consider close friends now. These friends support me, push me to do stuff when I need it, and help me in any way possible. They inspire me to do better, and take the extra step to achieve the goals I set myself. They are there when I need them, and that is the most important thing. And all of that because of SQL Family and the community! The community that I couldn’t believe to be more than a bunch of people with the same job and technical interests.

 
FORG
Being involved in the community is really cool, and there are benefits of that. For example, Red Gate gave me the opportunity to become a member of the Friends of Red Gate program, which was and is a great honor for me. I’ve loved using their tools for years, and now they gave me the opportunity to help make these tools better, by being part of this program. And hopefully there are a lot of cool things coming up, because I still have a lot of dreams and goals I set myself when I became a part of this program.

 
If you’re not involved yet, get involved!
The reason you should get involved in the community, is the fact that it’s really nice to help people, and you get so much in return. Getting help from someone is great, but helping other people makes you feel even better. At least, that’s my experience. So if you’re not already involved, start visiting local events like SQL Saturdays or online events like virtual users groups. It’ll give you insight in other SQL Server subjects, and show you that other people struggle with the same problems you do!

T-SQL Tuesday #57 – SQL Family and community

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 I’ll be the T-SQL Tuesday host, and I’m really honored! Thanks Adam, for this opportunity!

 
The topic
This month I would like to give everyone the opportunity to write about SQL Family. The first time I heard of SQL Family, was on Twitter where someone mentioned this. At first I didn’t know what to think about this. I wasn’t really active in the community, and I thought it was a little weird. They were just people you meet on the internet, and might meet in person at a conference some day. But I couldn’t be more wrong about that!

Once you start visiting events, forums, or any other involvement with the community, you’ll see I was totally wrong. I want to hear those stories. How do you feel about SQL Family? Did they help you, or did you help someone in the SQL Family? I would love to hear the stories of support, how it helped you grow and evolve, or how you would explain SQL Family to your friends and family (which I find hard). Just write about whatever topic you want, as long as it’s related to SQL Family or community.

 
The rules
– Your post must be published between 00:00:00 UTC and 23:59:59 UTC on Tuesday 12th of August
– Include the T-SQL Tuesday logo in the top of your post, and your post must link back to this one (trackback and comments are moderated, so it might take some time before they’re visible) or tweet about it using the hashtag #TSQL2sDay
– If you like this, check Steve Jones’ (Blog | @way0utwest) blog post that contains the list of topics, and contact Adam Machanic (Blog | @AdamMachanic) if you’d like to host