Microsoft SQL Server Migration Assistant for Access

Microsoft SQL Server Migration Assistant (SSMA) is a toolkit for quickly and easily converting Access Databases to full fledged SQL Server Databases.  Exactly what I was looking for to move my newly created 2010 Census Access Database to someplace more usable for my purposes.

Download the toolkit at:  http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=959

And thanks goes out to the Access Team over at the Access Blog for pointing me to the right place thanks to a wonderful Bing search.

Here’s the installation of that tool…

SNAGHTML25c80fd

SNAGHTML25cb97b

SNAGHTML25ce951

I have plenty of space and do not like to go back and reinstall things I need… so I’ll do a complete installation.

SNAGHTML25d6e48

SNAGHTML25da253

SNAGHTML25de0f8

SNAGHTML25e09ac

That’s it… checkout my next article where I describe using it to actually migrate Access data to my SQL 2008r2 server.

If you have any questions or comments, please leave them below!

Creating a 2010 Census Database

So you’re interested in the 2010 Census results.  Well they’re publicly available at http://www2.census.gov/census_2010/03-Demographic_Profile/ and with a little bit of work, you can have yourself a fully stocked Access Database with complete 2010 Census data.

Here’s a simple step-by-step using Microsoft Office – Access 2010 to create a database and import the data for the state( s ) you want to collect.  Later I’ll show you how to turn the resulting Access 2010 file into a SQL Database for SQL 2008r2.

Download the “DPSF2010_Access.accdb” file from the url above.  Also download the various .zip files under the states your interested in.  Then open up the Access file and perform the following steps as described in their documentation…

SNAGHTML1c746f7

SNAGHTML1c7c20f

We’ll first import the demographics data… so select the non-“geo” file.

SNAGHTML1c80833

SNAGHTML1c84a61

SNAGHTML1c88b95

SNAGHTML1c8bcd2

Select the “Data Part…” specification since this relates to the Demographics file.  When you repeat this wizard for the Population (geo) file, you’ll need to choose the other “GeoHeader…” option on this screen.

SNAGHTML1c9071a

The specs will automatically change to the correct format.

SNAGHTML1c931e2

SNAGHTML1c95e10

SNAGHTML1c98b37

SNAGHTML1c9adf3

This step is important… so specify the correct primary key.

SNAGHTML1ca1fa9

Name your table… I’m importing everything, so I’ll name it the state and attach Demo onto the end and Pop on the end of the geo file.  This will give me tons of data in two different tables for each state.

SNAGHTML1ca62b1

 

Have fun with data… if you have any questions or comments, please add it below!

Activating a BigFix Task

The Tivoli Endpoint Manager is a fantastic way of controlling your infrastructure from one central location.  One of the most basic skills is activating a task and direct it to do something on an endpoint.  Here is a step-by-step for activating a task to perform an action on an endpoint.

First find the task you wish to activate, in my example I will be installing a service onto one of my root servers.  Select the task to be activated and click the Take Action button…

SNAGHTML1e2b905b

image

Here is our targeting screen… Since I’ll only be installing this service onto one endpoint, I’ll simply select it out of the right side computer list.  I could just as easily choose the second radio button called “All computers with the property…” which allows me to target based on endpoint properties, or even “The computers specified in the list…” which allows me to type endpoint hostnames in one line per endpoint.  Note that the third option should be limited to <100 endpoints.  If you need to target more than that you should utilize the computer groups feature.

SNAGHTML1e2e5281

I’m very happy with the defaults on this particular task, however the Execution tab will allow me to start a task at a particular time, have it run between certain hours and even control the failure/retry activities of this task.  Try not to restrict these options to much… for example, you wouldn’t want to limit the run between to 10min since the larger your infrastructure the more difficult or impossible that will be to happen.

SNAGHTML1e308c84

In some cases your action will interact with end users and you may need to prevent the action from running if no user is logged in.  The following Users tab allows you to constrain the task to only run with certain users…

SNAGHTML1e33c060

Other cases you’ll want to present messaging to the end user or even allow the user to control the processing of this particular action.  Maybe you’ll allow the user to determine when the most convenient time for them to have a particular action occur.  This screen is used for that purpose…

SNAGHTML1e34db5f

Here we have the screen to Offer the user this optional action…

SNAGHTML1e35d759

What if your action requires a restart, and you want to allow the end user delay the restart till it’s convenient for them.

SNAGHTML1e35fb3e

Rarely will you need to change the Applicability tab.   Occasionally I find it necessary to alter the default behavior of an action on a one time basis.  This tab allows me to force the installation of something ignoring the default applicability relevance of the original task.

SNAGHTML1e37de6b

If I’ve modified the applicability relevance, I’ll need to modify the success relevance as well…

SNAGHTML1e385a1f

Lastly we have the ability to modify the default Action Script of this task.
SNAGHTML1e38dc78

Once you’re all done modifying the action… click the OK button at the bottom and you’ll be asked for your credentials.  (FYI:  This is no longer the case in v8.2 unless you upgraded from a previous version or you enabled this validation step)

SNAGHTML1e39ef0e

Our task is now activated and the action status window appears.  Here we can monitor the progression of our action to each of the endpoints… on the Computers tab we can see status details on individual computers.

image

 

If you have any questions or comments, please leave them below!

Tivoli Endpoint Manager (BigFix) Creating Custom Sites

Custom sites are an extremely nice way to collect related content together into one easily manageable group.  I am an avid believer in Agile software development and believe that BigFix is perfectly setup to support this methodology.

The use of custom sites is great way to organize content (IE: Tasks, Fixlets and Analyses) together and also allow for sub-categorizing using Domains… don’t forget to learn how to fill in the extra properties related to custom content.  They also allow for multiple non-master operators to work with content generated by other users including editing and stopping actions.

Creating a custom site is easy… here’s a simple step-by-step to do so:

SNAGHTML1e09615e

SNAGHTML1e0a6352

You’ll need to enter a short-name for your project.  In many cases I’ll just name it the vendor of the software… for example, I have published multiple free software under the name Moran IT, therefore it’s logical that my custom site be called “Moran IT”.  This could also be the name of your project… in one project I developed content related to the management of BigFix.  I called my project and my custom site “Core Infrastructure” since it directly relates.

SNAGHTML1e0ae923

I now have my custom site.. but I’ll need to add further detail like the description and put it into the correct Domain.  (FYI: For simplification, try not to put anything into the “All Content” domain.  You will benefit long-term if you avoid this domain.)

image

You’ll need to specify which endpoints are part of this project.  In some cases this might be all Windows computers, in others it might be all Windows computers with a certain software already installed.  You have very granular control over which endpoints are subscribed.  You can even use a complex relevance statement to subscribe only the computers related to the project.  I would encourage you to get very specific here since it will make it easier to not affect systems not part of your project.

SNAGHTML1e112c4b   image

The next most important part is specifying the operators that are allowed to view and manipulate your content as well as actions.  If you have a 5-person team of developers, with only one on-call at any one time… you’ll want to give all 5 appropriate permissions to this custom site.  If you get that 2am page, they can stop actions or activate troubleshooting tasks without engaging team mates in the middle of the night.

image

 

I hope you’ve come to realize the true value of sites in the organization of projects and developers… if you have any questions or comments please leave them below!

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!

Migrating BigFix v8.1 Root Server

In a previous article I described how to install the pre-requisites for BigFix v8.1.  In this article I’ll demonstrate the process for installing the root server and attaching it to a previously existing database.  I’ll be following the How to guide created by BigFix corp (KB133) to migrate my old BigFix physical computer to a freshly installed BigFix virtual computer.

I encountered some major problems with this process… so follow along but remember this is not a perfect migration.

image

image

image

image

image

image

 

I will be using this virtual as a root server only.  My web-reports service will be setup virtual to keep the installations and the processes of upgrading as simple as possible.  So… I unchecked the Web Reports option during the features selection part.

image

image

image

My SQL server is a separate system, so I had to specify the “Remote Database” option and on the next window give the details of connecting to the DB.

image

While specifying the SQL server, it needed to utilize the Computer Browser service.  This service was stopped and/or disabled on my virtual by default.

image

The service was disabled, so I will need to manually enable it so the installer can start it up for use.

image

image

image

image

image

Now, back to the installation…

hitting ok to the error, I hit Next again and answered Yes and all went well… we’re back on track…

image

image

image

image

image

image

image

image

image

image

image

I am utilizing a database that was moved from a previous instance of this license.  Thus many of my logins were already added to the database.  So there is no need to modify this…

image

image

Looks like during the startup process I ran into a little problem.  My FillDB service would not start.

image

Just finish up the install and cancel out of the diagnostics part, we’ll come back to it later.  (since filldb failed to start, expect to see lots of errors)

image


A quick look back at the Services MSC showed the Root Server also failed to start, however the Gather Service and GatherDB started just fine.  This points me to the either database connectivity or a permissions as the cause of their startup failures. 

First I’ll launch the 32bit version of ODBC to confirm connectivity with the database.  C:\Windows\SysWow64\odbcad32.exe

image  image

I don’t need to change anything, just want to test connectivity.  This function is on the last form and the “Finish” button is a shortcut there…

image

image

This shows that my DB connectivity is working through the firewalls that are configured… so I’ll ignore them.

image


Let’s look into permissions.  One of the most important permissions your service account requires is admin permissions to the server you’re running the root server on.  This will allow it access to the Program Files and other special directories.  Let’s start there… and test.

image

image

I don’t see my BigFix service user account listed, so we’ll have to add it.

image

image

image

There it is… now let’s try to start up our failed services… FillDB and Root Server

image


image

image

IT WORKED!!!  Excellent, next I started the Root Server which also worked.  Let’s turn our attention to validation of our installation.

image

image

image

So far so good… let’s take a quick look at our log files to confirm everything is functioning properly…

Two files I’ll be interested in is the BESRelay.log and the FillDBData\FillDB.log  these reside under the Root Servers path… “C:\Program Files (x86)\BigFix Enterprise\BES Server”.

Due to this being a pre-existing database these errors were expected…

image

I’ll first stop all of the services so I can complete step 9 on the How to guide (KB133).

image

Step 9 says to copy the following folders from the old server to the new server:

BigFix Enterprise\BES Server\wwwrootbes
BigFix Enterprise\BES Server\ClientRegisterData
BigFix Enterprise\BES Server\Mirror Server\Inbox

Using a little Robocopy magic… I’m done with this step in.. 2 hours!

Started the services back up and no go… grrrr what was up?

After a lot more research I came across KB215 which discussed the details of “Using NT Authentication with Domain Users or User Groups”.  On step 2 was my answer… my domain service account did not have SA authority in the database.

image

Added the required permission and restarted my services… and I’m done!

Let’s do a little bit more validation…


Validation Part 1

Back to the logs mentioned above… no errors… this is a good sign!


Validation Part 2

Visit http://localhost:52311/rd  for some simple server diagnostics like filldb status, actionsite status as well as downloads status…

image


Validation Part 3

Launch the BES Console, ensure you can connect into the DB and see computers checking in properly…

image


What I’ve learned from this process is pretty simple… you only need a few items in order to completely re-create your infrastructure if something bad happens.

1. (3) folders…
    a. BigFix Enterprise\BES Server\wwwrootbes
    b. BigFix Enterprise\BES Server\ClientRegisterData
    c. BigFix Enterprise\BES Server\Mirror Server\Inbox

2. Database

3. Masthead & auth keys (license.pvk and user keys)

4. Installer which is available at the BigFix website.

 

If you have any questions or comments, please leave them below!

Installing Tivoli Endpoint Manager (BigFix) Client 8.1.617

Continuing with my installation step-by-step series, I’ll be installing the BigFix client version 8.1.617.0.  This relates to my BigFix Personal Lab infrastructure. 
Get your own trial version of BigFix at the Corp website.

First locate your installer of the client and the corresponding masthead file.  In this step-by-step we’ll be using the EXE and not the MSI version of the installer.

I’ll be installing it onto my root server, a Windows 2008r2 server, however the installation process is the same for the EXE regardless of which Windows OS you’re using except for maybe the UAC dialogs.

image

image

image

image

I’ll be installing with the defaults, since I’m happy for the client to be installed onto the C drive for my endpoints.  However you may want to change the installation directory depending on company policies.

image

image

image

image

That’s all there is to it.  There are a few validation steps I’ll go over here which will give me the peace of mind that it is functioning properly.

Browse to the clients Logs directory, in my case its “c:\Program Files (x86)\BigFix Enterprise\BES Client\__BESData\__Global\Logs” and look for the todays dated log. 

image

You’ll be looking for two very important parts of this log.  The “Attempting to register” which should appear indicating the client itself is working and trying to get to the root server (or relay you specified).  And the “Registered with url” which shows us that it was able to talk to the root server (or relay) and has started the gathering process.

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.

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