Voice of the DBA

Category: Blog

Azure SQL Database – Link User to Login
I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

I created a login for an Azure SQL Database, but couldn’t log in. I would get this message, which makes perfect sense.

Just like an on-premises instance, I need to link a user to the login for access. However, unlike an on-premises SQL Server, I can’t willy-nilly change databases. If I do this:

Use SQLServerCentral GO

I’ll get this message.

I can’t switch, I need to reconnect. In this case, I use the same login (an admin level) and reconnect to a different database. This certainly can make scripts more cumbersome, and imply that your work, whether through T-SQL or PoSh, needs to include the connection strings for the correct database. In fact, I might recommend PoSh over SSMS for this reason.

Once I’ve connected, I can use standard commands. In this case, I’ll use this code:

— add user to SQLServerCentral database — connect to database CREATE USER sscdev FROM LOGIN sscdev; — add a role for ddl admin EXEC sp_addrolemember ‘db_ddladmin‘, ‘sscdev‘;

Now when I click connect with my user, I see this:

Just what I need.

SQLNewBlogger

This was a second post as part of the previous one. I was adding a user and login as part of a bit of work and when it didn’t work, I captured screen shots and split this part from the last one.

Five minutes writing this one, probably no more than five minutes slower in running the code to capture the images.

You can do this.

References
- Database-Level Roles – https://msdn.microsoft.com/en-us/library/ms189121.aspx
July 20, 2015
Information on the Go
I have been very interested in smart watches over the last couple years. I like wearing a watch that allows me to glance at it for the current time. While I can pull out my phone, and I’ve done that for little over a year, I preferred a watch. There are plenty of times that my phone isn’t easily pulled out.

After considering lots of watches out there, I eventually settled on a Pebble, which I wrote about after living with it for a few weeks.

Since that time, I’ve continued to use the watch and have enjoyed getting data on the go. Today I was traveling and getting a flight notification (priority email) through the watch as I was traveling through the airport was nice. I didn’t have to pull out the phone, and in fact, because it was in a low pocket, I didn’t feel it vibrate. Not that I would have had issues today, but there are times it would have been handy.

One the plane, I saw someone with an Apple Watch. In fact, I’ve been seeing them more and more. My wife had asked me if I wanted one for my birthday this year. I like Apple products, and it was tempting. However the more I’ve looked at the product and read about it, the more I agree with Troy Hunt. It’s beautiful and awesome. And pointless.

I want simple, quick information on the go. For me, that boils down to a few things I’m looking to learn without finding my phone while I’m walking, running, driving, or even talking with friends:
- time
- text messages – This is so I can decide if I want to, or need to, respond.
- high priority emails – In the iPhone, these are my “VIP” people.
- music – current song, play/pause, skip/restart
- pace and exercise time
The last item is really nice. If I start a workout on the Map My Run app, it starts the display on my wrist

There’s not much more I want to appear on my wrist. Having this track my steps would be nice, but I haven’t seen a way to track this in the background, which is what I would need to get rid of my flex.

Responding to texts might be nice, but I can’t see how that would work smoothly, nor do I want to use speech. I don’t want to answer the phone on it, ala Dick Tracy, nor am I looking to play games.

Really, I’m looking for a little bit of data. The Pebble seems to be the best choice for me, with a long battery life, flexibility to control notifications from the phone, and get a bit of information in an easy to consume fashion.

Your needs might be different, and I’m sure some people would prefer a better device that does more, perhaps with GPS or other functions, but for now I haven’t seen a better device for me.
July 17, 2015
Looking Back at a Sabbatical – One Year Later

Last year at this time I was nearing the end of my sabbatical. I’ve been using the Timehop app on my phone to look back at the past and I’ve noticed the tweets and pictures that I had from last year popping up. I decided it was a good time to look back at a year in the past.

Here was one of my projects, and the one I was most proud of: a flagpole made of wood.

If you’re interested, you can read the journey I went on.

Looking Back

It’s been a year since I had six weeks off to do whatever. I think I had some memorable experiences from the time off, but I wanted to first look at how I feel this year, with no long set of time away from work scheduled.

First, I don’t miss the sabbatical. I didn’t develop this craving to not work any longer, or work less. I didn’t think I needed to change my life around. In hindsight, it felt like a summer vacation between semesters in college. A fun time, and a break between the more serious work of building a career.

That’s how I see it now. I’d like to do it again, and I’m not sure I’ll wait five years to do it again. I’ll have to see how our family finances go, and probably wouldn’t self-finance 6 weeks, but I am thinking to take a week or two again, separate from vacation, and just get away from work. However, like my sabbatical, I’ll want a plan of something I do that’s different than sitting around or vacationing. I’ll be looking for some self-improvement.

Growth

What did last year teach me, or how did I grow? Those are good questions. I’m a little torn on how to answer them. Let me tackle the teaching first, since that’s easier.

I learned quite a few things about building, Habitat for Humanity, and woodworking by tackling projects. Those were easy skills, and things I picked up. I have more work to go and more to learn, but I’ve documented some of that stuff. The other thing I learned is that I can still learn and grow and make an effort to improve my life in numerous ways.

I also learned some confidence. By stepping back, tackling some physical projects that might have intimidated me a bit prior to last summer, I know I can do more. This has helped me in having a bit more confidence to tackle other projects, both in an outside of, work in the last year.

In terms of growth, I had the chance to reflect and appreciate what a great life I have, and what a great company I work for. Both of those definitely helped to recharge me and have made it a bit easier to manage life and travel in the last year. I’m a bit more mature in how I watch my schedule.

Perhaps the biggest thing I learned is that time truly is my most precious asset. It is hard to set aside time for large projects, and I have a few half finished ones because I can’t get to them with a busy family life. In some sense I’m willing to let them go for now because I’m busy, but it made me appreciate the time I had last year to just methodically plod along. Even on weekends it’s hard to find that now.

The Future

Andy Warren has always said that we don’t get enough thinking time at work. Many of us are so busy moving from project to project, from work item to work item, that we fail to stop and consider the bigger picture. I doubted how much value there was in that, but the sabbatical helped me re-consider my thoughts.

I have a clearer picture of the value of being able to step aside for a day or so and just think about things. I still find it hard to find the time, but when I can spare an hour or two and think about SQLServerCentral, or how Redgate works, or how some part of the world could be better, I appreciate that more.

Finding this time also has me considering self-financing a shorter sabbatical sometime in the next year. At some point I want to be able to take a few days to myself, thinking about life, and career, and how to improve them for me.

July 14, 2015
No Defaults – T-SQL Tuesday #68

It’s the second Tuesday of the month, and time for T-SQL Tuesday. This month’s invitation is from Andy Yun, where he asks you to Just Say No to Defaults.

This is the monthly blog party started by Adam Machanic, and it’s the chance for you to write on a particular topic every month. The hosts rotate, so you have to watch the #tsql2sday hashtag for the topic. Posts for the month need to go live during the day according to UTC time.

I also keep a list of topics on the blog here, and you should feel free to write about any past topics and post something on your blog. It’s great practice, and a good way to get started as a #SQLNewBlogger.

Default Changes

There are all sorts of defaults in SQL Server. The setup program presents you with a number of choices, but in most cases a default exists for setting because SQL Server needs something.

I used to have a setup script that I would run for every new install. In a few jobs, back when we used to have physical servers, a hardware person or sysadmin would install Windows and SQL Server onto a new computer and then send me the name for customization. My script, which was really a series of SQLCMD calls in a batch file that in turn called various other scripts, was designed to add an administrative database, create some jobs to track the system, setup backups, and more.

The process really did part of what Policy Based Management can do, but was simpler and tailored to ensure that all of our SQL Servers worked in a similar manner. We could override settings, but this quick script gave us a starting point that all DBAs understood. We even ran this on development machines for instances we didn’t manage as it allowed us to troubleshoot other issues, it only took a few minutes, and it removed some of the management headaches from the developers’ minds.

However, there is one thing I’ve almost always changed on my instances. I try to do it during setup, but at times I need to do it later. That setting is the default location for files. I do this as I want to usually have data files, log files, and backup files separate from each other.

Even if I don’t have different drives, but setting up separate locations here now, I can easily move the files later and make one change here for the defaults and I know I’ll have things separate.

I’m not running a new install this week, but I’ll show you how to change it on an instance that’s installed. First, right click the instance in Object Explorer and click Properties.

Next, go to the Database Settings section.

At the bottom here you see locations for data, log, and backup. In this case, on my laptop, I only have two drives, so I can’t achieve great separation.

However in any production system, I’d have the data and logs separated to different physical drives, or at least different LUNs. Backups might go with logs, but they’d ideally be separated to another location.

July 14, 2015