Putting SQL Server on the MAP

  • Article

Usually when I mention SQL Server and map in the same blog it’s a post on geospatial data but not today; today I want to talk about assessment and planning your SQL Server infrastructure. 

I know of several DBA’s who don’t know how many SQL Servers they have much less what stare they are in i.e. which patches and fixes been applied and which components have been installed.  This is not there fault it is often the case that new applications get installed that require SQL Server without the DBA knowing.  For example I met this business user at an even who works for the atomic weapons inspectorate and he had to get special permission to install SQL server as it’s not a standard and he would be the first to use it.  Once he got approval and turned on the client tools on his desktop to look for his new server he was amazed to find 50+ instances already out there.

So how do you get a grip on this,  there really isn’t anything out of the box in SQL Server itself , you can get clever with dynamic management views (dmv’s), and policy management (if you have SQL Server 2008) but that’s about it. You are in a good place if you have System Centre Configuration Manager (SCCM) or a similar tool form a third party.

However this also a free alternative the Microsoft Assessment and Planning (MAP) Toolkit, which has a special version for SQL Server as well as for other products like Windows Vista, 7 Server 2008 and 2008r2 and for initiatives like security and Green IT.

MAP is designed to be used as part of a consolidation or upgrade/migration exercise, but there’s nothing to stop you using at regularly, though I would advise always downloading the latest one.

The tool gives you a detailed  assessment spreadsheet of what’s on your server with a tab for each instance …

  image

with columns for everything form is clustered to service pack details, operating system and hardware.  You also get another tab with all the components on:

image

I can’t think of another simple way of getting at this so easily and quickly for no money.  You also get a proposal document, which you can wave under your managers nose should you actually use this for its intended purpose of assisting in the planning of a consolidation and upgrade project.

MAP uses WMI and so it doesn’t matter if SQL Server has been configured to use non default ports (which of course is best practice).This does need to make sure that your servers have some ports open for this:

  • TCP port 135  - remote administration. If you have another host firewall installed, then you will need to allow network traffic through this port.
  • TCP ports 139 and 445, and UDP ports 137 and 138 – File and Printer sharing.

All of the gathered telemetry is stored in a SQL Server (naturelment)  express database on the machine you install the MAP toolkit on.  MAP can use another edition of SQL Server for this but you must create a specific instance called MAP (yes instance not database) so I don’t see the point.  What you can do is to backup  the created database.  There is a file SupportBackup.cmd in the “\Help\Support Files” subdirectory of the MAP installation folder in Program Files. Run SupportBackup.cmd without any parameters to get help. If you’re using Windows Vista or Windows Server 2008, be sure to run the command prompt using “Run as Administrator.”

Pass the name of the database you want to back up to this batch file. After the backup completes, a new folder will be created in the Support Files directory with the same name as your database. In that folder, there will be a file called databasename.bkp. This is your database backup. Copy this file to the machine you want to restore the database on.

To restore a database: Use standard restore T-SQL or management studio to where you have SQL Server already installed.

On final thought this will also play across virtual machines, and report what resources the virtual machine has been given,even if this isn’t using Microsoft’s virtualisation (Hyper-V).  There is a specific toolkit for Hyper-V as well which is there to help you plan your server consolidation irrespective of the workloads they are running.

 

Technorati Tags: Microsoft assessment planning tool,SQL Server,Hyper-V,consolidation,migration,upgrade