Voice of the DBA

Author: way0utwest

T-SQL Tuesday #77–My Favorite Feature
This month is an interesting, but tough topic. The blog party is hosted by Jens Vestergaard, and his invitation is short and simple. Pick your favorite feature and write about it. This is good, because SQL Server has grown so much, I’m sure that many people will choose different things. However it’s hard for someone that works with many different features.

My Favorite Feature

I’ve been working with SQL Server since 1991. I’ve worked with all the Windows versions, and a few on OS/2. That means I’ve had the chance to manage and develop applications on:
- SQL Server 4.2
- SQL Server 6/6.5
- SQL Server 7/2005
- SQL Server 2008/R2/2012/2014/2016
I’ve seen the platform grow and expand quite a bit. I’ve spoken on a number of topics over the years, as my jobs have changed and my emphasis has wandered. Of all the features available, however, if I have to choose one, it would be…

SQL Agent.

I’m a programmer at heart. I grew up admiring the power of computers to execute code over and over again. I appreciate the ability of computers to remember things and remind me, or to handle them on their own.

SQL Agent allows that. Over the years, I’ve taken advantage of SQL Agent to perform maintenance on systems, to alert me to issues, to run a process that needed to be performed. I can even schedule one off jobs, having them delete themselves. I can’t tell you how many times someone has asked me to run something “later” on the server, often during the evening. It’s easy to schedule a job for later, have it run one time, and then let it disappear.

For example, Andy asks me to run a query tonight that gathers some data. I create a new job and step.

I set up a one time schedule.

Certainly I can set alerts and logging, but in notifications, I can have this job disappear.

I certainly want to make sure I have results saved, but this allows me to execute code, without much effort, and in a way that doesn’t clutter up my system.

I’ve found SQL Agent to be incredibly easy to work with, and quick to build jobs running against my SQL Server. I don’t need to setup connections, like I might need to with the Windows scheduler.

If you haven’t experimented with SQL Agent’s capabilities, or you don’t use it extensively in production, you should.
April 12, 2016
A Welcoming Conference

I’ve noted it in the past, but it bears repeating. SQL Bits is my favorite conference to attend, both as a speaker and attendee. The event is a rare combination of great technical content, first class conference management, and a casual, fun atmosphere. Add to all of this the fact that conference moves to various venues around the United Kingdom, and it’s become a can’t miss event for me.

Each year I’ve gone, I’ve learned some really fascinating things about the SQL Server platform. I’m not sure why, but I always see some great sessions that tackle interesting problems. Perhaps this is because many of the sessions are voted on by the community, rather than chosen by a small committee. Maybe it’s because the speakers have more leeway and aren’t bound by so many strict rules about what they can and can’t say. No matter what the reason, the content is top notch and worth watching. You can watch previous sessions at home, anytime you want.

However, I’d really recommend you attend the event. Saturday is free, like a SQL Saturday. The selection of pre-cons is amazing, and covers topics I don’t see often at other large events. Plus there’s the amazing party with a new theme each year. I’m excited to see what costumes come out this spring. You can check out the amazing super hero looks from last year or the Steampunk ones from the year before. Perhaps what I’ve really enjoyed is the casual way the party is run, with a few random spouses or friends coming and not being hassled by security. One of these days, I’m looking forward to being able to bring my wife to Bits, and have her enjoy the event with me.

Certainly this is a long way for many to travel for a conference, though it’s probably not that much more expensive than trying to make any of the large conferences in US if you must travel out of state. I’m lucky that I usually get to speak and I can combine this trip with a visit to the Redgate Software offices. If you have a choice of which conference to attend, I’d take a hard look at SQLBits, and maybe split the cost with your boss. It’s a great excuse to take a short vacation before or after an intellectually stimulating, emotionally relaxing, and thoroughly enjoyable event.

Note: You have until April 15 to post a review of a SQL Bits conference or video for a chance to win a pass to the conference.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and LibSyn.

April 12, 2016
Changing the sa Password with SSMS–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I wanted to make a quick note on changing the sa password, as this is a sensitive account, and the password should be changed if you ever suspect it is compromised. I’d also recommend you change this if anyone that knows the password leaves your group.

Changing sa with SSMS is really simple. Follow these steps:

Connect to SSMS as a sysadmin. You can check this for your login. Then expand the Security folder and the Logins folder. Right click the “sa” account and choose properties.

Once you do this, you’ll get the login properties dialog, and see the Password text field at the top in the General tab.

You can type a new password in the Password box, and confirm this in the Confirm Password box. Password policies checks are up to you, though I’d recommend you use them.

Click OK and the password is changed. You can then connect a new query window as “sa” and verify your password.

SQLNewBlogger

Make sure you know how to do this. It’s a basic skill, so learn it, blog about it, and use it where appropriate. Maybe write about why you’d do this in your own post.

April 11, 2016
The Data Dewey Decimal System

As a small child, I learned about the Dewey Decimal System in school. Those were the days when you wanted to look up some information or check a fact, you needed to peruse the dead tree books in a library, with information organized by the Dewey Decimal System in the US. At least, all libraries I used in school adhered to this.

These days we usually use a computer of some sort for learning, research, or really most any work with data. Often I start with Google to find my way to the source of information, but that’s not something that necessarily works well with finding sets of data. It certainly doesn’t work well within a an organization.

I saw recently that Microsoft announced the general availability of the Azure Data Catalog, which is designed to provide a catalog of data sets. In essence the Data Catalog is an index of the data sets that might be produced by your organization, with the information about the data filled in by the producer of data. Users that are looking for data can query the catalog instead of asking coworkers, wandering through the enterprise databases, or even relying on their own memory of where data might be located.

At first this seems silly, after all, don’t people inside of an organization know where data is kept? Don’t they learn the servers, databases, and connection methods? Certainly many do, but as with the pace of change these days, as well as the rapidly growing number of ways to publish data these days, it’s entirely possible that many people aren’t aware of all the data sources available inside of an organization. Even at Redgate Software, with a few hundred employees, it is fairly difficult to keep track of what data exists in which location.

The functionality of the Data Catalog seems a bit basic, and really almost like an extension of adding extended properties to various tables. Certainly things are centralized here, which is good. There are also ways to add other sources, such as SSRS reports, files, and even other relational sources. I’ll have to experiment a bit and see what’s available, and I might encourage you to do the same. The product relies on crowdsourcing, which can go really well, or really poorly, depending on how cooperative your crowd is.

In any case, I do like the idea of having a central catalog that individuals can update as they produce data sources for others to consume and change what’s available. If it works well, with good searching and tagging, it might eliminate some of the redundant work often performed to surface data inside of any organization and let employees know how to find the answers to their questions.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.9MB) podcast or subscribe to the feed at iTunes and LibSyn.

April 11, 2016