Voice of the DBA

Tag: syndicated

  • T-SQL Tuesday #46–The Rube Goldberg Machine

    tsqltuesdayIt’s T-SQL Tuesday time again. This is the monthly blog party where we all write on a topic. This month the topic is the Rube Goldberg Machine, as it might relate to SQL Server. You can read the invitation linked above from Rick Krueger and write your own post if you choose.

    Just be sure you post it on Tuesday, Sept 6, 2013.

    Catching the Cheaters

    A long, long time ago, in a county just to the west of here, I worked for a small company as a DBA. I had to support our production operations, as well as a team of 10-12 developers that were building our main company application. Since we were an ecommerce type company, this was an important application for the company.

    We were working in an high incremental way, probably similar to an Agile methodology, and deploying changes every week. However when I started the deployments weren’t smooth. Our developers were handling them, often bumbling them a bit, but able to fix things in an hour since they had done the development.

    Not a good separation of duties and it was becoming more of an issue as our boss wanted minutes of downtime, preferably single digits. I tracked down the main cause as a haphazard development style where changes were made to databases, instances, and IIS without much tracking going on. The IIS changes were easy: the developers had their rights removed from production and even test machines for configuration changes. However the database required a bit more work.

    As this was SQL Server 2000, we had limited ability to audit and manage rights. We also required our developers to have rights to the development machines in order to create test databases and work on importing data. We also wanted to ensure that developers moved quickly, but I needed a way to keep control of the instance.

    The first contraption I used was a simple one. I loaded the output of sp_configure into a table on the instance. I had created an administrative database that I used to track backups, and I added this information in there. I then created a job that would run sp_configure every day, compare the values to my table, and then alert me to changes. It would also update the table with the new, current values.

    This allowed me to catch various changes that developers were making when they tinkered with the server to “make it run faster”. I didn’t prevent changes, and if their changes worked, we’d deploy them to test (and eventually production), but this allowed us to document them and be aware.

    This worked well enough that I build a few more mousetraps. Catching schema changes is hard, as there isn’t good auditing in SQL Server 2000. However there is a “version” number for each object that is incremented when it’s changed. I built a similar audit/job system for our sysobjects table, but ran this every hour, catching changes. When I was alerted, which was more days than not, I’d email the team, track down the person changing things, and make a note for our current branch of work.

    This worked great in that development wasn’t slowed, but I was able to account for all development changes and slot them into the current, or future, development deployments. In a few months we reduced our deployment time from about an hour every Wed night to less than 5 minutes.

    Sometimes the mousetraps actually help the mice work better.

  • Be Careful in Business

    I like most of the SQL Server people I meet. Most of you are polite, cheerful, friendly, and it’s nice to share a few minutes of my life with you, chatting about anything. Whether that’s technical issues with SQL Server, running, books, or anything else, I often find my life richer for the time spent with others.

    However I wouldn’t go into business with most of you. In fact, the number of people I’d make a business partner can probably be counted on one hand.

    I wish it weren’t so, but I would choose my business partners very carefully, as carefully as choosing a mate. Getting into business is really like growing your family and all of your shareholders and partners are closely tied into your life. Since it’s not easy to get out of those types of relationships, you shouldn’t go into them too quickly.

    I was reminded of this when I read How to Screw a Friend out of an $800 million idea. Like The Accidental Billionaires/The Social Network, it’s a look at friendships and business, and how success caused problems. I don’t know if there is liability here, or a moral failing, but it does remind me that I should be careful in business.

    Just so you know, there are lots of you I’d work with. Easily thousands.

    That’s way less than the number of people I’d work for, which is likely a couple dozen. There are some good business people I’ve met who I think would be a good boss and who I would approach if I needed a job.

    Partners? I could name three off the top of my head and maybe three more I’d consider and want to get to know better.

  • I have a dream

    It seems fitting with the anniversary of Martin Luther King’s “I have a dream” speech that I would write about discrimination this week in my piece “Stunned”. There’s a long discussion over at SQLServerCentral, and along with a phone call with a friend, I thought a bit about what I wrote.

    I wasn’t present for the quote. I don’t know if there was nuance, or intent to joke, intent to annoy the listener, or something else. The written word doesn’t convey everything, and I decided not to dig deeper, identify the speaker, or take any actions as it’s all hearsay to me.

    However, I do think something along the lines of what you read, was said. I think there are still people that think this way, just as there are people that think various minorities are not competent, trustworthy, or something else.

    I have a dream that we will get beyond this as anything other than a rare occurrence. I don’t believe we will ever eliminate racism, or sexism, or really any type of discrimination. But perhaps we can reduce it to a very, very tiny level, get more people to think about there inherent prejudices and work to overcome them.

    I’m prejudiced in some ways. I recognize that and try to not let it affect my actions. I try to treat people fairly, and professionally. I try to treat them as I’d expect they will treat me. I want to accept them as they are, work with them with their strengths and weaknesses, and try to ensure I make an effort to increase diversity in my life.

    My intent today was to raise awareness. I wanted people to think about the actions, to talk about issues, and recognize that there are still problems in the world, including our tiny SQL Server, data professional world, that we should deal with.

    My hope is that we work to ensure this type of attitude is not tolerated. I hope for a frank discussion at user groups that note discrimination is not a part of the group. I hope that both minority groups (females, races, etc) and event/group leaders make efforts to include more disparate people. I hope more women and others make an effort to join in the community and share their knowledge. I hope more leaders choose to include more diverse speakers. If you have two people that are qualified, make the choice for diversity some of the time, rather than perhaps the more well known or safe choice.

    I hope we grow closer, not further apart, as a community.

  • Backup Your Certificate for TDE

    If you’ve enabled TDE, you need to be sure you have a copy of the certificate that protects the Database Encryption Key (DEK). If you follow my instructions, then you have one.

    If you didn’t make a backup, or you have just discovered a TDE database, make one now, and secure the password you use with your DR materials (off site).

    How do you make a backup? That’s easy. Use the BACKUP CERTIFICATE command. Here’s the command I use in demos:

    USE master
;
go
BACKUP CERTIFICATE TDEPRimer_CertSecurity
 TO FILE = 'tdeprimer_cert'
  WITH PRIVATE KEY (
               FILE = 'tdeprimer_cert.pvk',
               ENCRYPTION BY PASSWORD = 'AStr0ngB@ckUpP@ssw0rd4TDEcERT%')
;
go

    

    

    

     
    

    The certificate for TDE is in master, so you must make sure you’re in master for the backup. The TO FILE option lets you choose the file path. By default, this will be in the DATA folder for your instance, but you can choose other locations. You can give an extension if you like. This file is the certificate (public).
    

    There is a private key portion of the certificate, which is backed up with the “WITH PRIVATE KEY” portion of the command. This is where you specify the password and provide the protection for your certificate.
    

    You will need this password on restore, so keep track of it.