Migrating SQL Data

In my current project I have found a need to migrate data from one SQL server to another. For those of you SQL gurus out there, you know that there are many ways to migrate data from point A to point B in SQL, even when different versions is involved.

Here's my setup

Server 1:

SQL Server 2005 Standard (x86)

Windows Server 2008 (x86)

Server 2:

SQL Server 2008 Standard (x64)

Windows Server 2008 (x64)

As you can tell, I'm upgrading versions and processor architectures. Surprisingly enough, this didn't seem to cause any issues for me.

Here are a few options one has to migrate SQL data between servers for those who don't find this post too useful.

1.) SQL Copy Database Wizard

2.) Detach, copy to new server, and reattach

3.) Backup database, copy backup to the new server, convert backup to a database, attach the converted database

4.) Create a database mirror

5.) Duplicate the database structure on server two and import the data from server 1

For my environment, only 1, 3, and 5 would work since the others leave more possibility for data integrity issues during the transfer or require that the SQL server be temporarily taken offline. I tried out a few of my options and decided that 1, the SQL Copy Database Wizard, was the best option. It's relatively straightforward and very efficient.

For the last three days I have been struggling with it because of what looks like permissions issues, though I can't be sure since all the error says is that step 1 was the last step to run and that the job failed (give me ambiguity or give me something else!). All that being said, I decided I needed to find a new way to transfer the data.

Through all of my troubleshooting I found quite a few SQL facets that I pieced together to get what I needed. Here's how I chose to migrate my 12 databases without too much trouble.

To start, I used SQL Server's remarkable "Script Database as" functionality to write out the query that creates the database, all of the columns, and all of their constraints. For mine I just copied the script to the clipboard to make compiling all of the scripts together much faster. To sum it up, I had SQL generate the queries for each database I wanted to migrate and I pasted them all into notepad so I could run them all at the same time.

After running all of the afforementioned queries to create the all of the database structures on your destination server we're ready to start importing data. Right click the database to import data to. Go to Tasks -> Import Data...

If you haven't done this before, you should receive a "Welcome to the SQL Server Import and Export Wizard" screen. Click Next.

Here we are at the "Choose a Data Source" screen. For Server name type the hostname of the server you need to migrate from (the source server). After that, select the database you want to copy to the new server. Once done with that, click Next.

On the "Choose a Destination" screen, type in the name of the server to migrate the data to. Next, select the Database you want to copy the data to (this should corrospond to the DB name on the source server since we ran the create queries generated by SQL). In my case, I was running the Import and Export Wizard from the server I was importing the data to so SQL server already had the Server name and Database filled out. Click Next.

In my case, I wanted to copy the entire database to the destination server, so for the "Specify Table Copy or Query" screen, I elected to "Copy data from one or more tables or views". Click Next. On the following screen, check all the database tables you want to be copied (or just check the box at the top left for all of them).

Nearing the end the "Save and Run Package" screen comes up. If you don't need to save the package to be run later or again at a later time, just leave Run immediately checked and click Next. Finally we review our settings and what will be copied. If everything looks right, click Finish. Once the transfers have completed, click Close. If any transfers failed or threw a warning, you can click the "Messages" text next to the table that did not succeed to see the log entries about it.

Repeat the aforementioned steps until you have migrated every database you need.

Converting Hyper-V VHDs

I recently was assigned the task of rebuilding our Team Foundation Server with TFS 2010 for many reasons. One of those is because the old one has a VHD that is consuming far more resources than it should be (it's a 100 gigabyte vhd and only 8 gigabytes are in use). I seemed to recall somewhere that Hyper-V could "compact" a virtual hard drive, but I couldn't remember where. After doing a bit of searching around Hyper-V, I found what I needed.

Here's a few facts about this before getting started.

First, shrinking a hard drive only applies to Dynamically sizing disks. Since these do no shrink on their own (there's a lot of reasons why) but only grow, they might need to be compacted later to free up host space. It only reduces the .vhd file size by shrinking the 'shell' (if you will) to take up the drive space that is not being used by the guest OS.

In my situation, I was dealing with a drive that was not dynamically sizing but was static. If the vhd is static, the Compact button will not show up when you go to edit the drive.

In my case, I did not have to compact the drive. As I said, a drive cannot be compacted unless it is dynamic. Since mine was static, I converted it to dynamic to regain the compacting functionality but because of the way the conversion process works, it automatically 'compacts' the .vhd. My original static .vhd was 100 gigabytes. The output was 15.5 gigabytes.

Though I did not have to compact my .vhd because the conversion process did it for me, I'm going to put the instructions on how to compact the .vhd anyways.

For starters, the virtual machine that the hard drive is attached to must be turned off. Once the server is offline, from within the Hyper-V Manager (it's an mmc snap-in) go to the virtual machine's properties (right click the machine and select properties). Select the drive you want to shrink on the left panel that lists the various devices attached to the virtual machine. After selecting the drive, on the right panel, select Edit. This will bring up a window that tells you what editing a drive does and gives you the option to not show that screen again. Click Next. From here you should have three options (unless the .vhd is static). Select Compact and click Next. Finally, click Finish and Hyper-V Manager will shrink the .vhd as much as it can.

Team Foundation Server 2010 Beta 1 Configuration

After finishing my last post on the installation of Team Foundation Server 2010 Beta 1, I closed down for the day and went home (now now, no judging...that was a long post). Today I'm back to go over the configuration of TFS 2010 Beta.

Configuration of TFS 2010 Beta 1

If you're coming to this blog post from my last one on the installation of TFS 2010 Beta, you will have just restarted your server and the Team Foundation Server configuration screen should be up. That's where we'll be starting here.

At the first configuration page, you must decide which confiruation path you want to take. The descriptions that the page gives are quite helpful in deciding which path to take since they give "You want to use this if..." and "You don't want to use this if..." sections for each option. For my purposes (Single-server installation with Sharepoint not installed yet and using the default instance of SQL Server), the Default Configuration will suffice. Click Next.

The next page of the configuration wizard (if Default Configuration was selected) simply indicates that some tests will be performed on the server to determine if certain pre-requisites are met for a proper configuration. Click Next.

*NOTE*

Here you have the option to click review OR next. If you click review before clicking next, you will most likely get [invalid] for Report Reader Account and the SharePoint Service Account since the next screen prompts for the service account's username and password

Here you are prompted for a service account. This account will be used as the service account to run Windows Sharepoint Services (WSS) and SQL Reporting Services. For my instance, I created a domain user account called TFSWSSService (creative, yeah?). If you want, you can click Test to confirm that the username and password work. After typing in the requested username and password, click Next.

Here the configuration wizard runs tests on your server to confirm everything is installed properly. The first time through, I received a warning on the first test because my firewall was disabled (I talked about that in the last post regarding installing TFS). Since we're all learning here, I elected to re-enable my firewall so I could have the problems that accompany closed firewall ports (the more problems you have, the better you get at troubleshooting the given system, right?). Click Next.

Here's where the real fun begins...if you can classify sitting around for about 20 minutes watching a looping progress bar as fun.

Once the configuration is complete and assuming you had no errors or warnings on previous screens, you should seen a screen that says what we all love to exclaim at the end of a long project...SUCCESS. The great thing is that if you click Next, it continues to say Success as well as telling you where you can find the configuration log. How considerate. Click Close.

That's it for now. If you followed my last post on the installation of TFS, you probably noticed that I installed Team Build as well as TFS. My next post will be on the configuration of Team Build.

Thanks for reading.

Aaron Ball

Installing Team Foundation Server 2010 Beta 1

The installation of Microsoft's latest beta release of Team Foundation Server 2010 has apparently been quite a hot topic in IT lately. My Twitter page isn't that popular and when I first started tweeting my progress, shortly after I was receiving messages regarding my documentation and progress. Here is the shortly awaited documentation on what I've done to install TFS.

Here's what my environment looks/will look like:

- Windows Server 2008 Enterprise (with hyper-v but that's irrelevant)

- SQL Server 2008 Standard

- WSS 3.0

- Sharepoint 2007 Enterprise

- Team Foundation Server 2010 Beta 1 (of course)

There are a few ways this environment can be changed around (which versions such as Enterprise or Standard can be used). Check out Microsoft's TFS installation documentation for this information (a convenient .chm file...check my blog post on this if you have issues opening this file).

Additionally, this post documents a single-server installation on a 64-bit machine.

Here's a summary of the order of software installation that will be taking place.

1.) Windows Server 2008 (we have to have an operating system)

2.) IIS 7

3.) SQL Server 2008

4.) Team Foundation Server 2010 Beta 1 (with WSS 3.0)

If it isn't obvious yet, this is going to be a long post. I've taken screenshots of many of the steps which won't help the length. Is everyone buckled in? Good. Let's get started.

Installing Windows Server 2008

The install of Windows Server really isn't that complicated. There are not special requirements for this. However, post-install, my server was added to a domain so I could use domain service accounts. It isn't necessary to have the server added to a domain though.

Installing IIS 7.0

This part really isn't too bad (thankfully). In the server management, on the left click Roles. On the right, click Add Role Services.

On the window that comes up, at the bottom check Web Server (IIS) and click Next.

Here some default values are selected. You need to add HTTP Redirection, ASP.Net, Windows Authentication, and IIS 6 Management Compatibility and all subordinate check boxes. Click Next.

Here your selections are reviewed. If everything is correct, click Install. Once the install has completed, you'll see another review window. If everything was successful, click Close.

Installing SQL Server 2008

Here's where the real fun begins. This install isn't too bad. The real ticket is to know if you have any service accounts you want to use for the SQL services. Since I did a single-server install on a server dedicated to my team, I used NT Authority/Network Service for mostly everything, but I'll get to that a little later.

First off, insert the SQL installation media (I used an ISO file mounted through Hyper-V for mine).

From here, run the setup executable on the disk. Mine did an autorun. From that window, I selected installation on the left navigation pane. On the screen that loads on the right, select New SQL Server stand-alone installation or add features to an existing installation.

The screen that comes up will run five tests. If your installation is clean than most likely all five will pass with a green check mark. Mine threw a warning to me on Windows Firewall because mine was turned on with default settings. Since the server is behind two firewalls, I elected to disable the Windows firewall completely. Re-running the test after that resolved the warning. Click Okay.

Here we are at the obligatory Product Key screen. Enter your product key and hit Next.

If you accept the license terms (License Terms page), check the box and click Next.

The next screen wants to install setup support files. Go ahead and click Install to continue. The following screen will yet again, run some more tests (eleven to be precise). Again, if this is a clean install, every test should pass.

Here's one of the crucial screens: Feature Selection. On this screen, you should select to install Database Engine Services (for TFS), Full Text search (for reporting), Reporting Services (for reporting), Analysis Services, Client Tools Connectivity, and Management Tools Basic. Once those are checked, click Next.

Hit Next to continue to the Instance Configuration screen. If you want to, you can rename this SQL instance to whatever you want it to be. I chose the default MSSQLSERVER since it will be used for TFS only and nothing else will be connecting to it. Click Next.

Click Next on the Disk Space Requirements screen.

Here we are at the Server Configuration section. Unless you have any specific domain accounts set up for running SQL, NT AUTHORITY\Network Service will suffice for all of the accounts listed. No password is required to use this username. Also be sure to change SQL Server Agent to start up automatically (by default it is manual). Click Next.

The Microsoft documentation suggests on the Database Engine Configuration page that Windows authentication mode be checked. I have had some pretty nasty experiences with this in the past and selected Mixed mode authentication. Following this, you need to type in a password for the SQLSA user. Also, don't forget to add all of the users you want to have access to the DB engine. Once you're done with that, click next.

The next page is the Analysis Services Configuration page. Add any users you want to have access to the analysis services that your SQL instance will supply. Click Next.

On the Reporting Services Configuration page, select Install the native mode default configuration. Click Next.

Here's the obligatory Error and Usage Reporting screen. Check whether or not you want Microsoft to receive anonymous usage statistics regarding your SQL instance and click Next.

Nearing the end, click Next on the Installation Rules screen.

Finally, we're at the Ready to Install screen. Review the features to be installed and click Install.

Once the installation is complete, click Next and Close.

Installing Team Foundation Server 2010 Beta 1

Here we are at the final software install. This part really isn't too bad (surprisingly enough).

To begin, insert the installation media (once again, I mounted an ISO through Hyper-V). If autorun is enabled, a standard window should come up giving you the option to explore the newly inserted media. If this does not happen, just open up Computer and navigate to the disk.

Inside the disk there are three folders. Depending on your processor architecture, choose either the TFS-x64 or TFS-x86 folders. From within that folder, run the Setup.exe file.

Here we're at the first screen. Click Next to proceed.

Once again, the ever-present Licensing Terms page. If you accept, check the box and hit Next.

The Microsoft documentation suggests that only Team Foundation Server be checked. I actually need the build server to be on the Team Foundation Server as well so I checked all three. Either will work though. Click Install.

During your installation, the server will need to be restarted. Click restart now. Upon restart, the configuration option will become available to you.

Pat yourself on the back. You just installed TFS. This is a long enough blog post for now. I'll post here very shortly the configuration steps I took for TFS (still haven't taken all of the screenshots I need for it).

Thanks for reading.

Aaron

Opening CHM Files in Vista

If any of you have been following me on Twitter recently, you know I've been researching the installation of TFS. I had pretty good luck with a lot of websites regarding TFS 2008, but not so much with 2010 Beta 1 (naturally). The only source for detailed documentation was Microsoft, so I went with what I had and downloaded the document. If you hadn't guessed yet from the title of this post, the file was a .chm extension.

After downloading the .chm file, I proceeded to open it. Much to my dismay, it opened but the pages would not load. I was getting 404 errors on every topic. After spending a few hours searching, I found a solution. Apparently the .chm extension is not secure and Vista removed the ability to read files like that unless they are in a specified 'safe' location. I don't particularly want to have to download any .chm file to an obscure location and then have to find that location again to open the file. Naturally, I searched for a simpler solution.

The solution I found, simpler or no, worked. You guessed it...it's a registry hack.

I have a theory that one can literally do anything with registry hacks...things like make pigs fly or make hell freeze over.

Here's what to do:

1.) Right click your .chm file and select Properties

2.) Click Unblock and select Apply

3.) Open up Regedit (Windows Key + R and type regedit)

4.) Navigate to HKEY_LOCAL_MACHINE\Software\Microsoft\HTMLHelp\1.x\ItssRestrictions

5.) Modify the DWORD MaxAllowedZone to equal 1 (if it doesn't exist, create it)

6.) Close all Internet Explorer Windows and start up your .chm file

That's all there is to it. I haven't had any problems with any .chm files since.

The next post I have coming up (already have most of it written) is on the installation of TFS. Brace yourselves...

Aaron