SQL Musings

Autogrow guidelines

Steve Jones — Sat, 03 Dec 2011 01:30:30 GMT

I always enable autogrow on my databases. However, it’s there for emergencies, not as a space management tool. I monitor disk space, and I grow my files manually as space runs low. I want to control the growth, but in the event of a runaway process or some unexpected event, I want autogrow enabled to hopefully prevent a database crash.

What level of autogrow do you enable? That was a question I saw recently and it made me stop and think a bit. I asked the question on Twitter, but got very few responses and no real guidance from others.

In my mind, you want to enable a specific level of autogrow that will be likely to handle something unexpected in a single, or maybe a couple growths.

The two options for autogrowth are:

percentage
fixed size growth

In general I think a fixed size growth is the setting to choose. This offers more control and as your database size grows, it’s unlikely you want to grow at a percentage. A 10% growth of a 100MB database is 10MB, almost a rounding error on many of today’s drives.

However a 10% growth on a 2TB database is 200GB, which could easily exceed the free space on the drives that make up a file,which is where you set the autogrow specifications:

As the file size increases, the disk space goes down, but the percentage growth goes up as well, exacerbating the problem.

So what are the guidelines?

I think that you have to look at the data growth and each database and what is possible for data growth in that database. Some some database that does ETL work I think it’s more likely you could have an unexpected large or duplicate import in a process that would cause larger growth.

However the question I was asked talked about setting a guideline for hundreds of databases, where in the short term it’s impractical to review every database. For those, I offer up these guidelines, though I’m happy to have someone give better guidance.

MDF Size	File Autogrowth
< 1GB	100MB
1GB < mdf size < 50GB	2GB
50GB < mdf size < 200GB	5GB
200GB < mdf < 1TB	10GB
> 1TB	50GB

These are guesses on my part, based on some experience, but the largest database I ever managed was a 600GB one, so I’m guessing on the TB scale.

I’d welcome your comments and experience in this area.

Filed under: Blog Tagged: administration, sql server, syndicated

Forgiving Mistakes

Steve Jones — Wed, 30 Nov 2011 22:57:30 GMT

My main phone line at the house has been on the fritz for about 3-4 weeks. Yes, we still have a main line and it’s a legacy holdover from having that number for a decade and it links do our DSL account. We may get rid of it at some point, but for now we wanted it working. We actually weren’t aware it was down until a service repairperson told us when they arrived. We checked, and sure enough, no dial tone.

My wife and I are somewhat savvy and we tried to debug it, but it was down. With travel and holidays, it wasn’t until Tuesday morning that we called CenturyLink for service and I was pleasantly surprised by a technician arriving in the early afternoon.

He tried a few things in the house, then set up a tone generator and drove back out to the street. Yep, he drove. My driveway is 1/4 mile long and it’s another 1/4 mile to the paved road, and a mile and a half to the main road.

About ten minutes later he came back and said things were fixed. We checked, and sure enough, we had dial tone. He then explained what happened: our line was cut.

I was surprised and worried, until he explained that when you service lines in the boxes, there is often a little extra wire on a connection. Small scissors are used to snip that off and clean the connections, but there are a lot of wires in there and sometimes one gets accidently cut by a tech. He surmised that another tech was in a hurry and accidently got one of our lines (we have two). He apologized again, we shook hands and went on our ways.

My Reaction

I wasn’t quite sure how to react to the situation after that. Should I complain to CentuyLink and seek some credit on our bill? Should I vent on the Internet about shoddy workmanship? In the end, I told my wife it was fixed, explained what happened, and we both let it drop.

Andy Leonard wrote a great piece on stress during the holidays with a very important section on forgiveness. I forgave CenturyLink because it seemed like the right thing to do. A few dollars on my bill aren’t worth the time it would take me to call and the aggravation I’d cause myself and some customer service representative in order to file a complaint and seek credit.

I make mistakes at work. Heck, I make them all the time, and I don’t expect to be banned, burned at the stake, or excommunicated for them. They’re mistakes. I learn from them, I try not to make them again, I try to do my best, but I know that there will be a few that I make.

I expect my co-workers will make mistakes, and I have to live with some of those. I might be annoyed, or inconvenienced, but I understand that most of the time they are just that, mistakes.

I wonder sometimes if we’ve forgotten how to forgive in this world, forgotten how to tolerate the imperfections that exist in all of us, and in the work we do.

Filed under: Blog Tagged: life, syndicated

Shutting down for a week

Steve Jones — Fri, 18 Nov 2011 05:30:50 GMT

I try to speak and write about career things on a regular basis because I think they’re important topics for those of you working with SQL Server. Arguably they are as important as the technical stuff. One of the things I try to preach is “balance” and making sure that you remember that we work to live, not live to work.

With that in mind, I’m searching for a bit more balance in my life. This year has involved more speaking and traveling for me, and it was too much. I have been unbalanced the last 60-90 days, but I’ve had commitments to keep and I’ve made them. It’s been hard and now I need to get back in balance.

One way I’ve wanted to do that is to ensure that I take my vacation each year. I have had a few breaks this year, but not enough, and I’m trying to rectify that. I’m on vacation from Friday, Nov 18, and won’t be back at work until Mon, Nov 28. My family and I are taking a week to ski in the mountains, and I’ll be enjoying a half day at The Woodworking Show Friday before we leave.

Enjoy your own holiday, stay away from email and work if you don’t have an emergency, and remember to have a good break next week if you get time off for the Thanksgiving holiday.

Filed under: Blog Tagged: career, life, syndicated

The First Thing To Do After Creating Keys

Steve Jones — Wed, 16 Nov 2011 06:40:56 GMT

I’ve been looking at some security code in preparation for a few talks I plan on doing next year and there’s one thing I don’t see in many of the samples: backups.

When you get ready to encrypt data in a table, or you enable Transparent Data Encryption (TDE), you will end up creating a key of some sort, or a certificate. Some of these keys, especially the database master key, needs to be backed up. Otherwise you won’t be able to decrypt your database or data later on if you recover this database on another server.

As soon as you create a key for security, make sure you back it up right then. You might forget later, and then it might be too late. A little preparation will go a long way here.

The commands are simple, and linked below:

Securing these keys can be complex, but I would recommend that you place them in an administrative folder with limited access from anyone.

Filed under: Blog Tagged: security, sql server, syndicated

SQL Inspire NYC – 2011

Steve Jones — Tue, 15 Nov 2011 06:37:36 GMT

The lineup for SQLInspire

I spoke at the SQL Inspire event this last weekend and it was a greart experience. This event was sponsored by Linchpin people (founded by Andy Leonard, Brian Moran, and Matt Velic) and supported by Robert Pearl, Melissa Demsak, and others in the New York/New Jersey area.

The event is modeled after the TED events, which was inspirational and informative talks that bring people together to discuss ideas and thoughts about various topics. In this case all the speakers were technologype focused people, but bringing different points of view or frames of reference to our SQL world.

The event was held at the Microsoft office in Manhattan, the same place as the SQL Saturday event held there last year. I arrived just as the event was starting, and had breakfast. There was plenty to go around, which was good as I’d forgotten to pack the protein bars I normally eat in the morning.

There was plenty of coffee as well, and I had a cup while watching the event start with Robert Pearl talking about telecommuting. He had some good ideas for people that want to work from home, including some data and arguments that you could give to your boss.

The event was structured as a series of 20 minute talks, with a few breaks throughout the day. Tom LaRock had come down from Boston with his daughter. He had a great talk about “Someday”, which as he noted, “isn’t one of the seven days of the week.” Tom cautioned people against waiting too long to do things, or putting off the parts of their career that are important to them, or even the simple things that need to be done in every environment, like making sure you have good backups. He had one point in the talk where he was looking forward to things in the future, and he choked up while mentioning the chance to walk his daughter down the aisle at her wedding. It was a touching moment, and I was choked up as well thinking about my little girl.

SQL Someday

Michael Coles talked about the job market, Andrew Brust talked about Big Data, and Brian Moran talked about Linchpin people and how he is trying to change the way that people view their work and their business. Michael Corey had a great talk about how he had grown his career from DBA to CEO of a consulting company and how he runs a successful, moral, well balanced place to work. I grabbed a card from him and will keep NTirety, his company, in mind if I ever need a job. They seem to work the way I’d like to work.

My talk was right after lunch and titled “Finding Balance”. My focus was on the ways in which we can all manage to improve our careers, get raises, promotions, etc., while still managing to have a life. I’ll blog about it more, but the main ideas were three things I think are important to balancing out your life.
- Learn to say no
- Play your own game
- Balance

The entire event was video taped and recorded, and I believe that the videos will be available in a few weeks for people to view. There were about 60 or so people there throughout the day, and they seemed to enjoy the talks. Quite a few people came up to me and other speakers at the end to thank us for the information we presented, and they seemed to be making notes or engaged with the speakers during the day.

Video by And Now Video

There were a number of sponsors that helped bring this event to fruition, including my employer, Red Gate Software, along with Confio, Embarcadero, and Microsoft.

This was a much different type of event than many SQL Saturdays. The talks focused on abstract, more soft-skill topics, that were designed to inspire you, and get you to re-think the way that your career is progressing. I hope there are more of these events, or even that they can be held alongside other SQL Saturday events as a change of pace from the strictly technical presentations.

New York City was great, with mild weather on a November weekend. The sun shined as I went for a run out to Central Park on Saturday morning. I stayed at The Pod hotel, which is a hostel, and an ecclectic change of pace from many other places I’ve visited. We had a few people come out after the event to share a few beers and talk about the event, life, and a few other topics.

My thanks to Andy, Brian, and Matt and I hope they are able to run a few of these events in other places in 2012.

Filed under: Blog Tagged: speaking, syndicated, travel

Quite a salary

Steve Jones — Wed, 09 Nov 2011 00:06:23 GMT

If this article is true, and I suspect it is for some software engineers, why aren’t more people learning programming?

I think that we have a few problems in the industry, and I have an editorial coming out soon on this. One is that this type of money is rare. So many people work for companies and they make a good living, but they work hard. We have a perception issue of this industry being geeky, long hours, not a lot of respect, etc. I hear so many complaints from people that it’s hard sometimes to decide if they really like their jobs or they’re stuck in them, much like I hear complaints from people in accounting, or sales, etc.

The other issue is that we haven’t done a great job of getting younger people interested in computers. We require more computer work in schools, and my kids use computers as much as I did when I was 12 and a geek, but they’re using computers as tools to produce an end product, not as a way to build another tool.

We need to advocate, interest, and excite more kids about technology as a career if we want it to grow.

Filed under: Blog Tagged: career, syndicated

A view has no data

Steve Jones — Tue, 08 Nov 2011 21:13:00 GMT

I have seen quite a few posts and questions lately from people that are trying to change the data in a view, or move data in a view.

A view has no data.

It’s that simple. If you have something like this in AdventureWorks:

SELECT firstname
, lastname
 FROM HumanResources.vEmployee

And this view is defined as:

CREATE VIEW [HumanResources].[vEmployee]
AS
SELECT
e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode];

The SELECT statement is the same as running

 SELECT
 c.[FirstName]
,c.[LastName]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode];

Note this is exactly the same thing as the view definition with fewer columns included. Or it could be written like this:

SELECT firstname
, lastname
 FROM
 (
SELECT
e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
 ) a

In this case I’ve moved the view definition into the FROM clause of my SELECT query.

A view is literally a stored query that you can use to make it easier to write code. There is no data in the view, so if you need to change the data, or “refresh” the data from another database, you need to move the data in the tables that are referenced in the VIEW.

Filed under: Blog Tagged: syndicated, T-SQL

Slide Decks for PASS, SQL in the City, and SQL Server Connections

Steve Jones — Mon, 07 Nov 2011 22:25:39 GMT

I’ve sent my slide decks to the organizers, but I know they don’t always get posted. So I’m putting a few links up here for my talks.

If you check the page for my Preparation for Disaster talk, you’ll find I have links for the two recent events to the decks. I often version the decks after talks, and so I’ve decided to keep all versions live.

I’ve also uploaded the decks from PASS if you’re interested.

PASS Summit 2011

SQL in the City – LA 2011

Preparation for Disaster

SQL Server Connections

Preparation for Disaster

Filed under: Blog Tagged: career, disaster recovery, speaking, sql server, syndicated

Get Inspired – SQL Inspire 2011 New York

Steve Jones — Wed, 02 Nov 2011 00:50:00 GMT

Coming up next week is the SQL Inspire 2011 event in New York City. It’s an event with a number of SQL Server community speakers that are here to inspire you in your career, your life, and with SQL Server. The talks cover a variety of topics, and they look interesting. I am very excited to attend the event, and give my talk as well.

The event is from SQL People, the brainchild of Andy Leonard and Brian Moran, designed to build a stronger community by having us inspire each other. This event is based on the TED events, which are inspirational and informative talks that look to explore new ideas in a variety of topic areas. I have loved watching many of the talks, and while I’m not sure I’m up to the same level of presenting as many of these people, I’m going to try.

Please come if you are in New York on the 12th of November. It will be a lot of fun, and hopefully a very motivational event.

Filed under: Blog Tagged: career, speaking, syndicated

Document Your Day

Steve Jones — Tue, 01 Nov 2011 01:54:00 GMT

You should be documenting your day, your week, your month, on a regular basis. Take a few minutes when you accomplish something and make a blog entry. This is a great way for you to keep track of what you’ve accomplished during the year, and be ready to talk about it in an interview, or in a performance review.

You don’t have to publically blog this. You can use Word, emacs, Live Writer, text files, whatever works, but, keep track of it. Read through it when you need it and summarize your accomplishments for the quarter or year. This is also something you consult every quarter when you touch your resume.

And when you get ready for that review, read Kendra Little’s post on asking for a raise.

Filed under: Blog Tagged: career, syndicated

Buckets for Disaster Recovery

Steve Jones — Thu, 27 Oct 2011 01:46:00 GMT

Disaster recovery can be a huge project at any company. Considering the ways in which you build a plan that covers all the infrastructure, and it can quickly become a full time job for someone. The details, and the scope of the project can be overwhelming when you try to address your environment.

There’s a way to make this easier: buckets. If you can group your systems into a few gross buckets that define those systems that have similar needs, you can make life simpler.

In the past, I have typically built four buckets of systems for DR purposes, though I think you could go with three. These are the buckets I’ve had:

Critical Systems – high priority systems that typically must be running the majority of the business day. Note that you may have a 24 hour business day in which case downtime must be minimized to minutes.
Low priority – systems that we can function without for a day or two if we need to. Often development systems fall into this area.
Everything else – Medium levels of priority.
Not recovered – This is an optional level, but it’s almost always been a set of test systems, maybe some development systems that aren’t important enough to worry about.

The fourth bucket, the “Not Recovered” bucket doesn’t mean you abandon those systems. In the event of an issue, you would make an attempt to recover them, but you might not spend time or resources practicing or preparing for the recovery effort.

Bucketing your systems is the best way to easily manage your preparations for disaster, and also set some gross priorities. You might end up recovering systems within the bucket in different orders, depending on what happened, but this gives you an easy way to allocate resources, both in a disaster, and in preparation.

Filed under: Blog Tagged: disaster recovery, syndicated

The Basics of Joins – Skill #4

Steve Jones — Wed, 26 Oct 2011 01:19:00 GMT

This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

Databases are built to store data. That’s the primary purpose, and in SQL Server, we store data in a relational form. That means that often we have data spread across multiple tables. Why we do this is a discussion for another day, but suffice it to say that we often have structures like this:

Part of the person.contact table in AdventureWorks above and the HumanResource.Employee table below.

One typical join task might be to get an employee’s name, or a list of employees and their names. Here we have a birthday in the Employee table, but we don’t have a name. That’s in the Person.Contact table. Essentially we want to match these up using basic, elementary school set theory.

In the diagram above, you can think of each letter as a row in a table. As an example, let’s assume that B in the orange circle represents the row in the employee table with a ContactID value of 4. The B in the pink circle would represent the row in the Contact table with a ContactID value of 4 as well.

When we join these to get the Employee name and birth date, we get:

I used a join in my query to get that:

SELECT
  c.firstname
, c.LastName
, e.BirthDate
 FROM person.contact c
   INNER JOIN HumanResources.Employee e
     ON c.ContactID = e.ContactID
 WHERE c.ContactID = 4

In this query I’ve included two tables in the FROM clause with the INNER JOIN key phrase between them, which specifies I only choose the matching rows. The match is made in the ON clause.

I’ve also qualified this to only apply to the row with a ContactID of 4 in the WHERE clause.

There’s a lot more you can do with joins, and you can include more than two tables, such as this query:

SELECT
  c.firstname
, c.LastName
, e.BirthDate
, pa.AddressLine1
, pa.AddressLine2
 FROM person.contact c
   INNER JOIN HumanResources.Employee e
     ON c.ContactID = e.ContactID
   INNER JOIN HumanResources.EmployeeAddress ea
     ON e.EmployeeID = ea.EmployeeID
   INNER JOIN person.Address pa
     ON ea.AddressID = pa.AddressID
 WHERE c.ContactID = 4

I would recommend that you practice working with basic joins, based on the information that you commonly see queried in your application. Sooner or later someone will ask you for some data that isn’t available in the application and you will want to write a query to extract it for them.

Filed under: Blog Tagged: syndicated, T-SQL, TopTenSkills

It’s Your Career

Steve Jones — Mon, 24 Oct 2011 22:22:00 GMT

It’s your career. It’s something you have to take ownership of and work on. I know that life is busy, and training budgets are tight. That’s one reason we started SQL Saturday; it’s a way to bring a training event and a conference experience to many people.

I posted this tweet almost a year ago, seeing Brent in a class somewhere, learning and taking notes during some session. It was in humor, but I’m a little serious here. We all have more to learn, and while you don’t need to cram it all in this year, you should be taking advantage of your user group, local events, conferences, classes, even reading something in a newsletter on a regular basis.

Many of us are out here to help. I’ve spoken at 12 events this year, 10 of them free, and will be at another free event this week (SQL in the City – LA). However, you’ve got to make the effort to improve yourself. I , and many others, will try to help you, teach you, but you’ve got to do some work yourself.

Pace yourself, learn at a reasonable rate given the other responsibilities in your life, but don’t ignore this aspect of your career.

PS – If you’re in the LA area, there’s still time to register for SQL in the City and get a free day of training. I’ll also be at DevConnections next week and SQLInspire the week after that.

Filed under: Blog Tagged: career, speaking, syndicated

Fun Pix

Steve Jones — Fri, 21 Oct 2011 20:59:00 GMT

Various photo uploads from the past year.

Filed under: Blog Tagged: Humor, pictures, syndicated

Penance

Steve Jones — Thu, 20 Oct 2011 23:28:23 GMT

Buck Woody wrote a fantastic post this morning for speakers to think about. All too often I have seen some speakers at technical events adopt the prima donna stance, expecting gifts, special treatment, free food and drinks, and more. I do think that events should try to recognize speakers when they can, but for smaller events, gifts and dinner can be hard. Ultimately I want events to be sustainable, with 100 SQL Saturdays a year and there isn’t enough money to make them all lavish smorgasborghs of delight for those that present.

There was a bit of a discussion recently about cancellations and penalties for speakers at events. Cancellations happen, and sometimes are unavoidable, however they have a huge impact on organizers. There are any number of reasons for cancellation, some legitimate, some suspect, and since Andy Leonard likes elegant solutions, I have one:

If you cancel speaking at an event, the event bans you from speaking the next time they have the event. You get an appeal to the program committee to state your case, but their decision is final.

But Steve, my wife broke her arm and I had to leave early. Why can’t I speak next year?

I completely understand. If you have a family emergency, or you’re sick, cancel. Take care of you and yours. That’s understandable, no one can blame you, and you can appeal to the committee and state your care.

However do your penance. There’s no entitlement here for speaking. I believe in making the decisions that are ethically and morally just for each person, but then be man or woman enough to stand up for your decision, regardless of the consequences.

I cancelled on SQL Connections a couple years ago because my wife broke her arm as I was landing in Denver between events. I wasn’t speaking, but had committed to help cover the event for my company. They would have been justified in telling me they wouldn’t pay for me to attend anymore. And I would have accepted that as a result of my decision.

I cancelled on the Louisville SQL Saturday this past January, after I had submitted a session and been accepted. I gave them as much notice as I could, in this case I told them in Nov and they had two months. My reason: my middle son’s black belt test was the same day.

I am still apologizing to Malathi for my decision, and if she decides not to allow me to speak anymore, or any other SQL Saturdays cite that as a reason to not choose me, I accept the consequences.

We are adults, we are professionals, and if our other obligations intrude and override our commitment to speak, accept the consequences. It might suck, but it’s the adult thing to do.

Filed under: Blog Tagged: speaking, syndicated