Bit of a double header on this one!

I use the Microsoft assessment and planning toolkit (http://www.microsoft.com/en-gb/download/details.aspx?id=7826) to find any sneaky installs of SQL Server and upload our environment to the licensing team. MAPT installs a localdb install of SQL Server to house its results. I wanted to use the results to check against my Registered servers and see if im missing any servers (using a bit of SSIS action). With that I needed access to the database remotely. which means I needed off of the localdb and onto a bigger edition of SQL.

Install MAPT using pre-installed SQL Server

 Doing a rather quick read on the MAP Q&A (http://social.technet.microsoft.com/wiki/contents/articles/1643.microsoft-assessment-and-planning-toolkit-frequently-asked-questions.aspx) All you need to do is set up a named instance (MAPS) and use the “SQL_Latin1_General_CP1_CI_AS” collation on the server / workstation that will house the MAPT software.

Note when you install MAPT it will stay its installing localdb, but if you look on your MAPS instance you will see new databases!

Part one solved.. Onto part two.

Changing Instance Level Collation

After the install I noticed I had the wrong collation! As I didn’t have anything installed on the instance, it was a rather quick process to fix (Heck I still had the install CD ready to go!).

To change the collation of a SQL Instance you need to do the following:

 (***** Note if you have User Databases you will need to follow a much longer process! http://technet.microsoft.com/en-us/library/ms179254.aspx ***)

1: Open the Command Prompt with Administrator permissions

2: DIR to the root of the SQL Server install CD (Or where ever the setup.exe file is placed if its on a network directory or local drive)

3: Run the command:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MAPS /SQLSYSADMINACCOUNTS=sa /SAPWD= Pa$$w0rd /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

 

Part two solved! Not my brightest moment but a lesson learned…

Advertisements