Voice of the DBA

Tag: sql server

How do you learn the advanced stuff?
Advanced Study is hard, but worth it when you want to get better at something

I was talking with someone recently about features in SQL Server and they mentioned that partitioning was something DBAs should know. It’s only available in the Enterprise and Data Center editions for production use, but my friend noted that it works in Developer edition and felt there was no excuse for a DBA not being familiar with a feature that’s been out since SQL Server 2005, nearly four versions removed from its introduction.

I can understand that, but if you don’t have the ability to actually tune large data sets and see the impact of partitioning in a larger, production environment, it’s easy to dismiss this as a feature that doesn’t provide many benefits in a smaller situation. The same could be said for clustering, SSIS imports, or any number of features that aren’t often used. So how do you actually learn to get some experience with these features?

The first thing you need to do is get a copy of SQL Server Developer Edition and install it as a virtual machine on your primary computer. I use VMWare, but you could use Virtual PC, Hyper-V, or VirtualBox as well. The important thing to do is have a sandbox to play in and a machine you can easily copy, clone, or destroy as needed. I would recommend a base install of Windows and SQL Server DE, and then copy that for a machine you can experiment with.

One way to learn is duplicate the work that someone else has done. Make a copy of your virtual machine and then implement partitioning as described in an article. See if you can duplicate the way the author used the feature and get the same results. If you can’t, find out why, and if you can, experiment with the feature and try to improve the author’s implementation. The SQLServerCentral Stairway Series is a great way to do this in SSIS, SSRS, or other features that you may never have seen. Set aside an hour or two a week to learn some feature. After a month or two, you might be surprised what you’ve learned.

The most important is to understand how a feature works and gain some experience in using it. You might not become an expert, but being able to talk about the feature, and explain how you might use it in a situation comes from practice. That ability might get you the project or job that allows you to become an expert over time.

Steve Jones

The Voice of the DBA Podcasts
- Watch the Windows Media Podcast – 23.2MB WMV
- Watch the iPod Video Podcast – 16.6MB MP4
- Watch the MP3 Audio Podcast – 3.8MB MP3
October 4, 2011
SQL Authentication – Forcing Password Changes

When you create a SQL Server login (with SQL authentication), you have the option of enforcing password policies from Windows (in SQL Server 2005 and above).

The recommendation is that you check all three and force strong passwords. You also force a password change so the person has a private password not known by the administrator.

If you go back into this account later, and look at the boxes, only 2 are available to be checked. The “User must change password at next login” is grated out.

In order to access this box and force a password change, you need to change the password. The reason is that if the account is compromised, the hacker should not be the one to set a new password. The security model assumes the administrator can contact the legitimate owner offline and give them the new password.

Start typing in the password box, and you can check the box:

Of course you need to set a password that conforms to the policies, and it needs to match the confirm edit box

October 3, 2011
Recovery Models

In SQL Server we have three basic recovery models: full, bulk-logged, and simple. By default we find that most databases use the defaults, which mean that they are in the full recovery model.

In that case, you need to be sure that you are performing log backups, otherwise the log will grow until it reaches it’s limits, or you run out of disk space on that drive. If the log cannot record SQL Server transactions, the database cannot accept any more transactions.

The basics of recovery models are covered nicely in this article from Gail Shaw, which includes some common myths and misconceptions out there. However for the average person, the important thing is that you understand which recovery model to pick.

You Need Point in Time Recovery

Point in time recovery means recovery in between the full or differential backups. Quite a few DBAs will ask customers if they really need to recover to a point in time, and get the answer that they don’t, but that’s not often the right question to ask.

Ask your clients if the database failed at 5:00pm today, and all the work done today was lost because you restored to last night’s backup at midnight, how would they feel?

Sometimes they’re fine with the data loss, most times they aren’t. If you need to get back to a point in time between backups, make sure you use the full recovery model.

You Can Reload the Database

There are some databases, usually data warehouses, that can be rebuilt from other sources. If you take a backup of your database and then load data every day that rarely changes during the day, you might not need point in time recovery. In fact, many ETL processes are not designed for this anyway, and could not restart themselves in the middle of a load if you restored to the point in time when the database had an issue.

In this case, use the Simple recovery model.

You are space constrained with the log

If you run index rebuilds, or large data loads and find yourself with a transaction log that grows very large, you might want to investigate the Bulk-logged recovery model. This model is more confusing, so I don’t want to give you a general rule here. If you think you might benefit from less logging, investigate the bulk-logged recovery model, practice restores with it and make sure you fully understand the implications of using it before you set a database in this mode.

September 26, 2011
Map a Login – Basic Skill #3
This post is part of a series based on my presentation The Top Ten Skills You Need for SQL Server. This post is part of Skill #3 – Setup Security.

I wrote about the basic security model for SQL Server, and got a question about mapping users to logins. It actually is done automatically for you in the dialog when you create a login, but I thought I’d cover the basic process here in more detail.

Logins allow access to an instance of SQL Server. Users are the construct in a database that can be assigned permissions (explicitly or through a role). The mapping between a user and a login is what allows SQL Server to determine which logins get which rights.

Let’s look at an example. On one of my instance, I have a test login called “JoeLogin”. If I connect to the instance, I enter the credentials of “JoeLogin” and the password to connect.

Once I connect, however, the first thing the SQL Server database engine does is set my context to either the database I’ve specified, or my default database. This immediately maps me to a user in that database and allows me the connection to run commands, or it returns an error if I don’t have access.

In this case, I have access to my default database, and I can check on my user credentials with this code:
```
SELECT USER_NAME()
```
This returns “JoeUser”, which is my user name. You can read about user_name() here, but it is a system function that returns your current database user name.

My login has essentially been bypassed, and would only be used if I needed to check permissions to execute an instance level function, like setting a configuration value or viewing the error log. However I can check my login by using the SYSTEM_USER function.
```
SELECT SYSTEM_USER
```
This will return my login name, and it returns “JoeLogin” on my instance. I can easily see this in SSMS, in the right corner of the status bar at the bottom of the window.

The Mapping

We can view the mapping between users and logins in two ways. If you want to see where a user is mapped, you can right click the user and select properties.

This will bring up a dialog for the user, and at the top you can see the login mapped to this user:

If you want to see where a login is mapped, you can right click the login in the server level Security folder and select properties. This brings up the login dialog, and if you select the “User Mapping” item from the left pane, you will see the list of databases and the user mappings.

In this example, my JoeLogin has been mapped to two database. In db1, the default behavior is applied and the login is mapped to a user with the same name. In db4, I have changed the default and mapped to a user called “JoeUser”.

I haven’t run across a good reason to change the user name from the login name, and I don’t recommend it, but if you think you might have some issues, this is how you check things.
September 19, 2011