Upgrading Servers with Microsoft SQL 2008r2

I have a server whose hard drive is failing and unable to get an image of the hard drive.  This particular server is one of the most important on my network as it is my SQL database server and links to multiple other services:  Microsoft Team Foundation Server, Microsoft Sharepoint, IBM Tivoli Endpoint Manager (BigFix), among several of my personal developments.

Since I have upgraded my virtual host server I plan on running my new SQL server as a virtual  (see: 1, 2, 3) .  I’m assigning 4 cores and 3 vhd files running from multiple hard drives within my virtual host server.  This should serve my applications well.  I’m now left with the problem of migrating my databases from the old failing server to the new one.

The first thing to do is stop all services that rely on the old SQL server. 


Stop my custom vGPS website

First up, my custom virtual GPS website… This website provides an easy way to take someone’s public IP address and performs a database lookup, then returns the previously recorded GPS coordinates.

image


Stop Team Foundation Server

Next up, my Team Foundation Server… here I plan on just shutting down that virtual.  During my migration process I plan on changing the DNS name for my old SQL server to point to the new SQL server.  Thus when TFS starts back up, it shouldn’t see anything change.

image


Stop Tivoli Endpoint Manager services

Finally I’ll need to disable my BigFix infrastructure by stopping all of the related services.

image

I had to stop my BES Client also due to the policies I’ve put into place which monitors for stopped/failed services and restarts them.


While I’ve been working documenting what needs to be stopped, I’ve also been preparing and updating my new virtual SQL server.  Windows Updates, attaching to domain, installing SQL, etc…

Visit my “Installing Microsoft SQL Server 2008r2” for details on how to setup your own SQL server.

Now we’re ready to, backup/detach/move/attach our databases.

Backup Databases

First let’s backup each of the databases that will be migrated over:

Open up SQL Mgmt Studio

12-2-2011 2-13-44 PM12-2-2011 2-14-45 PM12-2-2011 2-15-42 PM

Open up the localhost->Databases and right click on each database->Tasks->Back Up.  The default location should be fine unless your shy on space.  If so, remove the destination at the bottom and redirect to a different location.

image  image

image

Repeat until you have all of the desired databases backed up and safe.

Since I went with the defaults, all of my database backups show up in “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup”.  I will leave them there for now and utilize them if something goes wrong later on.


Detach Databases

Next we’ll need to Detach these databases from the old SQL Server.

imageimage

The database files will remain where they were.  In my case I had them configured to sit in the default directory:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

This process will detach the database from this instance of SQL server and locks the two related files:  [database name].mdf and [database name]_log.LDF

image

I will repeat the detaching process until I get all 10 databases.  1 for my custom vGPS application, 4 related to BigFix, and the 5 related to TFS.

I now have 20 files to be moved over to the new SQL server.  I’ll do this using robocopy.  See my Robocopy how-to in a different article.

I’m going to move them straight over to the new SQL servers data drive… so I used the following robocopy command:image

robocopy ./ "\\vsql\e$\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA" -z –e

After the move, I wanted to position the transaction log files in a different “drive” than the database files… so I selected all of the _log files and moved them to a separate VHD which sits on a different physical RAID array than the OS and Data VHD’s.  This is due to performance reasons related to BigFix.

image


Nearly an hour and a half later…Attaching Databases

image

image

image

image

image

I encountered a SQL error 5123 while selecting my mdf file.  This is because the DATA directory is protected. I solved that by simply exploring to that directory and when asked for “admin” access to it, clicked yes.  Then choosing OK to the above dialog went as expected…

image

Since my Data and Log files reside on two different VHD drives, I needed to specify the path to each file separately.  The previous file selection automatically chose the correct Data file, and since my path matched between the Data VHD and the Logs VHD, I only needed to change the Drive letter.

image

image

Next, repeat that for each of the databases that are being moved over…

image


Transferring Logins

I opted not to perform this and simply recreate the couple of logins I needed.  Since only 3 services are currently using the SQL Server, recreating them was quick and extremely easy.

I also opted to change the DNS alias the three services were connecting to.  On my old server, both BigFix and SQL were running on the same box.  I decided to separate them into two separate virtuals.  vSQL will host my SQL server and allow all three services to connect into it.  vBigFix81 will serve as my BigFix server running version 8.1.  If I want to upgrade to 8.2 or beyond, I’ll simply create upgrade vBigFix81 to get upgrade documentation, then create a brand new vBigFix82 virtual, install 8.2 and change the BigFix DNS alias.

Now onto restoring each of my services…


Restoring Team Foundation Server

It made logical sense to restore this service first.  My vGPS service will need alias’s updated in code which is stored on my TFS.  So…

Turns out this is extremely easy.  I only needed to update the web.config file under “C:\Program Files\Microsoft Team Foundation Server 2010\Application Tier\Web Services” with the new DNS alias for my SQL server.  I found information on this from http://msdn.microsoft.com/en-us/library/ms404869(v=vs.80).aspx which pointed me to the Web.config for other reasons.  While there I remember for many of my apps, I have to configure the database connection within this file.  And done!

I was able to do that LIVE and the connection automatically repaired the db logins needed and established connections to the newly attached databases.


Restoring vGPS my Custom App

This is extremely easy as it was a simple matter of checking out my code from the TFS server, do a crtl-h to replace the alias, save and publish.  Testing showed my DB user account wasn’t configured right, so I made the appropriate adjustments and all done.  App works perfectly with the new SQL DNS alias.


Restoring BigFix

This one I take my time on because I really enjoy installing, customizing, and playing around with my companies program.

I created a brand new virtual 2008r2 server.  Thus I had to do the usual build the virtual, install os, patch os, install pre-requisites, and migrate BigFix v8.1.

 


Now that my three services have been restored and validated… the upgrade of my Microsoft SQL 2008r2 SP1 is now complete!

Please leave comments and questions below!

Attach Windows 8 to a Domain

My personal test lab has a fully functional Windows Domain in order to properly test my applications for corporate networks.  As a result, I require all of my windows boxes, when possible, to be attached to my domain for further testing purposes.  Since I’ve just added a few Windows 8 boxes, I had to “learn” the attachment process all over again.

image

image

image

image

image

image

image

image

image

image

image

image

image

After the system reboots, I’m presented with the customized disclaimer message I configured for my domain users… which was the expected results.

image

image_thumb[16]

Now that my system is attached to the domain, I’ll want to log in with my Domain account…

image

image

image

image

Then I’m presented with the new default windows 8 screen…

image

But let’s say I want my domain account to be linked to my Windows Live account.

image

image

image

Warning… the tab button does not work on this user/pass screen… grrrr!

image

image

image

image

 

Please leave your comments and questions below!

Installing Windows 8 without a DVD Drive

image

I just completed downloading the Windows 8 Developer Preview with tools and have a tiny problem.  Well, not so tiny… all of my blank DVD media is 4.7Gbs in size, however the Developers Preview with tools tops the 4.8Gbs mark.  I was searching high and low for a larger blank DVD disk when I remembered a previous article from a few years back where my Netbook didn’t even have an optical drive and I turned to a USB thumb drive to save me.  Time to do it again!

 

1. Format USB flash drive to NTFS format

Format the USB drive to NTFS format

formatL

2. Disk-Part the Drive

On the Start menu, navigate to the command prompt entry. Right-click and select Run as administrator.

startmenu

Type

diskpart

list disk

Record the disk number of your USB flash drive.

diskpart

Select the USB disk.

select disk X (X is the drive number of the USB flash drive)

List the current partition. Record the partition number.

list partition

Select the current partition and make it active.

select partition Y (Y is the partition number of the USB flash drive)

active

diskpart2

3. Prepare the OS files

Download Windows 8 Developers Preview ISO to your desktop.

Download and install PowerISO from http://poweriso.com/.

Right-click on the Windows 8 iso file and select Extract files. Extract the files to a folder (you can name the folder any name you want, but for illustration purpose, I name it win-8) in your desktop.

image

In your command prompt, cd to the windows 8 folder where you extracted the files.

cd Desktop/win-8 (Change the destination to the folder that you have extracted)

cd boot

bootsect /nt60 X: (X is the drive latter of your USB drive )

Now, copy all the files from the Windows 8 folder to the USB flash drive (of appropriate size of course, I’m using a Patriot 8Gb purchased for cheap off NewEgg.

Reboot the computer. Remember to change the first boot device to your USB drive in the BIOS.

You should be able to install Windows 8 from your USB flash drive now.

My first experience with Windows 8

Not so good…

Launching the News tile left me trapped. The Done button does not do what I’d expect… which is to close the news feed and return me to the Tiles screen.

image_thumb[35]

A classic Crtl-Alt-Del allowed me to access the task manager where I was able to kill the news feed.

image_thumb[36]

image_thumb[37]

While I’m here… let’s look at the improvements of one of my favorite tools… Task Manager.

After a tad bit of research, and button pushing… I found that closing out of any of the tile’d apps in Windows 8 was easy… push the Windows key on your keyboard and your back to the “Start” or tile screen.

(This forum was helpful in finding the information I needed!)

 

Leave your comments and questions below!

Installing Microsoft Windows 8 Developers Preview

I have gotten ahold of the Developers Preview of Windows 8 and figured you guys would like to see the installation.  This is the basic 64bit version.  I’m currently downloading and will be installing the 64bit with developer tools onto a physical box I just freed up for this purpose.  More on that later…

After watching “Microsoft Reimages Windows, Presents Windows 8 Developers Preview” I was extremely excited to get started…

I’m setting Windows 8 up on a Hyper-V virtual.  I’ve also allocated 80GB vhd for this particular virtual which grows as needed.  For this initial installation I’ve given the Virtual 2gigs of memory until installation is complete, then I’ll probably switch it to dynamic memory like my other virtuals.

image  (started at 11:55am)

image

image

image

image

image

image

image

Reboots…

image

Switches to graphical boot mode at this point.

image

image

Reboots…

image

image

Almost caught that opening screen… it’s a little faded but this is what is displayed just before the personalize screen.

image

image

image

I keep very close tabs on bandwidth leaving my home… so I never signup for the sending tracking and usage information to anybody… thus I’ll chose the Customize option.

image

I try to limit any sharing of anything from workstations due to the servers running within my environment.  If I want to share files, I’ll create a share on a server not the workstation.  Only reason I access workstations is for administrative purposes so I’ll just use the hidden $ shares.

image

I know a moment ago I mentioned keeping close tabs on my bandwidth, however I believe security patches are the most important thing to allow.  Thus I’ll allow the system to automatically download and install important patches.

image

I turned most of these off, however one is locked down probably due to the developers preview and their desire to receive feedback on that.

image

image

image

It’s connecting to the online resources for Windows Live now… then asks me to login.

image

image

image

image

image

image

After a few minutes, Windows 8 is now loaded.

image (completed at 12:24pm)

Clicking the 3rd row 1st icon gave us our classic Desktop view…

image

Clicking the start menu brought us back to the Tiled start screen.

Also turns out they’ll send you a confirmation email asking you to validate the connection of your new Windows 8 computer to your Windows Live account…

SNAGHTMLde96bd5

image


I started using windows update to get the very latest fixes and rebooted… I was welcomed by the following login screen:

image

A quick click/drag-up and I’m back to the login screen:

image