SQL Server

How to move your VMware vCenter Server database (SQL)

by

 

My vSphere 5 lab at home had been feeling a little sluggish when it came to running the vSphere client and working with vCenter – I could see that the drive where my vCenter DB was running (SATA) was taking a bit of hit when I accessed Performance graphs and the like from vCenter. I had a little bit of free SSD storage on another drive (OCZ Vertex 2 SATA drive) that would be perfect for my vCenter DB size, so in the search of better performance in my lab, and a bit of a practise run at moving the vCenter SQL database, I set about moving just the SQL databases on the VM to a new, dedicated SSD-based VMDK. This also meant that I didn’t take up too much space moving the entire VM across to SSD storage, as this kind of storage comes at a premium!

 

This post will cover the process I followed for SQL Server 2005 Express. Most labs environments are likely to be running this edition, especially if you upgrade the lab from vSphere 4.1. The steps are quite similar for a SQL 2008 database, but there are differences, so just make sure you follow the correct KBs if you are on a different edition of SQL Server. Here is the high level overview of what was involved in the whole process.

 

High level overview: vCenter 5 steps on SQL Server 2005 Express

 

  • Shutdown lab and make a full clone of the vCenter VM. Power back up again afterwards – Always good to have a rollback plan!
  • Added a new disk to the VM, located on my SSD-based storage.
  • Backup all my SQL databases on the vCenter VM along with the System databases.
  • Noted down all credentials that vCenter uses to connect to the SQL database and checked I was familiar with all my ODBC settings just in case any of these needed changing or updating.
  • Stopped vCenter and VUM services
  • Performed database move steps carefully, verifying everything each step of the way.
  • Started vCenter and VUM services back up and check all was working as expected.
  • Note that there are some additional considerations if you are planning on moving a vSphere 4.x database. Refer to the VMware KB linked below for more info if you are on vSphere 4.x

 

VMware have a fairly high-level KB on moving your vCenter Server SQL database. You can take a look at it over here to see if you need anything else.

 

The Process

 

  • After making a clone of my vCenter VM, backing up all my SQL databases on the vCenter server, and stopping all my VMware specific services, I started with the Microsoft specific steps for moving a SQL 2005 database.

 

  • First off, you need to detach the VIM_VCDB database. Execute the following SQL query in SQL Server Management Studio:

 

use master
   go
   sp_detach_db 'VIM_VCDB'
   go

 

  • After this query completes successfully, move your VIM_VCDB.mdf and VIM_VCDB.ldf files to the new location (where you are moving the database to). Once moved, go back to Management Studio and execute the next query which will reattach the database. Of course you will need to specify the path your database is now going to be located in – the example below references the path I used.

 

use master
     go
     sp_attach_db 'VIM_VCDB','D:\VCDB_SQLDATA\VIM_VCDB.mdf','D:\VCDB_SQLDATA\VIM_VCDB.ldf'
  go

 

  • After this query is successful, you can run the next stored procedure, which should return the new location of your database, provided it has been moved and reattached correctly.

 

use VIM_VCDB
go
sp_helpfile
go

 

  • Now that the Database has been moved, you should be good to start your vCenter services back up again and do some testing to ensure everything is working as expected.

 

Extra steps

 

If you would also like to move your System databases, things get a little more complex. First off, you will need to make sure your management studio is set to only open up a SQL query window on startup. (Tools -> Options -> Environment -> General ->At Startup -> Open new query Window) this is so that when we enter single-user mode for the SQL Server service (part of moving the System DBs), we don’t get errors trying to run our scripts – as by default SQL Server Management Studio tries to make a SQL connection for it’s object explorer, as well as for your query when you execute it. This means you’ll get an error message as there would already be an active SQL connection using the Object Explorer before you can execute your queries to move the System DBs. The Microsoft KB does not explain this, and it took me a little while to realise that this was the problem, so don’t forget to change this before starting! The rest of the steps can be followed through in this MSDN Article on moving SQL Server System Databases. Just make sure the correct edition of SQL Server is highlighted at the top of the page before you begin.

 

The System Databases include:

 

  • Model
  • MSDB
  • Master
  • Resource
  • TempDB

 

Another tip when moving the System databases that was not mentioned in the Microsoft KB article, and will cause you to get stuck unless already configured, is to set the correct permissions on the new folder your SQL System databases are going to be sitting in. There is a local security group on your SQL server that needs to be assigned “Full Control” on your new System database location. If it doesn’t have this permission, then you will get errors when you try to start services up again. See screenshot below for the service name (the name of this security group depends on the name of your Server and SQL Service):

 

 

That should cover the whole process, as well as provide a few tips on the areas I initially got stuck on (that were not explained in the official Microsoft KBs). If you have a different edition of SQL Server, just switch the MSDN article to the relevant edition and take it from there. Good luck!

 

SQL Server Full Text Indexing

by



So this is more of a mental note to self, based on the summary of a lesson I just completed on SQL Server Full Text Indexing. In summary, to create Full Text Indexes, we need to keep the following in mind:

  • Before a full text index is created, you need to make a full text catalog that is mapped to a filegroup first.
  • Full text indexes can be created on columns with CHAR, VARCHAR, XML and VARBINARY.
  • When using  a full text index with a VARBINARY(MAX) column, you need to specify the column for the COLUMN TYPE parameter so that the Full Text Index Engine knows which filter to load for parsing the data. (for example a filter for Word Documents, Excel, HTML etc…)
  • The “LANGUAGE” setting specifies which “Word Breaker” and “Stemmer” SQL Server loads to tokenise and build inflectional forms for the index.
  • Word breakers can be used for different languages that have a close relationship with fairly acceptable results in most cases, but stemmers are language specific and can only be used on the exact language data you are dealing with.
  • CHANGE_TRACKING controls whether SQL Server tracks changes to underlying data columns – Automatically , manually or is off.



This is my interpretation of the summary provided at the end of lesson 1 in Chapter 5 of the Microsoft MCTS Exam 70-432 authored by Mike Hotek.


Full Text Predicates that can be used:

  • CONTAINS
  • CONTAINSTABLE
  • FREETEXT
  • FREETEXTTABLE


A Thesaurus file can be used to configure synonyms for search arguments. A stop list contains a list of words that you want to exclude from your full text index or search arguments.

SQL Server 2008 – Change Tracking

by

I have recently started studying for some Microsoft SQL Server exams (in particular 70-432). In order to reinforce some of the information, I thought it would be a good idea to blog about some of the features of SQL Server 2008 I learn about. This post will be on the built in mechanism for Change Tracking.

Change tracking is a relatively lightweight functionality that associates a version with each row in a table which has had CHANGE_TRACKING enabled on it.

By using this mechanism, it should be easy to read the version number when data is read from the database, and when it comes to writing data back, this version number can be checked to see if it has changed or not, allowing your application to determine whether it is safe to write data back or not, depending on how you handle the situation.

Once the CHANGE_TRACKING option has been enabled for a database, you can choose which tables in the database change tracking information is kept for.

Two other options can also be used. Namely CHANGE_RETENTION, which allows you to specify how long change tracking information should be captured for, and AUTO_CLEANUP, which allows change tracking information to automatically be cleaned up.

If anyone has any extra information or can clarify any of the above points, then please feel free to add a comment 🙂

Shrink a SQL Database using SQL Management Studio

by

Here is a quick “how to” on shrinking a SQL Database using SQL Management Studio.

1. Launch SQL Management Studio and login with your desired credentials.

2. Connect to the SQL Database engine instance and expand it by double clicking on it.
3. Expand your “Databases” node and right-click the specific database you would like to shrink.
4. Go to “Tasks” -> “Shrink” and then click “Database”

5. There are some optional settings at this point. Read more about the process here if you would like to learn more. Otherwise continue with the defaults by clicking “OK”.

Here are the results on a DB I shrunk today (before & after):