MSSQL Server

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.

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.

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.

Powershell Add a Login to SQL Server

I have a need to write a script that will check to see if a Windows Group exists on a server instance and if not then add it. I was attempting to do this using Powershell and SMO, but I was not really having any luck with that. I changed modes and I have decided to use the invoke-sqlcmd commandlet to get the task done. I use SMO to determine if the group that I want is on the server. If not then I create it. Like so:

$instance = “server\instance”

[System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | out-null
# Create an SMO connection to the instance
$s = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) $instance
#$s | Get-Member -memberType Property
#$s.logins | Get-Member
$logins = $s.Logins
$query = “CREATE LOGIN [domain\group name] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]“
$dba = $logins | where{$_.Name -eq “domain\group name”}
if ($dba -eq $null)
{
Echo “DBA Group does not exist.`n Adding Group”
Invoke-Sqlcmd -ServerInstance $instance -Query $query
$logins = $s.Logins
$dba = $logins | where{$_.Name -eq “PNI\PNI SQL SYSTEMS DBAS”}
if ($dba -ne $null)
{
Echo “`n Group Created”
$dba.name
}
}
else
{
Echo “Group already exists.”
$dba.name
}

Not to hard and it is nice to be able to use SMO to check for the login and then Powershell to create it.