Blog Archives
Best Practicing, the Results Part I
Yesterday, I showed you how to install the Best Practices Analyzer and use it through the Baseline Configuration Analyzer. Even after your servers are setup for best practices, it is good to run the BPA from time to time to look for changes, especially if you have more than one DBA working on your servers. Here is a great example of its use: we purchased a new piece of software that needed to setup its database during the install. No problem, we do this quite often. After the install, two databases were created. Everything looked fine on the surface.
Not satisfied with everything looking fine, I ran the BPA and got the following results:
That’s right, the vendor tried to sneak in the Auto Shrink option. Now I can quickly remedy this situation and sleep easy tonight knowing that my SQL Server is yet again unblemished, for the most part.
Enjoy!
Best Practicing? Here’s How!
Are you trying to conform your systems to Microsoft standard best practices? Have you used Microsoft’s tools for automating this?
The first item you will need is the Microsoft Baseline Configuration Analyzer 2.0 (download). It will help you to maintain optimal system configuration by analyzing against the predefined set of best practices which we will download next. The Microsoft SQL Server 2008 R2 Best Practices Analyzer (download) is a component that runs within the MBCA. In addition, you will need PowerShell v2.0 installed on the server. This server can run against other SQL Servers so it does not need to be installed on your SQL Server.
Open the Microsoft Baseline Configuration Analyser and select SQL Server 2008 R2 BPA from the drop down as shown here. However, the first time you run the MBCA, you will have to type in the host and instance information. As you can see from my screenshot, I have already run this program at least once and now I can simply click once and scan it again. You can also choose Enter Parameters to view this screen, as shown below. Here it will let you choose which specific 
aspects of SQL Server that you would like to compare against the Best Practices for 2008 R2.
From this point you are ready to proceed with the scan and then view the results and recommendations presented after the analyzer compares against your particular SQL Server instance. Enjoy!
Logon Triggers aka How To Lock That Annoying User Out During Lunch
Earlier this week, I chose a few SQL Server 2008 topics for our #SQLHelp MCITP study group to discuss here on the blog. Thus, today I will discuss logon triggers, one of the new features available in SQL Server 2008 (well actually if I remember correctly they were snuck in 2005 SP2…shhhh). Logon triggers are a special form of trigger that the database administrator can use in order to monitor logon events.
Logon triggers can be used for the following purposes:
- To audit and control server sessions (such as tracking logon activity)
- Limiting the number of sessions allowed for a particular logon
- Restricting logons to SQL Server (similar to AD snap-in that lets you set the hours that a user may logon)
Here is an example from books online showing how to set up a logon trigger that limits the number of concurrent sessions for a particular logon, logon_test in this case. This example, in particular, will limit the concurrent sessions to three. You must create this logon in order to test this code, or change the logon name to match your requirements.
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS ‘logon_test’
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= ‘logon_test’ AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = ‘logon_test’) > 3
ROLLBACK;
END;
Here is some sample code for restricting the user based on logon time (between 12Pm and 1pm in this example for all users except the SA\SQLAdministrator):
CREATE TRIGGER Restrict_Logon_HoursON ALL SERVERFOR LOGONASBEGINIF (DATEPART(HOUR, GETDATE()) BETWEEN 12 AND 13) AND(SUSER_SNAME() != ‘AD\SQLAdministrator’)BEGINROLLBACK;
END;END;
Once you have created logon triggers, you can view them by a simple query to the sys.server_triggers metadata.
Zest for Learning
In today’s competitive marketplace, we must have a zest for learning to remain relevant and marketable. While I am excited about the release of SQL Server 2012, its launch means that my 2008 MCTS has just become a little less marketable. The certification itself does not expire but it’s usefulness is definitely dated. I know many other DBAs have been testing and learning Denali for some time now. Time to pick up the slack.
Since my agency is in the process of implementing 2008R2 boxes replacing our 2005 boxes, we are in the forefront of available technology today. However, in a few months we will once again be one version behind. I can live with that, but my learning must continue. I have been planning on setting up a 2012 lab where I can be ready for the day that we migrate these new boxes.
The reality is that we must keep learning, we must keep moving, and we must remain relevant. Keep up the good work and enjoy!
Microsoft RAP, Not Just Clever Lyrics
We scheduled a Microsoft RAP, or Risk and Health Assessment Program, with our Premier Support representative to occur this week. This is an excellent program where a Microsoft Premier Field Engineer comes on site and assesses the risk and health of your SQL servers. We installed some new hardware and began a migration process moving from 2005 to 2008R2, so we thought this would be an excellent opportunity to assess our new environment as well to look at our best practices. Additionally, one of my goals for the assessment has been to put some weight behind my best practice ideas as well as some weight in order to change some practices by our developers. This is also an excellent opportunity for some of junior staff members to talk one on one with the field engineer to discuss some issues we have been working through as well as some of our pain points.
I will blog next week about some of our more interesting findings. Enjoy!
MCITP…Should I do it?
I have been contemplating my next move for SQL certification. Last spring, I earned my MCTS for SQL Server 2008 Implementaion and Maintenance passing the 70-432 exam after a couple of months of preparation and a TechSherpas course. The next logical step is the 70-450 which is Designing, Optimizing and Maintaining a Database Administrative Solution using Microsoft SQL Server 2008. The title alone is impressive. I think with this budget year I will have the opportunity to take a bootcamp for this certification.
Should I wait before pursuing this level? Is it recommended to have more experience before taking this exam? What do you think?
Migrating SQL Server Database Logins with sp_help_revlogin
You’ve migrated your database to a new box, with no problems. You created the login that goes with this database, and all is right in the world. However, when your user goes to connect to it they get the dreaded error 18456, login failed. But wait, I created the login, right?
Not so fast there, accidental DBA. You are going to need the help of a nifty stored procedure called sp_help_revlogin. This stored procedure, provided by Microsoft in the link below, will generate a T-SQL script that will copy the logins, their passwords and their corresponding SIDs (or security identification number, which is usually the culprit here). This is also a great utility when the database has numerous users with various security levels and passwords. Really now, who wants to write all of that down and recreate it? Even with screenshots, it can turn into a large, fat-fingering and time consuming event. Mind-numbing, I would say. Why do something manually when you can do it automagically? Just think of all those happy logins that will not be orphaned by the dark side?
Here is also a code snippet to check for orphaned logins, for your enjoyment. Enjoy!
USE <database_name>; GO sp_change_users_login @Action='Report'; GO
Moving the Master Database Lesson Learned
With the new 2008R2 servers that were recently built, I am hoping to institute some standard best practices on the boxes moving forward. The first one was to move the TempDB to its own drive on a separate LUN and since I have moved a TempDB before, no problems here. The second best practice was to make a separate drive on a separate LUN for the databases and log files, check and check. The third practice was to move the system databases, which sounded simple in theory but I have never done it before, uncheck–it did not work.
I followed Microsoft’s directions but it did not cover my contingency. The following excerpt is taken from Microsoft’s MSDN site for SQL Server 2008 R2 in order to Move the Master Database, with changes for my separate drives.
To move the master database, follow these steps.
- From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.
- In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.
- In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.
- Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK. Moving the error log file is optional.The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data and log files.
-J:\SQL\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\
LOG\ERRORLOG;-lJ:\SQL\mastlog.ldf- Stop the instance of SQL Server by right-clicking the instance name and choosing Stop.
- Move the master.mdf and mastlog.ldf files to the new location.
- Restart the instance of SQL Server. Here is where I had my issue, it would not restart. Through my troubleshooting I realized that the new folder did not have the same permissions as the original folder. I added the SQLServerMSSQLUser$<machine name>$<instance name> user that was created by the SQL installer. Problem solved.
- Verify the file change for the master database by running the following query.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(‘master’);
GOVerify the file change
Enjoy! I hope this helps!
SQL Swampland 