January | 2014 | R4

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.

Here are my choice of links for the past week.

SQL Server:
- Brent Ozar’s What’s the Smallest SQL Server You Should Build? provides some advice on the hardware configuration for a “small” SQL Server that gets the best value from the licensing costs of SQL Server 2012, considering both the Standard and Enterprise Editions and physical and virtual servers.
- Klaus Aschenbrenner’s Why Transaction Log Auto Growths are degrading your performance explains why transaction log auto growth can bring performance problems
- Michael J. Swart’s SQL Simplicity Methods recommends that SQL is kept simple and provides some advice on how that can be achieved.
- Erin Stellato’s Performance Issues: The First Encounter provides a list of what to looks for when confronted with a performance issue in an unfamiliar environment.
- Jeremiah Peschka’s Dynamic Sorting presents an uncommon way to sort a query by a user specified criteria.
- Greg Low’s Reliably Dropping a Database in a T-SQL Script is Too Hard addresses the issue of ensuring a reliable way to drop a database using only T-SQL.
- Basit Farooq’s Understanding SQL Server Query Optimization – Part 4 addresses query optimization using the Database Engine Tuning Advisor.

Web Design and Development:
- Tero Piirainen’s Frameworkless JavaScript, in a very interesting post, explains the reasons for choosing not to use a Javascript framework when building Moot.
- Rob Eisenberg’s Durandal: Quick Start provides a brief introductory tutorial to Durandal.
- Derick Bayley’s SOLID JavaScript In A Wobbly World (Wide Web) provides a set of resources, including a video on applying SOLID principles to Javascript development.
- Aurelio De Rosa’s Managing Custom Data with the HTML5 Dataset API explains what the HTML5 data attributes are and how the Dataset API allows programmatic access to such attributes.
- Gabrielle Gosha’s 20 Distinctive Navigation Menu Designs provides links to 20 very interesting navigation menu designs.

Software Development:
- Tim Bray’s Software in 2014 provides an interesting analysis of the software development scenario, both server and client side as we enter 2014.
- Shivprasad Koirala’s SOLID architecture principles using simple C# examples presents and exemplifies, in a simple away the application of the SOLID architecture principles, using C# as the implementation language.
- David Budgen’s Design Patterns: Magic or Myth? describes an attempt to find an answer to whether design patterns are an effective way to exchange design knowledge and whether they help create designs that are simple to understand and maintain. The perceived reply, specifically to the second question, is not that clear.
- Neil Potter’s Scrum Lessons from the Trenches tries to address some of the challenges facing Scrum application by project teams.

Big Data / BI:
- Mike Wood’s An Introduction to Windows Azure Table Storage provides a good introduction to using Azure’s Table Storage, a key-value pair, non relational, storage.
- James Kobelius’s What Koby’s tea leaves foretell for big data in 2014 presents some predictions on what may happen in multiple big data related technologies in 2014.
- James Serra’s Business Intelligence/Data Warehouse Assessment provides the author’s view on how the steps needed to conduct a BI / data warehouse assessment.

That’s it for this week. Thanks for reading.

R4

Random Ramblings (on Technology, Programming and Databases,) by Rui Ribeiro

Monthly Archives: January 2014

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

My links of the week – January 12, 2014