MS SQL

MS Certification Test

I have scheduled my first certification test for late October. I think that by putting the date on the calendar it will push me to finish my study more quickly. I have not been as vigilent as I would have liked to be so I am lighting the fire so to speak. Also with my new position I think that I will have some big SQL Server Projects down the road pretty quickly. That is a bigger fire for me but all the extra motivation is good for me. I have learned a lot over the last 8 months or so that I have been doing DBA work with Sybase, Oracle, and SQL Server but I still feel like I have so much to learn. Now I will be focusing more on SQL Server so I am really going to start getting my hands dirty on some fun big national projects. I am very excited to be in the mix more than I have been to this point. I am sure that I will have some good stories for you very soon.
Anyway I do not have much time now to get ready for that first test. I think that I am pretty much ready but I want to study more and play in SQL Server more before taking the test. I just do better once the pressure is on. Wish me luck and I will keep you in the loop.

Moving System Databases in MSSQL2005

Recently I had to move all system databases for one server to another physical drive. This is pretty simple for all but the Master and SystemRecource databases. For all others you can just do the following and then copy the mdf and ldf files to the new location.

alter database tempdb

Modify File (Name = tempdev, Filename = ‘c:\tempdb\tempdb.mdf’);

go

alter database tempdb

Modify File (Name = templog, Filename = ‘c:\tempdb\templog.ldf’);

go

Of course with tempdb you do not need to move the mdf and ldf files and it will be recreated in the new location when the server restarts.

So for Master it is a bit different. First I would double check the current location of the master files by doing the following.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(’master’)

GO

This will give you the current location of the Master Database. Now that we know that lets move it. For master you will have to open up the SQL Server Configuration Manager utility. Highlight the SQL server 2005 Services node on the left and then find your server service on the right. Right click on it and go to Properties. Go to the Advcanced Tab and look for the Startup Options parameter. In here you will see something like the following.

-dC:\tempdb\master.mdf;-eC:\tempdb\LOG\ERRORLOG;-lC:\tempdb\mastlog.ldf

You will notice the -d -e and -l, you must leave all of those switches in the line. They are telling SQL Server the location of the Data, ErrorLog, and Log files. Then modify the path to the path that you would like it to be now. Then click apply and ok to close out of the configuration Manager. Now stop the SQL Server Service and copy the MDF, LDF files and make sure that the folder structure to the ErrorLog file is available as well as check the security and make sure that the account that runs your SQL Server has full access to this new locataion. Once that is all done pull up a command prompt so we can start the SQL Server in Single User mode.

Net Start MSSQL$Instancename /f /T3608 (for named instance)

Net Start MSSQLSERVER /f /T3608 (for default instance)

Once that starts up then do the following to move the systemresource database as well.

ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= ‘D:\SqlData\mssqlsystemresource.mdf’);
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= ‘ D:\SqlData\mssqlsystemresource.ldf’);

Go

now go back to the command prompt and stop the sql server by doing:

Net stop MSSQL$InstanceName (for named instance)

Net Stop MSSQLSERVER (for default instance)

Now mive the systemresource mdf and ldf files to the new location and lets start it up in single user mode again like we did above.

Now do the following:

ALTER DATABASE mssqlsystemresource SET READ_ONLY;

and now stop the service again. Now we are ready to start it up normally and if all went and goes well you should be all set. Not to big of a deal but be very careful when doing this. Make sure you have a backup of Master and make sure that you backup the original startup options just in case you need to go back. Safety first here. You do not want what should be a simple task to be a horrific event.

Thanks and let me know if you have any questions or comments.

Questions about health check script

I recieved a comment that contained a question about my previous post about writing a script to output a health check of SQL Server.

 http://itknowledgeexchange.techtarget.co…

The following is the question:

should we make anychanges for this do and what are the fields i need to provide at this columns: #Read thru the contents of the SQL_Servers.txt file
#$servers = Import-Csv “\pni-vmdbasqld$monitoringinputssqltab.txt”
$servers = Import-Csv “\ent-pocpacapcx01d$monitoringsqltab.txt”

So You should make changes to the $servers variable and point it to the input file that you are going to be using. My csv file has the following columns in it and you can adjust to yours to make it fit your needs.

Monitor,Server,Instance,TorP,ErrorLog,Ping,OS2000

Those are the things that I have found that I use in multiple scripts to deal with SQL Server. The Monitor is jsut a Y or N value to tell my monitoring scripts if I want to monitor that server at this time. Then the physical host name is the Server value, instance is NULL if default and the name of the instance if named, TorP is T if the server is Test and P if Prod, ErrorLog is the location of the errorlog on the filesystem, Ping is also a Y or N value used by my Ping Servers Script to tell it if I want it to be pinged, and OS200 tells my scripts if the server is running Server 2000 or not. That is important since OS2000 can not have Powershell installed.

Hope that helps and if you have any other questions or comments please let me know.

MSSQL Server Renaming Physical Host

Recently I had a server that hosts a MSSQL 2005 SQL Server named instance and the Windows group decided that the name of the server had to be changed. I thought that this may cuase an issue since the MSSQL Server name is ‘hostname\instancename’. I did a bit of research and found that a solution is available for this problem. At first I thought that I may have to reinstall a new instance of SQL Server in order to get the name correct. I did not want to go thru all of that though.

I found that MS has a couple of Stored Procs that will help with this problem.

sp_dropserver ‘hostname\instancename’ –of the old server\instancename

sp_addserver ‘hostname\instancename’, ‘local’ –of the new server\instancename

I found that when I did a select @@servername I still got the old server name returned. I also found that when I did a sp_helpserver I got the new name. I decided I would restart the server to see if that would update the @@servername variable. I right clicked on the server and said restart. After it cam back up I got a ‘NULL’ from select @@servername. I did not understand why and really still do not. I was able to resolve the issue by going to the services.msc and restarting the SQL Server Service from there. Now I see the new server name from select @@servername as well as sp_helpserver.

SQL Server Certs

I have been talking about what I would like to do to further my education and my carreer as a DBA. I started reading the books in order to study for my MCITP DBA certification and the other members of my team decided that they would like to do the same. They both ordered the books as well and now we are going to attempt to get the certification as a team. We just started working as a group on this and we are doing a 3 hour study session per week and trying to get three chapters of the book done per week. We read the chapters on our own and then we can work the problems, case scenarios, and the examples in the book together. This is great since if any one of us has any questions we can talk about it and make sure that we all understand the concepts.

I think I will gain the most out of this arrangement since I am the new guy still and they have a lot more experiance than I do. I recommend trying to do this type of thing in a group for a couple of reasons.

1. For me this will really push me forward and I will be more driven to get it all done

2. I work better in a group and if I have others that understand concepts I learn best from them.

Error while installing SQL Server 2005 SP 3

I have installed SP3 on a few SQL Servers with no issue at all. The other day I go to install it on a production instance and it fails with the following error:

A Recently Applied Update, KB955706, failed to install.

The update failed with that error for the following services:

Database Services

Notification Services

Integration Services

Client Components

I then went and looked into the log files for each that failed and I noticed some interesting things. Here are the highlights:

Warning: Local cached package ‘C:\WINDOWS\Installer\8510ea.msi’ is missing.

Couldn’t find local patch ‘C:\WINDOWS\Installer\549bc1bf.msp’. Looking for it at its source.

MainEngineThread is returning 1635

This patch package could not be opened. Verify that the patch package exists and that you can access it, or contact the application vendor to verify that this is a valid Windows Installer patch package.

D:\SQL Installer\Disk 1\Setup\SqlRun_SQL.msi

So I started googleing like crazy and I was just about ready to call MS to open a case when I found the best writeup ever on this problem.

http://blogs.msdn.com/sqlserverfaq/archive/2009/01/30/part-1-sql-server-2005-patch-fails-to-install-with-an-error-unable-to-install-windows-installer-msp-file.aspx

I followed the instructions here on a clone of the server and everything worked perfect. Now I just need to make sure that no one ever touches the c:\Windows\Installer directory when trying to free up disk space.

SQL Version Script

I needed a quick audit of some of the SQL Servers in my environment. We wanted to know what version and what SP each instance was running. I threw this together and it is pretty simple. It only checks for SQL Server 2000 SP4 and SQL Server 2005 RTM thru SP3.

New-PSDrive
i
-PSProvider
filesystem
-Root
Path
to
where
input
file
is

Clear-Content
“Path to results file”

$OUTFILE
=
path
to
outfile

$servers
=
Import-Csv
-Path
“i:\sqltab.txt”

$sqlquery1
=
“select @@version”

$sqlcatalog
=
“master”

foreach ($server
in
$servers)

{

$machine
=
$server.server

$instance
=
$server.instance

$torp
=
$server.torp

$ping
=
$server.ping

if ($instance
-eq
“NULL”)

{

$SqlServer
=
$server.server

$folder
=
$server.server

}

else

{

$SqlServer
=
“$machine\$instance”

$folder
=
“$machine-$instance”

}

$sqlcommandpath
=
“c:\Program Files\Microsoft SQL Server\90\Tools\binn\”

cd
$sqlcommandpath

$version
= ./sqlcmd.exe
-Q
“`”$sqlquery1`” -S $SqlServer -d $sqlcatalog -Y 255″

if ($version
-like
“*1399*”)

{$sqlversion
=
“SQL Server 2005 No Service Pack”}

if ($version
-like
“*2047*”)

{$sqlversion
=
“SQL Server 2005 SP1″}

if ($version
-like
“*3042*”)

{$sqlversion
=
“SQL Server 2005 SP2″}

if ($version
-like
“*4035*”)

{$sqlversion
=
“SQL Server 2005 SP3″}

if ($version
-like
“*2050*”)

{$sqlversion
=
“SQL Server 2000 SP4″}

echo
“`n######################################################################” >> $OUTFILE

echo
”         Server = $SqlServer                                ” >> $OUTFILE

echo
“            $sqlversion                                     ”>> $OUTFILE

echo
“######################################################################`n” >> $OUTFILE

If you have read my other posts then you know that sqltab is a csv file with all the information about my servers. If you have any questions about this or anything else head over to
http://sysadminsmith.com
and click on ‘Submit a Question’

The Security Battle

I just had a battle with some applications support team members about securing our data. We have a system that connects to a SQL Server backend, this system has been in place for over a year now and is about to get a major application update. When this system was first implemented management let security go by the wayside and did not really care what we did as long as users were able to do what they needed to do and fast. Because of this management mandate the system was left essentially wide open to the world. This has been an annoyance to us DBA’s as we do not want to be responsible for any issues that may arise that are due to the concerns that we voiced when this was put in place.

Since a big upgrade is about to occur I have again brought up all the concerns that we have and everyone again agreed that we need to make these changes but tried to give us the run around again. They say things like it is changing to much at once. We will not know if we need to look at the application or the Database Security changes if we have an issue. To be honest all I hear is “blah blah blah”. I was involved in the initial set up of all this but at that time I was not a DBA and had little understanding of the issues that can arise with the lack of security that was in place. Now that I am a DBA and I do have a much better understanding I am unwilling to move forward without making the changes. I talked to the applications support team manager and expressed my concern and told him that I do not think this is something we can do without. The concern of what to look at when a problem arises should not be an issue as security should be the number one priority when setting up a system that contains sensitive data.

I finally got my point across and I have them on board for now. I think that I have learned that no matter who says what, I am responsible for this data being safe and sound and ready for users to use. I will not take the risk of losing this data for any preventable reason. If you are new to the DBA world just know that your data is your data and you need to do whatever possible to keep it safe and confidential. Do not give up on that battle. You can win and if you do not then I would update resume and get ready for when your data gets stolen or corrupt because you did not stand your ground on the issue of security.