Voice of the DBA

Author: way0utwest

Rename a Primary Key–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Having system named objects is one of those things that people may debate, but as you move to more automated and scripted processes can cause you issues. In the case of a Primary Key (PK), if you do something like this:
```
CREATE TABLE OrderDetail
    (
      OrderID INT IDENTITY(1, 1)
                  PRIMARY KEY ,
      OrderDate DATETIME
    );
```
What you get is something like this:

When you compare that with the same table in another database, what’s the likelihood that you’ll have the PK named PK__OrderDet__D3B9D30C7D677BB4? Probably pretty low.

This means that if you are looking to deploy changes, and perhaps compare the deployment from one database to the next, you’ll think you have different indexes. Most comparison tools will then want to change the index on your target server, which might be using this technique. Or the choice might be something that performs much worse.

What we want to do is get this named the same on all databases. In this case, the easiest thing to do with rename the constraint on all systems. This is easy to do with sp_rename, which is better than dropping and rebuilding the index.

I can issue an easy query to do this:
```
exec sp_rename ‘PK__OrderDet__D3B9D30C7D677BB4’, ‘OrderDetail_PK’
```
When do this, I see the object is renamed.

This table has over a million rows, and while that’s not large, it does take time time to rebuild the index. With a rename, the change is to the metadata and takes a split second.

SQLNewBlogger

These quick, easy, administrator items are great to blog about. As an exercise, if this table has millions of rows, how much longer does the index rebuild take?

This is a great topic for you to write about (or learn about) and show how you can better administer your SQL Server databases.
July 21, 2016
ReadyRoll and Octopus Deploy Webinar

I’ll be hosting a webinar next week, Thursday, July 28, with Daniel Nolan (t) (founder of ReadyRoll and fellow Redgate employee) and Damian Brady (b | t) of Octopus Deploy.

We’ll be talking deployment, with demos to show you how to work with ReadyRoll for your deployment and then how to build a package to be automatically deployed though Octopus Deploy. Both are great products that I enjoy working with.

Register today if you’re interested in seeing how migration based development and deployment works with ReadyRoll and Octopus Deploy.

July 21, 2016
The Relentless Cloud

The march to the cloud is inevitable. No, I don’t think all your services will be run from a cloud service, but I do think that some of them will be. Maybe 10% for your company, maybe 90%, but likely somewhere in between. In the efforts to reduce costs, and maybe more likely, the idea of shifting costs to expenses, I would bet that most organiztions will take serious looks at the cloud in the next decade for a significant portion of all new services. They might not move most applications, but they’ll think about it.

I saw a note recently on Geekwire that Azure is estimated to exceed Amazon Web Services (AWS) by 2019. Most of us might not care about the race between these two tech giants to build large cloud infrastructures, but their capital expenditure is based on the idea that your organizations will want to use some of those services. They have good reasons to think this with surveys and conversations with many of their customers.

In the absence of changing tax policy in many nations, it also makes more sense to make move costs to expense, rather than capital expenditures. And that’s the way things are going, at least according to what CIOs are saying. More and more applications and services will be moving to the cloud in the next few years. Up to 30% from the current 14% of applications today. As I’ve seen more and more entreprises, public and private, moving mail and web services to outsourcing providers like O365, I also think we’ll see other applications moving.

I don’t think every application is suited to an outsourced area, but I do think there’s a case to be made for many of them, including databases. For those with privacy or security issues, cloud providers are working to mitigate the issues. Amazon has built separate data centers for government services, and I believe Azure is (or has) done so as well. The challenges that exist can be solved, and many of the risks or concerns will be mitigated, though you’ll still need to evaluate your situation on a case by case basis, which will change over time.

My recommendation: learn about the cloud. Really learn, don’t just assume you know. It will be good for your career.

Steve Jones

The Voice of the DBA Podcast

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

July 21, 2016
Growth or Control – #sqlpass
I’m disturbed.

I read an announcement from the PASS organization this week that bothers me. I am less upset with the content than the manner in which it was presented, though I am not pleased with either.

Making SQL Saturday Sustainable was released this week, with a few changes to the SQL Saturday franchise. You can read the piece, but there are two fundamental changes being made for 2017 and beyond:
- [PASS] will be reducing this to amount to $250 and targeting those cities/locations that really need the financial support to grow, rather than well-established events.
- [PASS] is implementing a new 600-mile radius and concurrent event timing restriction to reduce competition between individual SQLSaturday events in Canada/USA.
There are comments on these notes at the bottom of the announcement.

Heavy Handed and Ham Fisted

I’m not sure why, but it seems that no matter who has been elected to the PASS Board of Directors seems to believe that they must make decisions without communicating, collaborating, or consulting the community. Decisions made year after year, on various topics (Summit location, pre-con speaker criteria, code of conduct, org name change, etc.) are announced without and sort of discussion or communication with the community they serve.

We are a community of diverse and disparate individuals. We won’t agree on many topics as a group, but we can discuss and debate them. We can have open and transparent disagreement about how to handle situations. Ultimately the board must decide (after a vote), but I’d hope and expect, really, I demand, that they solicit views before making decisions. It’s ridiculous in this age of instant communication, of no-cost publishing, of email votes, to not propose changes, gather feedback, and then make a decision.

Doing otherwise is as disrespectful as it is heavy handed.

This is not specific to this decision, but rather the way the PASS BoD has operated across a decade. I will say that I don’t think the Board makes all bad decisions. But they have made some that could have been avoided with a little more collaboration with those affected, us, the voters.

The Decisions

What about these specific decisions? Well, I have a few thoughts.

I do think these decisions are intended to slow the growth of SQL Saturdays. There is something to be said for this, in that sustaining fewer events for the long term is easier to manage and may not require replacing Karla Landrum as an evangelist. This benefits vendors, including my employer, but it’s not great for attendees. There will be fewer chances to learn, and smaller events in places that will never grow to 200+ people will likely die. Or move to a Code Camp/different model, abandoning SQL Saturday. That makes me sad. This appears to be less about growth, and mostly about controlling fewer events.

First, on the financial aspect. Let me say I know there are events that raise well over $20k (I have no idea what the record is). I also know there are events that have only raised $2-3k. The low level might even be lower. It doesn’t matter what you think about event cost, some events don’t need $500 and there is a limit to what PASS can support. I think not committing to funds for every event is good. However, what I’d like is flexibility.

Don’t set a hard limit, and note that events could get up to $500, but PASS reserves the right to not provide any financial support.

Now, the 600mi radius for events. Well, it’s not just 600mi, but also 600mi + a weekend either way. Let’s see 600mi from a few cities. Rob Volk tweeted this image:

This is 600mi from Nashville and Salt Lake City. That means that most of the US couldn’t have an event in a month with these two, at least not if there were a holiday weekend. Certainly some large cities are still here (major TX cities, NY area, etc), but most of CA, the West, the Midwest are all cut off.

Know what cities are within 600mi of each other? Vancouver and Portland. These two cities have held events book-ending the PASS Summit to allow speakers from overseas (or US speakers/attendees) to go to one or both events around the Summit. That’s now cut off.

There are probably others, but I will say this. The hardest part of getting an event going is the venue. There are more than a few organizers and events that have worked out deals with schools or other events. Baton Rouge and Oklahoma City? One might need to move. Those markets have had events in the same month, within 600mi of each other.

I have always thought the 400mi limitation was ridiculous. Events can happen within 400mi if they can manage to find speakers and raise funds. Rather than arbitrarily limit events by distance, why not limit by viability, or by fundraising?

I love that we’ve grown to over 100 events in a year. I think it’s great, but I also think the competition and effort put into making large events is crazy. I don’t like 10+ tracks. I don’t like 30+ speakers. I don’t like $20k spends that include fancy dinners.

The aim here is to train people, teach them something, inspire them, and expose them to options for SQL Server, including vendors. I get that vendors struggle to justify going to so many events. That’s fine. Make choices. I suspect vendors will support larger events and smaller ones will struggle.

I’ll put down thoughts, but smaller event, just be smaller. Don’t buy dinner. Don’t make shirts. Don’t try to provide a great meal for attendees.

Teach them. That’s what the event is about. Stop there and you’ll be fine. Everything else is nice to have, but in no way necessary.

And PASS, please, stop believing you need to earn your position and make hard decisions as a small group. Work with the community, with proposals, not decrees.
July 20, 2016