Jan 14

T-SQL Tuesday #50 – Automation – When repetition is really what you want


This post is meant to be part of January 2014 edition of T-SQL Tuesday, a rotating blog party started by Adam Machanic (@AdamMachanic | blog). This month’s edition is hosted by Hemanth.D (@SQLChow | blog) and the topic is automation:

“Automation plays a huge part in our lives and the DBA profession is not so different. A couple of years ago this topic was shared by Pat Wright. I would like to revisit this again and see how much of our approach has changed.”

At the beginning there was a shell script

My first serious dabbling with computing started back in college, and at the time, Unix was the only operating system around. Windows was still to be created and scripting was the natural way to do things, other than creating programs in a language such as C or Fortran. Back then I already preferred programming to just about anything else and didn’t have any administration responsibilities, so I wrote a few scripts, but can’t say I was very proficient. This really didn’t change much after graduation and even though I had the chance to try stuff in other scripting languages, such as perl, I confess I was never a heavy scripter. 

Oh, that amazing and dreadful GUI

Then, of course, the GUI came about and it didn’t really contribute to change the state of things very much. GUIs are pretty much amazing, when you want to have an easy life. A few clicks here and there and things are done.This works fine when your needs are confined to a database at a time,the GUI makes it really easy to do most of the things. The reverse of this is, of course, a bit of incentive not to really delve into things, especially  when time is short and the GUI is nice and good enough to do what you need.

Ok, now repeat it 80 times

GUIs are not so nice when whatever you need to do, needs to be done not once or twice, but many times, in a repeated manner. I think my first encounter with automation in SQL Server was 10 years ago, when I found myself responsible for 80 undergrads and had to provide them with databases and proper access to such databases, as part of the database design course I was going to teach. The solution was obvious – write a simple T-SQL script, creating the databases, the logins, the users, the permissions and make sure everything works fine. Of course, while you are at it, write one script to clean everything up when the semester ends.

Since that first time, the T-SQL script has become a good friend. Although my automation needs are not very pressing, it is usually based in T-SQL. even if s part of a bigger SSIS package and directed with timely precision under the guidance of the SQL Server Agent. On my company, we really only host development servers, so this fulfills our automation needs rather well.

Repetition, yet again

Things will probably change soon, since we are launching a new application that will, mean the need to deal with a big number of databases (hundreds or even thousands). Expecting our automation needs to change because of that, we’re looking into other options, including the obvious Powershell, but also considering the possibility of developing apps that can make our expected tasks easier. Its’ even a bit of a coincidence that this topic should be the subject of this month’s T-SQL Tuesday, since we are evaluating our options for automation at this very moment. I will certainly be looking at the contribution of the SQL Server community for additional insight on this matter.


Nov 12

T-SQL Tuesday #48 – Cloud Atlas – #TSQL2sDay


This post is meant to be part of Novembers 2013’s edition of T-SQL Tuesday, a rotating blog party started by Adam Machanic (@AdamMachanic | blog). This month’s edition is hosted by Jorge Segarra (@SQLChicken | blog) and the topic is the cloud:

“This month’s topic is all about the cloud. What’s your take on it? Have you used it? If so, let’s hear your experiences. Haven’t used it? Let’s hear why or why not? Do you like/dislike recent changes made to cloud services? It’s clear skies for writing! So let’s hear it folks, where do you stand with the cloud?”

The personal perspective

 It’s hard not to have been exposed to “The Cloud”, either through our personal use of technology – a smartphone, a tablet, a modern operating system – all of these offer cloud based services in a way or another. Confronted with these offerings, it’s not hard to find many people who see these offerings with a bit of mistrust. Who are we entrusting our data to? Who gets access to it? How is our privacy ensured when we trust personal data to a cloud provider? Who can guarantee that we are not trusting our data to a provider that won’t be here in a year or so?

These are all questions that I regularly am faced with, especially at Windows Secrets, where I am a moderator. Some of them are quite pertinent and I can’t say that I have an answer for all of them. Of course, when adopting the cloud, and especially if we are using it to store valuable personal data, such as contacts, documents or, even more so, passwords, a great deal of care must be taken with the choice of provider, which can answer some of the previous questions.

In terms of personal use, I confess I am less hesitant. I am using multiple cloud services from SkyDrive to Cubby, LastPass or VisualStudio.com, to keep documents, backups, or even code related to personal projects. Being fully invested into the Microsoft stack, I own a Windows Phone and run Windows 8 on my laptop, all my contacts are now kept at outlook.com and the Microsoft account has become increasingly important.  I am, thus, a cloud user and I can say that I am coverted to the Cloud’s benefits.

The professional perspective

Having changed jobs a year ago, my first project at my current position was fully into the cloud. I can say we are really a “Microsoft shop” and the project was a web app, that we will be releasing soon and that was hosted on Azure and used SQL Azure a backend. It was not an easy start, since we found SQLAzure not a really the best option, at the moment, both from a performance perspective, and from a cost perspective. Although we ended up deciding not to use Azure for this specific project, it’s clear the cloud offers quite a few advantages in terms of availability, scalability, ease of management, even cost, since the use of resources only when you need them can be very interesting. 

For anyone developing using Microsoft technologies, which the case of my company, Microsoft is building quite a compelling case. The assortment of easy to use management tools, the integration of Azure with some of the usual Microsoft development tools, like Visual Studio, the sheer convenience of having a Virtual Machine running the OS of your choice, in a hardware configuration that can be suited to the specific use case at hand, in just a few minutes, all of these make quite a compelling case for “The Cloud”. Of course, Microsoft is not the only cloud player and the competition between cloud providers is bringing costs down, which makes the cloud even more compelling.  

The cloud is seeing its popularity increasing, accompanied with with improvements on technology and management tools, at the very same time that NoSQL is gaining acceptance. These two technologies go pretty much hand in hand, since NoSQL has infrastructure requirements that would be harder to meet without cloud providers. So I think we’ll be seeing both evolve and gain more traction as time goes by.

Just as in the case of NoSQL, the cloud is no silver bullet. Using either of them only makes sense with the proper use cases, something that doesn’t happen all the time. It’s not hard to find examples of use of a given technology, just because it is the new hype. So, for both of them, it’s important to use them in scenarios where they make sense, both from a technological and an economic point of view. 


There are very compelling reasons to adopt the cloud and in the scenarios where that makes sense, we’ll see its usage increasing. Technology will keep improving and we’ll certainly see more scenarios where the cloud will be a valid option. For anyone working on IT, the cloud as a viable technological and economical option, is already something that needs to be considered. Failing to do so, may be costly and a competitive disadvantage.