Installing SQL Server 2008 Failover Cluster on a Windows Server 2008 R2?

I did a demo fest on installing SQL Server 2008 Failover Cluster on a Windows Server 2008 system a few weeks back for a user group event and the attendees requested that I post more information about how to do a slipstream of service pack in a SQL Server 2008 installation. With Windows Server 2008 R2 already released, Microsoft released KB article 955725 highlighting the need for SQL Server 2008 Service Pack 1 when installing on either Windows 7 or Windows Server 2008 R2. I wrote an article on MSSQLTips.com about it to supplement the series on installing SQL Server 2008 Failover Cluster.

This blog post came a bit late as I needed to wait for the article to be posted on the site so I can use it as a reference.

Enabling wireless on Windows Server 2008 - and eventually allowing Hyper-V guests to use it

A lot of you may probably ask why the need to enable wireless on a server operating system. Well, if you're like me who runs a server operating system on a laptop for testing purposes, there's a thousand and one reason why you would like to have it enabled. Now, since my Windows XP work-machine is now a virtual machine, I would need to have the wireless on my laptop enabled and working with Windows Server 2008. For this, you would need to install the Wireless LAN Service feature in Windows Server 2008. This article provides a detailed step-by-step procedure on enabling wireless on your Windows Server 2008. You just need to make sure that you have the appropriate Windows Server 2008 drivers for your wireless adapter or it won't work.

But my dilemma didn't stop there. I obviously don't want to access the internet via the Windows Server 2008 platform. That's what my Windows XP virtual machine is for. So what I did was to add an Internal virtual network using Hyper-V's Virtual Network Manager. This will create another network adapter on the host operating system - in this case, Windows Server 2008. I've renamed this WiFi-Guest-Bridge. Next, I've added a network adapter on the Hyper-V guest and mapped it to this virtual network. Once, I've done both of these, I've bridged the wireless network adapter and the WiFi-Guest-Bridge network adapter, thereby, providing wireless connectivity to my Hyper-V guest.

Keith Combs provided a step-by-step procedure (with screenshots) on how to configure wireless networking with Hyper-V guests in his blog

It is very important to name your network adapters accordingly as Windows does a real good job of using a generic naming convention (i.e. Local Area Network x) which adds confusion especially if you are dealing with a ton of virtual networks from within a single Hyper-V (or other virtualization) platform

Connecting HyperV guests to the local network even before installing Integration Services

So, I have finally had my hands on Microsoft Hyper-V. I've installed Windows Server 2008 with Hyper-V role on my laptop for creating virtual machines. I've decided to virtualize my Windows XP work-machine so that I won't have to worry about backups in case it got corrupted. What I had in mind was to simply create a virtual machine with all the work-related applications installed and create a network folder that maps to my host operating system's local drive, making it easy to backup - just backup the VM once for the base image and regularly backup the folder containing my files. Unfortunately, in order for you to take full advantage of networking and all sorts of stuff in Hyper-V guests, you need to install Integration Services. This, however, requires Service Pack 2 or higher on Windows XP machines. There are a few ways to install Service Pack 2 or higher on a Windows XP virtual machine running on Hyper-V: install using a CD, a USB drive or a network drive. I am not about to embark on any of the first two options since I try to avoid creating CDs for patches and service packs to save on disks (not to mention saving the environment a few non-biodegradable materials) whereas USB devices are not supported on Hyper-V, something that a lot of customers have been asking for. That leaves me with the network drive option. The solution: add a legacy network adapter. A legacy network adapter emulates the physical network adapter of the host operating system, thereby, not needing virtual machine drivers for the guest operating system. That is all I need to know to get Windows XP Service Pack 2 and higher installed on my virtual machine. I've added a legacy network adapter on my virtual machine and set it to the same IP subnet as that of my host and I have a virtual network between my host and my guest. This TechNet article describes configuring networking on Hyper-V

After managing to install Windows XP Service Pack 3, I can now install Hyper-V Integration Services and work on the usual networking stuff using the Hyper-V emulators and drivers. Since I no longer need the legacy network adapter, I removed it immediately after installing the service pack as I now have the option to add network adapters that can be recognized by the guest operating system.

You can read more about Hyper-V Integration Services from this article

Blank space between startup parameters

I was trying to figure out why my startup trace flags don't work. While it is mentioned in this Microsoft TechNet article about using semi-colons to separate one startup parameter from another, nothing was mentioned about the leading blank space in between defining the parameters. SQL Server MVP Erland Sommarskog recommended removing the leading blank spaces in between the trace flags and parameters like the one in the example below

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf;-T1204;-T1222;-T845;-T1262;-T2330

The best way to check if your trace flags are actually enabled during startup is to run the DBCC TRACESTATUS command or, as Erland suggested, looking at your SQL Server error log and note whether the trace flags are aligned with the other SQL Server parameters like the one below. Notice how aligned the startup parameters are (you can see that the - symbol are all aligned). A mere leading blank space would definitely cause them not to work.

2009-11-05 10:49:36.010 Server Registry startup parameters:
2009-11-05 10:49:36.010 Server -dC:\Program Files\Microsoft SQL Server\MSSQL\DATA\master.mdf
2009-11-05 10:49:36.010 Server -eC:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG
2009-11-05 10:49:36.010 Server -lC:\Program Files\Microsoft SQL Server\MSSQL\DATA\mastlog.ldf
2009-11-05 10:49:36.010 Server -T1204
2009-11-05 10:49:36.010 Server -T1222
2009-11-05 10:49:36.010 Server -T845
2009-11-05 10:49:36.010 Server -T1262
2009-11-05 10:49:36.010 Server -T2330

NT AUTHORITY\NETWORK SERVICE account in SharePoint Content Database

I have had the opportunity to assist a WIndows SBS MVP migrating Windows SharePoint Services 3.0 databases from SQL Server 2005 to SQL Server 2008 in a Windows SBS 2008 environment. He was following the steps outlined in this Microsoft TechNet article to move the content databases and configure them from inside SharePoint Central Administration when he hit a wall and couldn't move forward. When he was at the point where he is already pointing to the new content database, SharePoint started throwing an error. My first instinct was that it has something to do with permissions but I need to capture some activity before even doing anything. Apparently, SQL Profiler is not installed. I opted to change the Authentication mode from Windows to Mixed mode just so I can use a SQL Server login with the SharePoint configuration. After creating a SQL Server login for SharePoint to use, everything was back to normal. While I wasn't comfortable with using Mixed Mode authentication for SharePoint, I dug deeper to see what Windows account was being used thru the Application Pool from within IIS. It appears to be NT AUTHORITY\NETWORK SYSTEM - the default account when you install Windows SharePoint Services 3.0 in the same server as the SQL Server (this is a common setup with Windows SBS). What I did was to add the NT AUTHORITY\NETWORK SYSTEM account in SQL Server using the CREATE LOGIN command (you won't be able to do this in Management Studio as the account is not exposed) and, then, assign the db_owner role in the content database.

I went back to the SharePoint Central Administration after that to switch the configuration of the content database from using a SQL Server login to using Windows authentication. That did the trick.

Note that if you are moving your SharePoint databases from SQL Server 2005 to SQL Server 2008, whether within Windows SBS or a full blown Windows Server system, make sure you check the logins as they need to be moved as well for the SharePoint application to work. You can even use the transfer SQL Server logins script from Microsoft to do this

SQL Server Deep Dives book now available

I have had an opportunity to be a part of a wonderful project that took almost a year in the making.

SQL Server MVP Deep Dives is not your ordinary SQL Server book as it brings together the world's most highly-regarded SQL Server experts to create a masterful collection of tips, techniques, and experience-driven best practices for SQL Server development and administration. These SQL Server MVPs—53 in all—each selected a topic of great interest to them, and in this unique book, they share their knowledge and passion with you.

SQL Server MVP Deep Dives is organized into five parts: Design and Architecture, Development, Administration, Performance Tuning and Optimization, and Business Intelligence. Within each part, you'll find a collection of brilliantly concise and focused chapters that take on key topics like mobile data strategies, Dynamic Management Views, or query performance. The range of subjects covered is comprehensive, from database design tips to data profiling strategies for BI.

The authors of this book have generously donated 100% of their royalties to support War Child International. War Child International is a network of independent organizations, working across the world to help children affected by war. War Child was founded upon a fundamental goal: to advance the cause of peace through investing hope in the lives of children caught up in the horrors of war. War Child works in many different conflict areas around the world, helping hundreds of thousands of children every year. Visit www.warchild.org for more information.

If you are attending the PASS Community Summit 2009 this year at Seattle, WA, you can get your copy there. And since most of the authors are SQL Server MVPs, you will have a chance to have your book signed during the conference. I believe that there is also a book launching activity that will occur during the summit so watch out for that

Learn SQL Server thru videos

I'm passionate about learning and helping people develop their full potential thru continuous personal and professional development. So everytime I chance upon something that would be of help to somebody else, I pass it along

MSDev.com has tons of resources to help IT professionals and developers learn more about technology. Check out this link for SQL Server-related resources

ISDATE() function returns 0 for valid DATETIMEOFFSET values?

Quite confusing but true. I was working on trying to validate a specific value if it is indeed a valid DATETIMEOFFSET data type using the ISDATE() function until I saw this forum post

"Adam,

The problem is that ISDATE returns 0 for valid DATETIMEOFFSET values."

I couldn't believe it since Books Online until I tested it out myself

IF ISDATE('2009-09-08 10:19:41.177 -05:00') = 1
PRINT 'VALID'
ELSE
PRINT 'INVALID'

This returns INVALID when, in fact, it is a valid datetime value with time zone awareness. I even tried the values provided by Books Online and still gives me invalid results. I have yet to wait for a response from the SQL Server product group regarding this but at least it gives us an opportunity to dig deper

WARNING:the CID values for both test machines are the same

There are a thousand or more issues that can come up with virtualization if not done properly. One of which is having the same machine SID in the domain in case you decide to join them to one. This happens a lot of times especially when a virtual machine is created from a template or cloned from another virtual machine without using the appropriate tools like Sysprep from Microsoft

One unique incident occurred to me while trying to troubleshoot a MS DTC communication issue between SQL Server instances. A client of ours requested for assistance to fix a distributed query that uses MS DTC. Apparently, communication between the two SQL Server instances is not happening. My usual round of troubleshooting started with a series of network connectivity tests, ranging from PING to TELNET to NETSTAT to whatever is necessary to make sure that communications between the servers are working fine. That led me to look for ways to check for connectivity specifically with MSDTC. One tool from Microsoft is DTCPing, a utility to help troubleshoot MS DTC firewall issues. While I know for a fact that firewall is not an issue in this particular case, I've decided to give it a shot. Running the DTCPing utility on both servers gave me this error message in the log

WARNING:the CID values for both test machines are the same

A quick Google search led me to this blog post and made me think that the servers might have been cloned. Sure enough, when I asked the customers about the history of the servers, they were indeed cloned VMWare images. They didn't use Sysprep to prepare the images after the cloning process, hence, the reason for having the same CID values. There's nothing wrong with VMWare here. It's just the process that's pretty screwed up. What are the chances of two machines having the same GUID values which are supposed to be globally unique across the enterprise? Very slim unless they are inappropriately cloned.

I followed the steps outlined in the blog post to fix the CID values

• Use Add/Remove Windows Components to remove Network DTC.
• Run MSDTC -uninstall in the command-line
• Delete the MSDTC keys in in the registry

HKLM/Software/Microsoft/Software/MSDTC
HKLM/System/CurrentControlSet/Services/MSDTC
HKEY_CLASSES_ROOT\CID

• Reboot the server
• Run MSDTC -install in the command
• Use Add/Remove Windows Components to add the Network DTC back.
• Restart the Distributed Transaction Coordinator service
  

Following these steps helped solve the MSDTC issue but sure enough, another issue surfaced. Since SQL Server uses MSDTC in a few of its processes like executing distributed queries, the installation got screwed up big time. When we used the server to test a disaster recovery process for the entire SQL Server instance, restoring the master database became a real pain. I spent hours trying to restore the master database but to no avail. The resolution was to simply uninstall and re-install SQL Server. Only then was I able to restore the master database successfully. Lesson learned: if the foundation is screwed up, anything built on top of it will surely be the same. That applies to just about anything, whether you're building a server or developing a character.

Extending your Active Directory Schema

While most organizations use Active Directory as their directory service, very few maximize the use of it. Several applications out there like Microsoft Exchange extend the default schema to track Exchange-related information. You can, however, extend the schema yourself by opening the Active Directory Schema snap-in from the Microsoft Management Console. Unfortunately, it is not available by default. You need to register the schmmgmt.dll using the RegSvr32.exe utility (and I thought I would never have to use this utility again). Open a command prompt and run the command below

regsvr32.exe schmmgmt.dll

After that, you can now open up the Microsoft Management Console and add the Active Directory Schema snap-in. You can now add new attributes to the objects as you wish, although, updating the existing ones is definitely not recommended

Creating Active Directory Users with Windows PowerShell

While it may seem easy to create Active Directory users using the management console, I still prefer doing it using scripts so as to make sure that they are done in a uniform, standard fashion (not to mention as fast as one can possibly do especially if you will be doing it for many users). I've referenced the scripts provided at the CodePlex site for ADSI and Active Directory for Windows PowerShell (full credit goes to them) to create users in Active Directory for Windows Server 2008. This also works for Windows Server 2003. While I may be a big fan of automation, it is important to highlight that processes are what makes automation really work. The reason I am saying this is that the CSV file can come from different sources, say, an intranet site where you ask employees to log in and key in their details. Having a process in place to make sure that users who would be entering their details in a standard way would eliminate the need to cleanse the data (I'm still thinking as a DBA here) in the long run. Plus, having a standard in place as an organization is starting out will make it flexible enough to scale as growth happens.


# define constants
$domainstr = ",dc=domainName,dc=local"
$domainnb = "domainName" # domain netbios name
$domain = "domainName.local"

$ADs_UF_NORMAL_ACCOUNT = 512 # Disables account and sets password required.

# Remember to enable the account before logging in


# Prompt user to enter the default passsword for the users
$defaultPassword = Read-Host "Please enter default Password:" -asSecureString

# Read the list of users from the CSV file
# Include other user properties in the CSV file as necessary

Import-csv users.txt | foreach
{
# Create user name based on FirstName and LastName column in the CSV file
$strUser = $_.firstName + " " + $_.lastName


#Form the LDAP string based on the OU column from the CSV file
$strLDAP = "LDAP://OU=" + $_.OU + ",OU=domainName Domain Users" + $domainstr

$target = [ADSI] $strLDAP
$newUser = $target.create("User", "cn=" + $strUser)
$newUser.SetInfo()

#Define a naming convention for the login based on your corporate policy
#This one uses the first letter of the firstname and the lastname
$userID = $_.firstName[0]+$_.lastName

#Define the other user attributes based on the columns defined in the CSV file
$newUser.sAMAccountName = $userID.ToString()
$newUser.givenName = $_.firstName
$newUser.sn = $_.lastName
$newUser.displayName = $_.firstName + " " + $_.lastName
$newUser.userPrincipalName = $_.firstName[0]+$_.lastName + "@" + $domain
$newUser.mail = $_.Email
$newUser.physicalDeliveryOfficeName = $_.Location
$newUser.title = $_.Designation
$newUser.description = $_.Designation
$newUser.SetInfo()

$newUser.SetPassword($defaultPassword.ToString())

#Normal user that requires password & is disabled
$newUser.userAccountControl = $ADs_UF_NORMAL_ACCOUNT

Write-Host "Created Account for: " $newUser.Displayname

}

So what's a Tombstone Reanimation Feature?

They say that this feature is available in as early as Windows 2000 but this is the only time I am hearing about it. But what is it, anyway? Tombstore reanimation is the process of re-activating a deleted object from Active Directory. When Active Directory deletes an object, say a user or computer, from the directory, it does not physically remove the object from the database. Instead, it marks the object as deleted by setting the object's isDeleted attribute to TRUE, removing most of the attributes from the object, renaming the object, and then moving the object to a special container in the object's naming context (NC) named CN=Deleted Objects. The deleted object is now called a tombstone as is totally invisible from any LDAP tools like Active Directory Users and Computers. Even though the object is invisble, it is still there and readily available for us to salvage the data for the purpose of disaster recovery

This TechNet Magazine article highlights how to reanimate Active Directory tombstone objects in case you need to recover an object that has been accidentally deleted. I can't wait to try it out for myself though

Check the last backup date in SQL Server using WIndows PowerShell

This article highlights how to use Windows PowerShell to retrieve database properties using SMO. Notice how easy it is to check the database properties using pretty common syntax

One of the challenges I have when I was starting out as a SQL Server DBA was to check for the last backup date for a database. One way to do this is to find out which tables in the MSDB database contain the records of the backup history. What's really challenging here is the fact that you would have to look at the tables and their corresponding relationships which, apparently, MSDB doesn't have. You simply have to rely on what SQL Server Books Online has to say. Plus, the MSDB database will only contain records for databases with backups. What about those without?

For SQL Server 2005, the script below displays the last backup date of all the databases on your SQL Server instance. This script is from the MSDN Code Gallery

SELECT
T1.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') AS LastBackUpTaken
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
GROUP BY T1.Name
ORDER BY T1.Name

You can simply replace the sys.sysdatabases table with master.dbo.sysdatabases for SQL Server 2000

Below is the equivalent script using Windows PowerShell.

$instance="Your_SQL_Server_Instance_Name"
[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

$dbs = $s.Databases
$dbs | select Name,LastBackupDate, LastLogBackupDate | format-table -autosize

The only thing to note here are the last two lines - the one that creates an instance of the database object and the one that displays and formats a few of the database object properties. The first few lines will be the same for just about any PowerShell script that will access SQL Server using SMO

Copying DTS packages from SQL Server 2000 to SQL Server 2005 and SQL Server 2008

I've seen this approach from the CSS SQL Server Engineers blog post and wondered if I can give it a shot. We're migrating a SQL Server 2000 database to a SQL Server 2005 instance (finally!) and that included a lot of DTS packages. While I would highly recommend upgrading the DTS packages to SSIS packages for the long term due to product support lifecycle reasons, that would take quite a while and would impact businesses dramatically.

I've used Method 2A mentioned in the blog post to move the DTS packages from the SQL Server 2000 to SQL Server 2005 as I have like more than a hundred DTS packages stored in MSDB. Remember that the size of the sysdtspackages table in MSDB will depend on not just the number of packages stored but the number of versions of the packages you decide to keep.

After a restore of the MSDB database from the SQL Server 2000 instance and importing the records from the sysdtspackages table, you would definitely want to install the Microsoft SQL Server 2000 DTS Designer Components to modify and edit your DTS packages in SQL Server 2005 to change those connection strings, test them after migration and so on.

No Drives Found error installing CentOS 5.2 on VMWare

Ok, so this is not my typical blog post that talks about anything Microsoft but it still is technology so it makes a good blog post.

I was installing CentOS 5.2 on a VMWare Workstation image when I suddenly hit a wall with this error

No Drives Found
An error has occurred - no valid devices were found on which to create new file systems. Please check your hardware for the cause of this problem.

Now, this isn't the first time I'm installing CentOS on a virtual machine nor on a physical hardware but it definitely is the first time to install version 5.2. Back in the old versions, everything was pretty straight-forward and that I had never encountered this error message before. I was beginning to be tempted to use an iSCSI disk for the installation with another virtualized iSCSI disk but I wouldn't want to go down that road unless I will be configuring this virtual machine as a clustered server. Having searched thru a ton of newsgroup and blog posts on similar issues, a few of them mentioned changing the Operating System to Red Hat Enterprise Linux or Other Linux 2.4.x kernel to make it work. I did find a recommendation to change the virtual disk from SCSI (which happens to be the default setting when you configure your virtual machine) to IDE. That did the trick, although I needed to create a new virtual machine in the process which was the quickest way to do it.

So, remember - use an IDE disk in your VMWare image when installing CentOS 5.2

Backup on shared folders running on a local system account?

I still see a lot of SQL Servers running using the LocalSystem account, particularly, MSDE 2000, which is very popular among third-party applications that requires storing data in a database. Others run a stand-alone SQL Server system and use LocalSystem account for the service accounts. While this may be a security risk, a lot of users still have them configured. And when they need to generate database backups on a shared folder, the SQL Server service's attempt to connect to network resources are denied access because they have no credentials and they are using a null session.Of course, a typical recommendation would be to change the service account to a local Windows or a domain account with least privilege but not everybody is open to that suggestion. So how do you allow SQL Server to generate a backup on a shared folder while using a LocalSystem account? The solution: enable null session shares

While I do not advocate such workarounds as it opens up additional security loopholes, it still is a workaround. And as I usuallly say, WARNING: This is not a recommended approach. Use at your own risk

Microsoft has a documented procedure to enable null sessions shares and while the KB article mentions Windows 2000, it does work for Windows Server 2003. This should be done on the Windows machine that hosts the shared folder. A word of caution if you intend to use this approach - document every step that you do and make sure you rollback any changes made after generating your database backup. Tasks like enabling the Guest user account (this is disabled by default), modifying the registry, etc. should be rolled back as soon as you're done, otherwise, you're opening up security vulnerabilities across your network.

Could not load file or assembly

I was assisting one of our customers move their .NET applications from one server to another and run them using Windows Task Scheduler. I did highlight to them that in order to make the application work, we have to make sure that the correct .NET Framework version was installed on the target server, that the correct .NET Framework version was referenced by the assemblies used (this is very important when the target server has multiple versions of the .NET Framework installed and the assembly was created using an older version) and that the appropriate permissions were given to the accounts that will launch the EXE files from within the Windows Scheduled Tasks. I wasn't surprised when after the move, I saw this error from calling the assembly from the command prompt

System.IO.FileNotFoundException: Could not load file or assembly 'MyAssembly, Version=1.0.0.0, Culture=neutral, PublicKeyToken=0e1d67af9d31f077' or one of its dependencies. The system cannot find the file specified.
File name: 'MyAssembly, Version=1.0.0.0, Culture=neutral, PublicKeyToken=0e1d67af9d31f077' ---> System.IO.FileNotFoundException: Could not load file or assembly 'MyAssembly' or one of its dependencies. The system cannot find the file specified.
File name: 'MyAssembly'

This blog post highlights a couple of different reasons why an assembly would not load. What I would like to highlight, though, is the use of a pointer to the correct .NET Framework version in the application's config files.

< version="v1.1.4322" safemode="true">

A lot of developers in the past simply didn't realize that it would be possible to co-exist different versions of the .NET Framework in a single machine yet cause application issues if not handled properly. This Microsoft document, although relatively old, describes how to manage multiple versions of the .NET Framework on a single machine. If unsure, you might want to enable the Assembly binding logging option thru your registry key

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Fusion!EnableLog] (DWORD) = 1

You can also use the Assembly Binding Log Viewer tool if you have the SDK Tools installed on your target machine (of course, as best practice will tell you, you wouldn't install anything unnecessary on your production servers)

Finding out the .NET Framework version that an assembly uses

There will be cases where you would need to find out the .NET Framework version of an application or assembly running on a machine and you just don't have the right tools. This is especially true when you need to promote your code from test to production environment. If there is only one version of the .NET Framework on the machine, it would be easy. But if you have servers with multiple versions of the .NET Framework installed and the assembly is not configured to bind to the correct framework version, you might end up having an application that might break due to incompatibility issues. This is true for .NET Framework versions 2.0 and below although I haven't really tried out the newer ones but it would be basically the same since .NET Framework 3.0 and 3.5 are just stacks on top of 2.0

If you don't have the tools on the server to check, you can simply copy the assembly on your local machine and use either ILDASM.exe or simply download RedGate's Reflector. Reflector does not need installation as long as you have the appropriate .NET Framework versions in your machine. Just extract the EXE and CONFIG files and you're good to go. A video demonstrating how to use RedGate's Reflector can be found here

Transfering Windows Scheduled Task Jobs between servers

There might be some cases where you need to transfer Windows Scheduled Task jobs like maybe promoting them from test to production. The simplest way to do it is by simply opening the Windows Scheduled Task in Windows Explorer for the source and the destination servers (you can do this by expanding on My Network Places and selecting the destination server. Just remember to open separate window for the two of them). Copying and pasting between windows should do the trick. It would be a bit challenging if the source and target servers are not in the domain as you would need an account that has the appropriate privileges on both machines

Windows 7 on VMWare Workstation 6.5

I was at the Microsoft Canada Energize IT Windows 7 Installfest event in Ottawa and volunteered to assist with the attendees while they were doing the installation. While the supported installation options during the event were clean install, upgrade, dual boot or virtualization using Hyper-V, Virtual PC or Virtual Server, a few of those who came were asking if they can install it on VMWare. Now, I have been working with VMWare for years now and my take on this is if its a Microsoft operating system and it runs on the Microsoft Virtualization products, it will definitely run on VMWare. I was making suggestions about how to go about it and the caveats when working with VMWare, I have never seen Windows 7 yet. So what I did was to fire up my VMWare Workstation and started installing Windows 7, just so I can answer the questions knowing I had he experience of doing it rather than just saying "I know it will work." I used the ISO image provided by Microsoft and the installation went really fast. If you've installed Windows Vista or Windows Server 2008 before, the process is pretty similar. I guess I'm on my way to playing with the Windows 7 image just to get the hang of it. And one thing is for sure, it will work on VMWare - even with 512MB of RAM.

Changing a SQL Server 2000 login

WARNING: This is not a recommended approach. Use at your own risk

While SQL Server 2005 has the ALTER LOGIN statement to change the properties of a SQL Server login account, SQL Server 2000 does not have such a command. Unfortunately, there are cases where you need to simply rename the login due to a misspelled name or a change management policy. The proper way to do it in SQL Server 2000 is to create the new login, map the permissions and roles of the existing login that you wish to change to this new login and, then, drop the old login. I wouldn't want to go thru that if I only have to rename the login. The only simpler way to do it is to modify the system tables. As I've said, it is not recommended to modify the system tables and/or objects directly so bear in mind that doing this would be at your own risk. This would also require that you torn on allowing ad hoc updates to system tables and turning it off afterwards

sp_CONFIGURE 'ALLOW UPDATES', 1
GO
RECONFIGURE WITH OVERRIDE
GO

UPDATE db..sysusers
SET name='newLogin'
WHERE
name='oldLogin'

UPDATE master..sysxlogins
SET name='newLogin'
WHERE
name='oldLogin'

sp_CONFIGURE 'ALLOW UPDATES', 0
GO
RECONFIGURE WITH OVERRIDE
GO

A similar stored procedure is described here

Fixing Orphaned Database Users in SQL Server 2005

I see this on almost all SQL Server instances I work on - existing user in a database with dbo permissions yet they could not access anything in the database. This is because SQL Server doesn't really look at the user or login name but rather the SID values (I've talked about this in here). Maybe they restored a database back from a development server to the production server without synchronizing the logins. If this was SQL Server 2000, you'd probably end up dropping logins, recreating the logins using the script provided by Microsoft in this KB article and recreate the database users. Unfortunately, this is not a straight-forward task. Good thing Microsoft has provided the sp_change_users_login stored procedure to fix orphaned users. Using the UPDATE_ONE parameter will 're-map' an orphaned database user to an existing SQL Server login with the same name. That in itself will spare you the trouble of going thru the process mentioned above (although you will still have to worry about orphaned database users that own schemas which is very much common among databases that have been upgraded from SQL Server 7.0 and 2000). Below is a syntax for 're-mapping' an orphaned database user to an existing SQL Server login

EXEC sp_change_users_login 'UPDATE_ONE','user','user'
GO

You still need to run the stored procedure with the REPORT parameter to return a list of orphaned database user

EXEC sp_change_users_login 'Report'

Whacked out Windows Service

I was supposed to blog about this sometime last year when we we're called in to be standby engineers for a large government organization in Singapore as they were trying to change their hardware for one of their SQL Server clusters (I had another similar incident today which reminded me to blog about it). When they didn't get the chance to successfully move to the new hardware, they rolled back their changes. Unfortunately, even their rollback process was screwed up as they couldn't bring up the services after doing a restore of their backup. Looking at the services applet in Windows, we couldn't start the service at all. It looks like their backup software was not able to resolve the long names in Windows, thus, introducing the tilde (~) character in the location of the executable for the service. The workaround for this is a registry hack. You can remove or change settings in your service by looking at this registry key

HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services

Look for the service name and update the ImagePath key to the correct parameters that you would need for your service. In case you're thinking of deleting a service when it was not properly uninstalled or when it is totally screwed up, you can use the Windows command line tool sc to do the trick

RELOG tool in Windows Server 2008

I was configuring one of our customer's Windows Server 2008 to capture PerfMon counters for our analysis when I accidentally ended up storing the log file in binary format. Now, I didn't like the idea of reading the binary file as I will be importing it in a SQL Server database for Reporting Services to use. Although PerfMon has the ability to log directly to a SQL Server database using ODBC, I am not allowed to do anything outside of PerfMon. After digging thru some options in Windows Server 2008, I found out about the RELOG tool. The tool gives you the ability to extract out the counters from any format generated by PerfMon and store them in a different format. In my case, I had to store them as a CSV file



relog PerfMonCounterLog.blg -f csv -o PerfMonCounterLog.csv



I was trying to find more about this tool from Microsoft TechNet but couldn't find any (or maybe I wasn't just looking hard enough) although MSDN has something about the method called ReLog which does exactly the same thing. It's exposed as an API so you can call it from an application should you wish to do so

A thousand and one reasons for a generic error message.

I've spent a couple of hours trying to troubleshoot a clustered SQL Server 2008 installation. All I know is that it throws this error message after the installation process and does not give me any clue at all

The cluster resource ‘SQL Server (MSSQLSERVER)’ could not be brought online. Error: The group or resource is not in the correct state to perform the requested operation. (Exception from HRESULT: 0×8007139F)

Now, this might look like a dependency issue not working correctly but when I checked the Failover Cluster Management console on Windows Server 2008, all the dependencies are online and working as expected. As always, I started searching the Internet for related errors and couldn't find anything really specific except for the same thing - dependency issue. Now, here's what I found out. Since all of the dependencies - disks, MSDTC, IP and virtual server name - are all online, maybe it doesn't have anything to do with them after all. So the first thing I did was to do a PING test to the virtual server name for my clustered SQL Server instance and guess what I found out - there is another IP registered on the DNS server with the same FQDN (maybe a previous installation that wasn't cleaned up properly). I logged in to the DNS server and updated the IP address of my clustered SQL Server instance, ran ipconfig /flushdns on the node on which I am logged in and started the service in Failover Cluster Management. It worked! It just tells you that you should think outside of the box every now and then. It really pays to have that background in network and systems infrastructure every once in a while.

Want to free up disk space on your Windows Server 2008 system partition?

Here's one idea I got from this blog post. It came from KB 920730: How to disable and re-enable hibernation on a computer that is running Windows Vista. It certainly was not Windows Vista, but who would want to let their servers hibernate? Besides, Windows Server 2008 is listed on the Applies To section of the KB article. I've decided to give it a shot. Run this command on your command-prompt with administrative privileges

powercfg.exe /hibernate off

Now, if you happen to take a look at your system partition and show all the hidden files, you would see a hyberfil.sys file, a hidden system file located in the root folder of the system partition. The Windows Kernel Power Manager reserves this file when you install Microsoft Windows. The size of this file is approximately equal to the amount of RAM installed on the computer. Since we dont need hibernation on servers, you can just remove this file using the command above. Sometimes I wonder why everything is on the system partition when you don't even need them

"The current SKU is invalid." error? Didn't I pay for my license?

I've done quite a few SQL Server 2008 cluster installations before which is why this struck me as a surprise. I was trying to rebuild one of my test mahines when I hit upon this error message while adding the second node on a two-node SQL Server 2008 cluster on a Windows Server 2008.

The current SKU is invalid.

After a quick search on the Internet, I found out that this is a bug (my first time to get bitten by it). There is currently a Microsoft Connect item regarding this and it mentions having Cumulative Update 1 applied should fix this issue but I wouldn't want to apply a hotfix for something like this as I normally do that after the entire installation is complete. A few more hits on Google directed me to a forum post that mentions about deleting the DefaultSetup.ini file from the installation media. Just make sure you copy the installation key from this file before deleting it or simply do what I did - move the file some place else. What I usually do is copy everything on a local disk for the installation to be a lot faster. After going thru the Add node to a SQL Server failover cluster option, I manually entered the installation key and it the installation completed successfully. I wonder why I never got this error during my previous installations

The Return of the Living Dead:Job Owners who Keep Coming Back

Have you ever had the feeling that SQL Server was playing a trick on you? I had those moments. I was updating a database maintenance plan and ran the corresponding job associated with it. I got an error saying that the job owner did not have permissions to execute the task. So I did what I would do as part of my testing procedure- change the job owner to sa and re-ran. And so it completed successfully until I realized I need to change some parameters in the database maintenance plan. After updating the maintenance plan, I again re-ran the same job and unexpectedly, it failed throwing the same error. When I checked the job owner, it reverted back to the original login prior to me changing it to sa. This login happens to be the user account that created the database maintenance plan. The worse part is, the maintenance plan has like 20+ subplans, each creating a SQL Server job. Now, you wouldn't want to change the job owner for each job associated with the maintenance job every time you change something, would you? Well, there's just no way you can do it directly. But there's always a workaround. This blog post provided a workaround for this case. You just modify the sysdtspackages90 table in the msdb database

UPDATE
msdb.dbo.sysdtspackages90
SET
OWNERSID = SUSER_SID('DOMAIN\new_user')
WHERE
OWNERSID = SUSER_SID('DOMAIN\old_user')

For SQL Server 2008, this query should do the trick

UPDATE msdb.dbo.sysssispackages
SET OWNERSID = SUSER_SID('DOMAIN\new_user')
WHERE name = 'MaintenancePlan' AND OWNERSID = SUSER_SID('DOMAIN\old_user')

There's currently a Microsoft Connect item for this case so feel free to vote on it if you think it's worth having a solution that just a workaround

How to immediately shrink the SQL Server log files

Have you ever wanted to shrink your log files but couldn't do it no matter how hard you try? While I and the majority of SQL Server DBAs would not recommend shrinking the transaction log (or any data file in your database), you'll end up doing it one way or another due to emergency situations. Here's what you'll probably end up doing as defined in this Microsoft KB article:

• Backup the transaction log. This will truncate the log
• Shrink the log file

Pretty simple, right? Well, there are times when this might not work because SQL Server does not shrink the log immediately. The DBCC SHRINKFILE operation occurs only at checkpoints or transaction log backups. SQL Server divides each physical log file internally into a number of virtual log files (VLFs), which make up the transaction log. This MSDN article describes virtual log files in SQL Server. SQL Server MVP Tibor Karaszi highlights why you would not want to shrink your log files. This blog by Johnny Hughes has a script that lets you do this task.

USE databaseName
GO

DBCC shrinkfile(<file_id>,NOTRUNCATE)
DBCC shrinkfile(<file_id>TRUNCATEONLY)

CREATE TABLE t1 (CHAR1 CHAR(4000))
GO

DECLARE @i INT
SELECT @i = 0
WHILE (1 = 1)

BEGIN
WHILE (@i < 100)
BEGIN
INSERT INTO t1 VALUES ('a')
SELECT @i = @i +1
END

TRUNCATE TABLE t1

BACKUP LOG databaseName WITH TRUNCATE_ONLY
END

Are you a SQL Server DBA wanting to Learn Windows PowerShell?

I've been working on PowerShell for quite some time but mostly for systems administration tasks. Since Microsoft has decided to make PowerShell as a common engineering criteria for all server applications being released, every SQL Server DBA needs to know at least what it is and what they can do with it.

This article is a first in this series of introducing Windows PowerShell to SQL Server DBAs. Go check it out

Trying to attach an MDF file to SQL Server 2005?

While this is not recommended especially when you're a bit paranoid about disaster recovery and data integrity, I still find a lot of people doing it. I guess a lot of independent software vendors don't understand the underlying concepts behind the database engine. While it may have worked in SQL Server 2000 using Enterprise Manager, trying to attach an MDF file in SQL Server 2005 Management Studio will fail. To work around this, you can use the sp_attach_single_file_db system stored procedure. This will attach the MDF file and create an LDF file as well. This will only work if your database only has one datafile. Using this if your database has multipe datafiles won't work. You do have to run the DBCC CHECKDB command to validate your database

I'd still prefer the backup/restore approach. Nothing beats having a peace of mind

Why it is necessary to understand the underlying infrastructure even though you're a DBA

I keep highlighting that it is necessary for DBAs to understand the underlying infrastructure as it helps when it comes to resolving issues. I was having connectivity issues with my SQL Server 2005 instance as sqlcmd and osql are both throwing this error

HResult 0xFFFFFFFF, Level 16, State 1SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

This blog post highlights how to troubleshoot connectivity issues with SQL Server and gave me an idea why I am getting the issue. The first thing I did was to run an ipconfig /flushdns to make sure I looking at the correct IP-hostname resolution from my DNS server. Then, I tried to PING the IP address of the SQL Server instance and it worked just fine (I usually add the -a switch on my PING command to include the hostname if it is indeed correct). I tried the reverse - PING the hostname - but my DNS server is returning a different IP for the same hostname. Lokks like the DNS entry for my SQL Server box is a bit screwed up. I logged on to the DNS server to validate and I was right. I modified the IP address of the A record pointing to my SQL Server box, ran another ipconfig /flushdns before doing another PING test to validate the change. Once everything is ok, I was able to connect to my SQL Server instance using sqlcmd and osql. It pays to understand a bit about something not directly related to your work sometimes

Start Windows Services using PowerShell

I start and stop services on my workstation every now and then for testing purposes (well, primarily to stop services I do not use to minimize surface area and free up available resources which are not being used). What I used to do was to run a NET START command listing all the services that are started and piping it to a text file. Next, I'd insert a NET STOP on each line in the list to call the NET STOP command stopping that particular service. Unfortunately, there are cases where I wanted to start a specific service that start with a specific name. An example of this is starting the SQL Server instances running on my laptop. I have like 4 instances which are SQL Server versions ranging from 2000 to 20008. Now, I happen to memorize the instance names of those SQL Server instances, making it easy for me to just run a NET STOP servicename. Although it would be rather easy, imagine doing that on a server with like 9 instances (not to mention memorizing the instance names). I wouldn't want to type the NET STOP command 9 times. Enter PowerShell

Here's a PowerShell script to start all SQL Server instances on the local computer. You can modify the parameters if you want to suit your needs. I've used this to start all the services related to Symantec on my machine as well

Get-Service where {$_.Name -like "MSSQL$*"} Start-Service

(NOTE: There's a pipe character "" between Get-Service cmdlet and Where and before the Start-Service cmdlet. It just does not display here)

So what's this CLSID {ABF05265-635E-44B0-A28F-AEA45247ACA0}?

I was working on a server when I noticed an error in the Windows Event Log in this format

Event Type: Error
Event Source: DCOM
Event Category: None
Event ID: 10016
User:
Computer: SERVER1

Description:
The application-specific permission settings do not grant Local Launch permission for the COM Server application with CLSID {ABF05265-635E-44B0-A28F-AEA45247ACA0} to the user SID (S-1-5-21-573225893-205518295-3307690801-69150). This security permission can be modified using the Component Services administrative tool.

Digging up on the registry gave me this information. The CLSID value {ABF05265-635E-44B0-A28F-AEA45247ACA0} happens to be for Microsoft.SqlServer.Dts.Server.DtsServer. I checked the Component Services console and opened the Properties for the MSDTSServer DCOM application. I've added the user account mentioned in the error message in the Launch and Activate Permissions and that did the trick

NewSID on Windows Server 2008?

I made a mistake of not having a backup of my base virtual image for testing and back in the old days, I used the NewSID tool from Sysinternals. Unfortunately, I am not back in the good old days as the tool is only supported to run on Windows XP and Windows Server 2003 whereas I am working on Windows Server 2008. Although there are claims that it would work on both 32- and 64-bit Windows Server 2008, I am not going to spend another minute trying something that I have not validated myself (thanks to a new paradigm in business models) just for this project. My next best bet - SYSPREP. Unlike the days of pre-Windows Server 2008 where you had to find the Windows installation media, find and extract the Deploy.CAB file to you hard drive and then execute the SYSPREP.EXE tool. Not anymore! Widows Vista and Windows Server 2008 has it out-of-the-box (and you wonder why you needed all that hard drive space to install them) on

%WINDIR%\System32\Sysprep

One thing to note is that you need to check the Generalize checkbox to create a new SID for a cloned system

Reconfiguring your SQL Server disks - 2008 edition

This was from my old blog entry from way back November 2006 and remembered how easy it was to fool SQL Server when trying to replace disks. I was configuring a clustered SQL Server 2008 on a Windows Server 2008 and testing the disk resources for failover. I tried failing over one of the disks and it went out pretty well. The next disk resource that I failed over happened to be the one containing the system databases. To my surprise, it did not failover to the other node causing the SQL Server resource to fail as well. It took me quite a while figuring out that the disk subsystem is somehow corrupted (good thing it was just a test environment) so what I ended up doing was recreating a new disk subsystem and redefining it on my cluster using Failover Cluster Management. I made it available on my SQL Server 2008 resource. I knew I would definitely have downtime as I need to bring down the SQL Server cluster resource. Here's what I did - I brought the SQL Server resource offline, copied the contents of the original disks that contain my system databases to the new disk resource, changed the drive letter of the original disk to something else and rebooted the node that originally hosted the disk resource that is failing. Once it is up, I can reuse the same drive letter to the new disk resource which now has the original system databases. When I brought te SQL Server resource back online, it's as if nothing had happened (except, of course, for some errors in the Clustering error log).

Note that this is definitely not a substitute to a valid backup and restore process for your SQL Server databases. All I'm saying is it just works

Building a Windows Server 2008 Cluster Part 1

And so I was asked to blog about it. I have posted the details in here. This is part 1 of a 3 part series on building a Windows Server 2008 Cluster. Feel free to post comments and questions regarding this blog post as I am still working on the full details based on this TechNet article. If you are looking for step-by-step procedures on building a Windows Server 2008 cluster specifically for SQL Server 2008, check out this first in the series of articles I've written on how to go thru it from start to finish

Teredo what?

I bet you've encountered this error when running your Validate Cluster Configuration wizard in Windows Server 2008.

Verifying that there are no duplicate IP addresses between any pair of nodes. Found duplicate IP address fe80::100:7f:fffe%13 on node node1.domain.local adapter Local Area Connection* X and node node2.domain.local adapter Local Area Connection* X.

I've spent a bit of time trying to find out why the cluster validation report is displaying a duplicate IP address on my network when I only have like 2 static IP addresses for each cluster node. After digging thru a few blog and forum posts, I've found out that this was caused by the Teredo IPv6 Tunneling Protocol. This allows IPv6 communications to pass through IPv4 NATs and IPv4 servers. but gives an identical IPv6 address to its network interfaces. This is the one getting flagged by Failover Clustering as an error since it require unique IP addresses.

This blog post describes how to disable the Teredo Tunneling Protocol although when I tried the first option, I still get the error in the cluster validation report. After doing that, I tried disabling it in the Device Manager console as well. It did work after that

So you can't configure clustering in a Windows Server 2003?

You might encounter this error when adding a node in a Windows Server 2003 Cluster

Status: 0x800713de The quorum disk could not be located by the cluster service.

This occurs even if you have created shared disks on a node you wanted to add in a cluster but created it on the same SCSI bus as the disk that holds the operating system. Windows does not allow you to do that since if you are going to failover to the other node, the shared disk should be flexible enough to move. This is a common mistake when trying to create a cluster in a virtualized environment, assigning shared disks on the same SCSI bus as that of the disk that holds the operating system. Check out this blog entry on creating clustered VMs in VMWare Workstation 6. It doesn't matter whether you are using VMWare, Microsoft Virtual Server or Hyper-V; if you don't take note of this, you'll end up spending a lot of time troubleshooting a very simple configuration issue

Fixing PRIMARY KEY constraint issues in SQL Server 2005 Replication

I must admit that I had to stay away from replication for quite a while since most of the work I've done in my previous job has a lot to do with disaster recovery. Nonetheless, I'm presented with new challenges every single day which I need to face. I got notified for an error regarding a transactional replication issue between two SQL Server 2005 instances. The error went something like this

Replication-Replication Distribution Subsystem: agent INSTANCE-db-Publication-SUBSCRIBER_INSTANCE-21 failed. Violation of PRIMARY KEY constraint 'PK_Whatever'. Cannot insert duplicate key in object 'dbo.table1'

The error message simply says that replication between the publisher and the subscriber could not push thru due to an existing record in the subscription that is causing a violation of the PRIMARY KEY constraint. Either a record is manually inserted in the subscriber or its just an annoying application which is not properly configured. Here's how you can fix this

1. Open Replication Monitor and check for the publication that is causing the error. You should see some error messages in the details that display something about the transaction sequence number, publisher id and command id values
2. Next, you retrieve the command associated with the transaction sequence number using the system stored procedure sp_browsereplcmds. This will return a result set of the replicated commands stored in the Distribution database at the Distributor. The query should look something like this

EXEC Distribution..sp_browsereplcmds @xact_seqno_start = '0x000B5103000043B7000700000000' ,@xact_seqno_end = '0x000B5103000043B7000700000000',@command_id =2 , @publisher_database_id = 1
This will give you an idea on the possible INSERT/UPDATE/DELETE statement that is inside this transaction. Based on the error message, we are looking at either an INSERT or an UPDATE statement.
3. Next, run a SELECT query on the subscriber instance against the table specified in the subscription passing the values you retrieved from the sp_browsereplcmds system stored procedure.
4. If the record already exists, simply delete the record since it will be written over by the replication (of course, this is not always the case and would depend on your business requirements)

This is just an overview of what you can do when you see such errors in replication. There are a few more articles and tips to help you get by from this website by Paul Ibison