Voice of the DBA

Tag: syndicated

Career Ratings
I wrote the other day about measuring your career, and I gave some general advice, but I wanted to give you some thoughts to take away about how you might rate your career.

These are some suggested areas, with a note or two in there. I’m not saying any of these, and certainly not all of these, should be important to you. Or that they are important to me. Some of these matter, some don’t.

Pick and choose what matters to you and think about the relative importance. These are presented in no particular order.
- Salary – We need to pay the bills, but don’t let this overwhelm other areas. However also understand you can use this to get other benefits. If I make 10k more, but keep my budget the same, I could use that 10k to take unpaid time off, or pay for other things.
- Vacation days – time off matters. However, taking time off matters more. If you can’t, or don’t, use it, then does this matter? As I get older, I think of this more. One thing I love about Red Gate is the sabbatical. I really enjoyed it, and it makes me think about just taking time off again in a few years. If salary is high enough, you can create your own time off, unpaid, of course.
- Medical benefits – Again, as I get older, I think about this. The US is different than other places, so you might not care. However you might care about leave here with the birth of a child. Think about where you are.
- Education benefits – This mattered when I was younger, but for most of my career, I haven’t had time to use these, so they essentially aren’t benefits. However I have had friend get degrees under these programs. I’d be sure, however, this matches what matters to you. Some companies only reimburse if it’s a field of study related to your job.
- Retirement assistance/401k/matching/etc. – I hope you live a long time. Far, far too many of us don’t plan well for the future, when we may not work as much, we may make less, or just change our lives. What does your employer help you with here.
- Commute – Time is the most valuable resource I have as I get older. Spending time in the car just to move to a job is something I dislike. What does this mean for you?
- Equipment – Perhaps you want the perk of upgrading equipment regularly, or just having some control. Brent Ozar Unlimited offers this, does your employer? Mine does.
- On-call – I once worked in a company where we had 20 operations people. We shared on call, one week of 20. My week I got over 40 calls when the sun was on the other side of the world. That really, really sucked. Think about what on-call does to your life. Some companies offer compensation time when you work more. Some don’t. Think about what you consider to be fair.
- Side Projects – The famous Google 20% time, or the Red Gate Down Tools Week. Maybe you care.
- Travel – Maybe you like to travel, maybe you don’t. Earlier in life I didn’t. I’m better at it now and enjoy some of the experiences. Get a firm grasp of how you feel before you commit to traveling, or get stuck in an office.
- Kudos – Do you care if you get recognized for doing well? Most of the time I don’t, but if I never get a thank you or any acknowledgement of my efforts, it bothers me.
- Training/conferences – We need to improve ourselves in technology, no matter what. Do you want help? Will you get support or even time to improve your skills?
- Hours in the office – Does your company allow remote work? I’m not sure I need 100% remote (or my 98% now), but I’m not willing to work at 0% remote time. However I also have family commitments, so the core hours I need to be in a place matter.
- Flexibility of Schedule – Required for me. Some of you might want a rigid schedule you can plan around. Choose what matters to you.
- Promotion – What are your chances for advancement? How does the company review you and raise your salary or title? How do they move people into new positions?
- Google benefits – Large companies have more overhead in working there. Parking, commutes, lots of stuff eat up time. However many large companies try to help you here. Google has published quite a few of their benefits, but I’ve worked in large companies that offered massages on site, oil changes, child care, gyms, cafeterias, and more. Ask, or suggest, things here. These concierge services can be helpful in life.
- Charity – Some companies match donations, some give time off for volunteer efforts.
All of these items are things you should consider. They aren’t ranked here, but just listed. You might want these as benefits, or not want them. You might want more or less of them. It’s really up to you, but it’s important for you to determine how important each of these items is for your career.

The best advice I can give you here is to ask for what matters to you. Negotiate. You might not get what you want, but that shouldn’t discourage you, nor should you feel you’ve failed. Negotiation is give and take, with you and your employer compromising.

Whether you’re looking for a new position or want changes in your current arrangement, ask and discuss.
March 20, 2015
Measuring My Career

I’m going to do a shorter editorial on this, but really I think this deserves more treatment here.

I saw Brent Ozar write a post on measuring your career. In it, he talks about the fact that many people measure their career as a function of their salary. He points out that that’s a poor measure, as it’s really based on something in the past, not the current position or situation in which we are engaged.

I agree, plus I think salary is a poor way to measure success. It’s a crude, gross way to compare the value of one job to another, based on someone else’s view of your job. Your employer decides what constitutes a fair salary, which may have nothing to do with you, your work, the responsibilities, or the total work you accomplish.

Boiling It Down

Pegging the needle

I’ve been asked many questions in my life, often something like “how would you rate xx?” or “on a scale of 1-10, what do you think of yy?”

In all those cases, I’ve struggled to do much more than peg the needle in my rating. It’s all the way left, all the way right, or dead center.

The reality is that so much of our views of anything, whether it’s our career, our car, our laptop, or anything else can’t be boiled down to a single number. It’s silly to try, and I’ve given up. I’m not going to rate things along one axis when there are many ways to view anything, along different sets of criteria.

It’s one reason I really have started to discard all speaking feedback other than comments. The 1-5 scale means nothing to me. If you can’t express a positive or negative criticism or praise to me, then I don’t gain anything from the evaluation.

My Career

My career has been great. Not all an upward rise, nor all an unbroken series of successes. I’ve had setbacks, and at times, I’m a little disconcerted by the number of jobs I’ve had. However overall, my career is a success.

Why?

For one thing, I’ve been able to make a difference in many jobs. I’ve written software, a report, documentation, or produced something that was useful to others. They’ve told me it saved time, it saved money, or just that they appreciated it. That’s a success.

My income has gone up over the years, but it’s also gone down. If I were to look at my salary, it has a few nice dips in it. However the important thing is that at most every stage, it’s been enough money to take care of the needs of my family. Sometimes we’ve also needed my wife’s income, but that’s OK. The reality is that my salary doesn’t stand alone. It goes hand in hand with my wife’s and together the numbers have to make sense. I can make less if she makes more, or vice versa.

Or we fix our budget. That’s an area that some people don’t consider. If I can change my budget, take a lower salary at a position I like better for some reason, then it’s still a growth in my career.

I’ve also thought that my career flexed and changed to fit my life. When my kids were younger, I really valued having a job that kept me at home and limited travel. My current position at Red Gate wouldn’t have been a good fit then, but it is now, with teenagers and adult-aged children.

What’s Important?

Every job’s a job at some point.

If I were to measure my career right now, in the context of my current situation at home and my position with Red Gate, it’s the best job I’ve ever had and it’s a huge success as I view it. It’s not perfect, but it’s like my tractor.

When I first got a tractor for the ranch, I thought it was so cool. It’s a big, powerful piece of equipment and it gives you a sense of power to drive it around. I can move snow, pick up huge objects like hay bales that I could never move alone, and drill holes in the ground with an ease I never thought possible. Driving a tractor around to plow snow or cut grass is always cool.

For 15 minutes. Sometimes 5 minutes. Then it’s work. Often it’s tedious or really annoying to do something over and over. However I’m always thrilled when I first get going on the tractor.

I have this view of jobs. No matter how amazing the position is, whether it’s rock star, professional athlete, journalist, or DBA, at some point you’re going to have to do something you don’t enjoy or want to do. You’re going to have tedious moments. You’re going to get bored.

Hopefully you get energized with the next project, day, or city you tackle next in your job.

My job is hard. The travel wears me out at times. Video calls can be maddening when people are late, or they don’t speak into the microphone, or any of the things Scott Hanselman ranted about. The tedious nature of administering the site, or editing poorly written articles, and more can wear on me. The stress of putting out daily information tightens the muscles in my neck and fills my stomach with butterflies at times.

I put up with those things because I love writing, speaking, and teaching people about SQL Server. I like learning new things, I like working for a company that values me and gives me opportunities. I like working from home on a flexible schedule. I like the challenge of trying to help the community and improving other’s careers and success at work.

I guess that you have to start rating the importance of things in your career in the ways that are important to you. What matters in your life? When you find the position meets more of your needs than it doesn’t, when you have more positives than negatives, when you have more good days than bad, I think you’re succeeding.

Good luck, remember you get one life to live, so take advantage of it while you can, and always, always, work to live rather than living to work.

March 19, 2015
ALTER SCHEMA TO ADD PERMISSIONS
I’m sure some of you have wanted to do this:

ALTER SCHEMA Steve AUTHORIZATION Steve

You realize this doesn’t work, and you can’t grant the user Steve, rights to his schema after it’s created. You can do this:

CREATE SCHEMA Steve Authorization Steve

UPDATE: Someone pointed out this works after the fact:

ALTER AUTHORIZATION ON SCHEMA::Steve TO Steve

But not alter it. Strange and annoying. In my last post, I showed dropping and recreating the schema. That works well if you are beginning development, but not when you’re in the middle.

Let’s make this less confusing and see how we actually allow a developer to access a schema to create procedures (or other objects) when the schema exists.

First, let’s assume we want a developer, Steve, to be able to create procedures in the ETL schema. We have these conditions:
- The ETL schema exists
- The ETL schema is owned by another developer.
- The login and user, Steve, exists in this database with no permissions.
I want to now allow Steve to build the procedure ETL.MyProc.

Grant Permissions

The first thing I do is grant create procedure permissions to Steve.

CREATE LOGIN steve WITH PASSWORD = ‘Test’;
GO
USE Sandbox
GO
CREATE USER Steve FOR LOGIN Steve
GO
GRANT CREATE PROCEDURE to Steve;

GO

With this done, now let’s set up our schema.

CREATE SCHEMA ETL
GO
There are no default permissions, so the user Steve cannot create ETL.MyProc right now. How do we fix this?

The trick here is that I need to allow Steve to ALTER the schema. I can do this by using this statement.

GRANT ALTER ON SCHEMA::ETL TO Steve;
GO

I could do other things. I could grant CONTROL. to Steve instead, but I might not want to do that. That gives Steve the ability to actually drop the schema, which probably isn’t want. It’s certainly not the “least permissions” to let the developer create objects in a schema.
March 18, 2015
The Demo Setup–Attaching Databases with Powershell
I found another use for Powershell, one actually suggested by someone else: attaching specific SQL Server databases.

TL;DR I have a script that detaches all user databases from a SQL Server instance and then reattches certain ones. Full script at the end.

The Issue

We have a lot of demo databases on our demo VMs for Red Gate. Some specific databases are used to show things with different products, but it ends up with us having a few dozen databases on an instance of SQL Server.

That’s not the best way to show things to users, as they can get confused with so many databases. Specifically for us, we have a set of databases for one of our classes, a different set for a second class, and a third set for a third class. We do this because things need to be set in different stages for each class.

One of our sales engineers said it would be great if we could hide some databases when we didn’t need them. I immediately saw a use for Powershell here.

Approach

My approach to this problem would be this.
- detach all user databases
- attach specific databases by specifying the name of the database, and the mdf/ldf/ndf file names.
- use a batch file the user can double click on the desktop to run the Powershell script.
This seemed to make sense, and I started to tackle this on one of my machines in this manner. However because I detached all my databases first, all of a sudden working on things was a pain. As a result, I setup a new VM and created dummy databases there. I first worked on the attach piece, and then the detach part.

Detaching User Databases

This was fairly simple, and I’ve written about it before. In this case, I merely cut and pasted this code into my script.

$srv = New-Object ‘Microsoft.SqlServer.Management.SMO.Server’ $instance

#detach all user databases
$dbnames = $srv.Databases.name

foreach ($dbn in $dbnames) {
    Write-Host $dbn
    if ($dbn -ne "master" -and $dbn -ne "model" -and $dbn -ne "msdb" -and $dbn -ne "tempdb") {
      $srv.DetachDatabase($dbn, $false)

      }
    }

The first line is actually needed for both parts of the script, and we re-use that object later.

The script gets a handle to the databases object and then a collection of all the names. We loop through the collection and if we aren’t looking at one of the four system databases, we call the detachDatabase method.

Note that this means I’m in control of the instance and I know I don’t have a distribution database or anything else that might break. For me, I can safely drop everything other than master/model/msdb/tempdb.

Attaching Databases

I had to search around for some example code. I guess I didn’t have to, but the docs from MS can be tricky to put together, so I searched and found a few examples. Specifically, I ran across this post that described how to attach a single database.

I decided to begin by building up the db name and paths to the files. I started by setting a variable to the path and database name.

$sqldatapath = "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\"

$dbn = "sandbox"

One of my databases is “Sandbox” and the path for all my database files is given as the default.

Next I build up the mdf/ldf files. In my case, I don’t have anything other than single mdf file databases.

$mdffiles = $sqldatapath + $dbn + ".mdf"
write-host $mdffiles
$ldffiles = $sqldatapath + $dbn + "_Log.ldf"
write-host $ldffiles

With these, I now can tell what I’m doing. I write the data out to the host, mostly so that if something breaks, the user can determine where. We’re all technical, but it’s nice to know what’s broken.

These are the important bits, but now I need a place to store them. At only one time in the script, I create a new StringCollection object.

$dbfiles = New-Object System.Collections.Specialized.StringCollection

I’ll reuse this object for each database. In this object, I store the database file names. I use the .Add method to get them in here.

$dbfiles.Add($mdffiles)
$dbfiles.Add($ldffiles)

Now I have all my parameters. I can call the AttachDatabase method.

$srv.AttachDatabase($dbn, $dbfiles, "sa", "None")

The documentation says I need an owner, and for simplicity, I use “sa”. I also can specify options, but I don’t care in this case.

This attaches my first database. However, I need to repeat this. I could build some loop and use some array, which is probably better, but for the sake of simplicity here, and preventing issues, I copy and paste this code multiple times. In my case, I have no more than 4 databases, for any environment, so I merely copy/paste this code and change the database name.

However, I don’t want to keep adding to my StringCollection each time. In between each set of databases I need to call, I add this:

$dbfiles.Clear()

Now I have a few simple scripts I can modify easily, and others can understand them.

The Batch File

The other thing I learned with the batch file is that it doesn’t have the same context as my editing session. I had to add a line to load the SQLPS stuff at the beginning for it to work.

Import-Module "sqlps" -DisableNameChecking

I also had to ensure the execution policy is set on each machine, but we tend to do that when we set up the machines.

Simplicity

This is the simple way. It’s really not the best way, and if these scripts change much, this is a problematic way of doing things. I really should have a loop with a list of databases in one place in the script. That way if I add or remove a database, I can easily do it.

That’s an improvement I’ll make.

Let me also say that I have a pattern of database names, and files. If I needed to handle different file locations and varying numbers of files, I think this approach actually works better. Each section of the script can be edited easily, and separately, without worrying about complex logic.

I like simple.

Scripts

The batch script is this.

powershell c:\Utilities\attach_demodbs.ps1

I call the Powershell host and give a fully qualified path to the script.

Here is one of my demo scripts, for two databases: sandbox and EncryptionPrimer:

<#

Attach Demo Databases

This script detaches all user databases and then attaches the following databases

Attaches
– Sandbox
– EncryptionPrimer

#>

Import-Module "sqlps" -DisableNameChecking

$srv = New-Object ‘Microsoft.SqlServer.Management.SMO.Server’ $instance

#detach all user databases
$dbnames = $srv.Databases.name

foreach ($dbn in $dbnames) {
    Write-Host $dbn
    if ($dbn -ne "master" -and $dbn -ne "model" -and $dbn -ne "msdb" -and $dbn -ne "tempdb") {
      $srv.DetachDatabase($dbn, $false)

      }
    }

$dbfiles = New-Object System.Collections.Specialized.StringCollection

$sqldatapath = "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\"

$dbn = "sandbox"

write-host "Instance: " $srv.Name
write-host "Attach " $dbn

$mdffiles = $sqldatapath + $dbn + ".mdf"
write-host $mdffiles
$ldffiles = $sqldatapath + $dbn + "_Log.ldf"
write-host $ldffiles

$dbfiles.Add($mdffiles)
$dbfiles.Add($ldffiles)

$srv.AttachDatabase($dbn, $dbfiles, "sa", "None")

$dbfiles.Clear()

#attach staging
$dbn = "EncryptionPrimer"

write-host "Instance: " $srv.Name
write-host "Attach " $dbn

$mdffiles = $sqldatapath + $dbn + ".mdf"
write-host "MDF: " $mdffiles
$ldffiles = $sqldatapath + $dbn + "_Log.ldf"
write-host "LDF: " $ldffiles

$dbfiles.Add($mdffiles)
$dbfiles.Add($ldffiles)

$srv.AttachDatabase($dbn, $dbfiles, "sa", "None")

$dbfiles.Clear()
March 16, 2015