MSSQL Administration

Active, Active, Active

Last week I got the chance to work on an Active, Active, Active SQL Server 2008 Cluster install. This was new to me and very fun. We have three physical hosts all using SQL Server 2008 Enterprise Edition. The Servers are also running Windows Server 2008 Enterprise Edition. I worked with one of our windows admins to do the install. First the windows admin set up the windows cluster and assigned all of the storage to the cluster but did not assign it to an application or service. Once that was done we had to set up the Distributed Transaction Coordinator and give it its storage. Now we started the install of SQL Server 2008. Since all of the storage was owned by one server we installed all three instances, one for each node, of SQL Server on that server and that created the different cluster applications for us. Then on each of the other servers we added a node three times, again once for each instance. Now that is complete we can fail over the resources for any instance to any of the physical nodes. Very cool.

Databases not accessible

Just got a call that three databases on an instance where many reside were not accessible. Users were getting errors about not enough disk, memory, or the files were inaccessible. I logged into the server and found that the databases were not offline but could not be accessed. I looked into the errorlog and found the following error:

‘The transaction log for database ‘database_name’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases’

Well I looked around and I did not find anything wrong. Disk were logs are kept had just under 100 Gigs free and the ldf files were not in a read-only state. Based on that I offlined the databases and then attempted to bring them back online. SQL found the MDF and the LDF files, went through recovery, found no issues and brought the databases back online.

Still not sure exactly what happened. It is strange also since all the databases use the simple recovery model. Based on that I am not sure how the log was full but there you go.

Installing SQL Server 2008

Yesterday I had to install SQL Server 2008 for the first time. I went through it on a development server first before installing on a production server. As I went through the setup process I noticed one thing that I think is long overdue. The Database Configuration step is a great addition to the install process. It is about time that MS allowed us to seperate out where all the Database and Log files will be before we actually do the install. This makes it easy to break out tempdb from the other databases and also to separate your data and log files. Make sure that you take advantage of this and plan out where you want all of the data files and log files to be on the file system. This will save some headache in the future when you have to move all of your databases as well as the system databases.

Powershell execute SQL Server Jobs

I have been asked to write a script that will allow users to connect to a server, get a list of only the jobs that I want them to see, and execute only those jobs. No options to do anything other than execute the job. This is what I have come up with.

[System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | out-null
$s = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) “Servername\instnace”
$jobserver = $s.JobServer
$jobs = $jobserver.Jobs | where {$_.Name -like “*jobname*”}
$totaljobs = $jobs.Count
$i = 1
echo “`n”
foreach ($job in $jobs)
{
echo “$i - $job.name”
$i ++
}
$exit = $i
Echo “$i - Exit”
echo “`n”
$exjob = Read-Host “What job would you like to execute`n”
if($exjob -ne $exit)
{
echo “Running Job”
$run = $exjob - 1
$jobs[$run].Invoke()
}
Echo “Exiting”

This will use integrated authentication to connect and enumerate jobs with the name criteria that you define. You will then get a numbered menu of the jobs and a prompt asking what job to run. Select the number of the job and it will execute on the server.

Please 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.

Migrating SQL 2005 Reporting Services

I have been asked to help with Migrating a SQL Server 2005 Reporting Services server from one host to another. I set up a couple VM’s and away I went. I found that it is pretty straight forward though. I did run into a couple little hickups along the way but nothing to dificult. I did have a problem though, after I attached the database on the new server and brought up the reporting services configuration manager. I could not get the server to initialize. To resolve the issue I had t delete the encryption keys. Then I was able to initialize the server but I could not get any of my reports to run. Kept getting error about connection string not being initialized. I then changed the encryption keys in the configuration manager and all started working again. One other thing is to make sure that you have the RSExecRole setup. Here is a link on migrating a reporting services server.

 http://msdn.microsoft.com/en-us/library/…

SQL Server 2008 Learning

I found this nice article that I thought I should pass on regarding Learning SQL Server 2008.

 http://searchsqlserver.techtarget.com/ge…

The article talks about key features and enhancments from SQL Server 2005, BI, Security, and more good stuff. Check it out and enjoy.

New Job

Last Thursday my company did some layoffs and re-orginization. I survived the layoffs but my job has changed. I am no longer a Database Administrator for a major media company in Phoenix Arizona, but now I am a Database Analyst for a nation wide media company. I am still based in the Phoenix area and still use my same desk, computer and all that. I will still be doing Database Administration for the company in Phoenix as well, but my role is expanding. This company is a big MS Shop and I will be essentailly the only Administrator for all instances that this large enterprise has. I am very excited about this opportunity and look forward to the challenges ahead of me. I know that this will put my learning on the fast track and I am hoping to have my first test done in a month or two at the most. Well wish me luck and I am sure that I will have many new stories and experiances to share as I move forward on this new adventure.