Posted by

DanielHeth

Posted on

March 15, 2013

Posted under

Installation, Software, SQL 2012

Comments

Installing Microsoft SQL 2012

Like many of my step-by-step articles, it’s mostly for corporate level documentation purposes.

I won’t describe every screen, only those that include important decision points that affect my particular use case.

The above dialog is warning me that the firewall does not allow remote database connectivity. The below command line can be used to open up the SQL port. The cmd prompt must be opened with administrative permissions for this to work.
netsh advfirewall firewall add rule name = SQLPort dir = inprotocol = tcp action – allow localport = 1433 remoteip = localsubnet profile = DOMAIN

This is one of those major decision points. My purpose for this database is to house the newest IBM Endpoint Manager v9 database… nothing else. So I only require a few items to accomplish this simple task. The following items are needed for my particular use case:

Database Engine Services

Full-Text and Semantic Extractions for Search

Management Tools – Basic

Management Tools – Complete

In order to get IEM installed properly… an SA account is required. So I’ll configure the database authentication in “Mixed Mode” and specify a password for the SA account.

That’s all there is to it. Installations are almost always straight forward… but some corporations require complete step-by-step documentation, I hope this fits the bill.

Posted by

DanielHeth

Posted on

December 21, 2011

Posted under

Bing Maps, C#, Code, Microsoft, Programming, SQL 2008r2, Technology, Windows 8

Comments

Congressional District Polygons

Here I am again working on my House of Congress/Windows 8/Metro app contest when I’m wanting to show the individual districts each Congress person represents. Using a Bing search I found myself back at the Census website. This time looking at the congressional boundary files: http://www.census.gov/geo/www/cob/cd110.html

I don’t know anything about the e00 or shp files, so I’ll be working with the available ascii files.

Each state’s zip file contains two dat files. One dat contains the Long/Lat coordinates for various map-polygons which represent districts that are defined in the other dat file. What I wanted was a way to tie together the dat files with the Districts table I’d already defined at the start of my development.

I need this information to be in the most covenant format for my application, therefore, I’ll be importing the information for each state into my SQL database. I created a new table defined as follows:

Like many of my tables, I’ve setup an Id which auto increments and is the primary key for each row. Next we have the linked DistrictID, the PolygonID identified within the dat file as well as the Latitude and Longitude values.

First thing was to download each individual ascii file, unblock (windows 7 “feature”), extract and rename each file… only took 20min.

Then using my import program, I follow the following sudo code to get into my database:

foreach DAT file

    read in dat and def files

    extract state name //will use this later to get district reference

    

    open database

    find state

   

    foreach dat file line

        parse Long/Lat and cur Polygon # if available

        if cur Polygon # found, then

            foreach def file line

                if cur polygon # then

                    district id = line

        if have district id and current polygon

            insert new coordinates into database
In the end we have a database called Boundaries that looks something like this:

If you have any questions or would like to see the source code for my importer mention it in the comments section below.

Posted by

DanielHeth

Posted on

December 17, 2011

Posted under

Access 2010, Microsoft, SQL 2008r2, Technology

Comments

5 Comments

Migrating Access Data to SQL 2008r2

I recently spent some time building an Access database file full of 2010 US Census data… As an Access database, it’s not very useful since I want to use the data within as a feed for my Windows 8 Metro application. I’ll need to move this data into my SQL 2008r2 server to make it available to my application.

To do this I’ll be using the Microsoft SQL Server Migration Assistant (SSMA) toolkit I recently installed.

Upon first use, I’ll need to acquire a license for the tool as instructed by the included Readme file.

After filling out the form, they provide me a file which I will through onto a network file share and give to the tool…

ok, in the above pic, I didn’t need to specify the filename… just the directory it sat in… then the license imported correctly.

After specifying the database, I had to give it a minute or so to completely read in and load my database file… the 2010 Census file is right at 100mbs.

Then specify my SQL server, destination Database name, and my authentication method… I’m in a domain so I’ll simply use Windows Authentication since my Domain account has the appropriate level of access to the SQL Server.

Yes Please…

I do not have any pre-existing applications which connected to this Access file… thus no need to link anything.

Let the Migration begin!

At this point I could see the connection and the data migration was occurring…

back on the server, my tables are now created…

On the Migration Wizard, the data is slowly being copied over…

This is where my SQL server is running a tad slow… a lot of data being input into the database…

Wizard complete, and I can see the newly arrived data on the server.

According to the Migration report… there were Warnings… but no Errors. The Warnings mostly related to database quality… IE: some tables did not have primary keys, others had their primary key nullable value configured.

Those tables with nullable primary keys were corrected to not allow null values.

Those without primary keys were ignored since SQL server doesn’t consider that a serious problem…

That and I’m not worried about those two tables considering they were used during the “Import” process from the Census .dp files and won’t be used again.

It’s time to integrate the new found data into my Windows 8 application!

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

Posted by

DanielHeth

Posted on

December 16, 2011

Posted under

Access 2010, Microsoft, SQL 2008r2, Technology

Comments

1 Comment

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…

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

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!

Posted by

DanielHeth

Posted on

December 6, 2011

Posted under

Microsoft, SQL 2008r2

Comments

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.

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.

Stop Tivoli Endpoint Manager services

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

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

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.

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.

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

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:

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.

Nearly an hour and a half later…Attaching Databases

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…

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.

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

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!

Posted by

DanielHeth

Posted on

December 2, 2011

Posted under

Installation, Microsoft, SQL 2008r2

Comments

3 Comments

Installing Microsoft SQL Server 2008r2

I often find myself having to reinstall software. I liked the way I installed it last time, but over time something occurred that required a reinstallation. I don’t have instant recall on a lot of things… thus like much of my blog, I generate articles which assist me in the future. This article relates to the installation of Microsoft SQL Server 2008r2.

This outlines how I install this application within my personal test environment. If you have alternative ways I would love to hear about them in the comments section of this post.

Not all screen shots have comments, but a few do that require additional explanations.

I try to leave the firewalls on within my environment in order to promote good security practices. MS SQL warns me whenever the firewall is on and directs me to a website with details on SQL’s firewall requirements when accessing the server from other systems. (http://go.microsoft.com/fwlink/?LinkId=94001)

When I visit the link, I find I will need to configure the windows firewall to poke a hole for SQL. Read my SQL Firewall article for details on doing this.

All of my special services are configured with extremely limited domain accounts. Thus I needed to specify which account the SQL instances will run as. They are specified here.

I wanted to allow both Domain and Local logins for my SQL server. Thus I set it up with Mixed mode, configured an “SA” account password and added my domain account to the list of administrators of the DB server.

In the future I want to create a virtual SQL cluster. In order to pre-plan for that I needed to put the database onto a separate vhd drive. Thus I setup a separate vhd, mounted it via vSCSI and mounted it as the E:\ Drive. Now This step shows pointing the database’s “data” drive at this new E:\ drive location.

Now that the base installation is complete, I need to patch-the-hell out of it. A quick browse of Google and I find that SQL 2008r2 has a published Service Pack. I wrote a step-by-step guide for installing SP1 here…

Leave your comments or suggestions below!

Posted by

DanielHeth

Posted on

December 2, 2011

Posted under

Microsoft, Service Pack, SQL 2008r2

Comments

3 Comments

Installing Microsoft SQL Server 2008r2 SP1

Recently I needed up upgrade my SQL server to with the latest Service Pack. Here is my walk through for installing Microsoft SQL Server 2008r2 SP1.

I just wasn’t fast enough to capture their fancy progress bar on installing… so that screen was skipped.

There you go… easy peasy… leave your comments below!

Posted by

DanielHeth

Posted on

December 2, 2011

Posted under

Firewall, Microsoft, Security, SQL 2008r2

Comments

3 Comments

Accessing SQL through the Windows Firewall

Recently I installed a new instance of SQL 2008r2. (Get more details on installing Microsoft SQL Server 2008r2 here…)

Upon my arrival I quickly learned in order to allow applications to access the SQL server instance I needed to open up the following port on my windows firewall: 1433

So here goes…

The Microsoft article described a way to do this via an admin command prompt. I chose the graphical process.

If you have your firewall configured for outbound filtering as well, you may need to follow this process for under outbound filters as well.

Do you have an alternative way of configuring SQL for firewall access? I’d love to hear from you… leave your comments below with your process or comments/suggestions on my process.

danielheth

Tag Archives: SQL

Installing Microsoft SQL 2012

Congressional District Polygons

Migrating Access Data to SQL 2008r2

Microsoft SQL Server Migration Assistant for Access

Upgrading Servers with Microsoft SQL 2008r2

Installing Microsoft SQL Server 2008r2

Installing Microsoft SQL Server 2008r2 SP1

Accessing SQL through the Windows Firewall