View Composer with SQL Express 2008

So, I don’t know anything about database administration. One of my customers is primarily an Oracle shop, which is fine for most things VMware (even if VMware’s Oracle documentation isn’t quite up to the standards set by their SQL documents). View Composer 2.7 however, has a very specific version of Oracle 11g R2 that it’s looking for (11.2.0.1 Patch 5, to be exact). I’ve never in my life applied patches to an Oracle client (and prior to working with this customer, I’d never installed the Oracle client at all!), and boy could they learn a thing or two from Microsoft… or VMware… or Symantec… or just about anyone else who has ever sold software, ever.

I used to complain about the experience of browsing HP’s or Dell’s website, looking for firmware or drivers. Dell has implemented a nice work around with their service tags so that you will hopefully never need to actually navigate the site. HP’s site is indexed by google, so once again you’ll hopefully never need to actually browse the site. Long story short, everything from Oracle requires you to sign in to download (so the google index isn’t an option) and software doesn’t have a “service tag”. What’s more, they don’t seem to offer rollup patches, as the one that I want (Oracle ID 9966926) requires that I first install another patch… which required a Perl update from Oracle… which requires who knows what else… and the clock was ticking the whole time that I spent discovering these facts.

Composer doesn’t need a giant database, since it’s just tracking which VM refers to which base image. More importantly, VMware supports SQL Express 2008 as a database back end for Composer (see: http://pubs.vmware.com/view-50/topic/com.vmware.view.installation.doc/GUID-5B2266B8-EA3C-4F49-BABB-2D0B91DE6C1D.html#GUID-5B2266B8-EA3C-4F49-BABB-2D0B91DE6C1D). In order to minimize my lost time, while the Oracle guys were tracking down files for me I decided to move forward with the SQL Express direction, using the database that was installed for Update Manager. If I could get it working with SQL Express before they could get the Oracle stuff lined up, we’d just use SQL Express for this database.

This is not a description of best practices. Instead, this is how I got it working – if any DBA reads this and wants to give out corrections or pointers, I’ll happily incorporate them to the post. Here’s the process that I followed in order to get View Composer using the locally installed instance of SQL Express 2008, written for someone without database experience, by someone without database experience. What I’ve done here was pretty much a combination of intuition, trial and error, and google searches on error codes.

My first challenge was downloading the SQL Management Studio for SQL Express 2008. Eventually I found the download at http://www.microsoft.com/download/en/details.aspx?id=7593 (after following the link in the VMware article and getting the 2005 version). Installing it was a bit of a chore and I wish I took better notes around that process, but it wasn’t too bad. As I recall, I had to update some framework software (it helpfully informed me of that when I tried to install it) and then browse the interface to eventually add features to my existing installation.  UPDATE: Thank you Virtually, in the comments, for tracking down a link with proper instructions for setting up the SQL 2008 Management Studio.

Once I had the Management Studio installed, I had to actually create the database and do some slight configurations to get everything working. I started up the management studio, but was surprised to find that my first challenge was simply logging in. The default Server type of “Database Engine” is correct, however you can’t just enter the server’s name in the Server name field. Some google-fu tracked down some Microsoft documentation revealing the format \ and it even helpfully specified that SQL Express uses the default instance name “sqlexpress” and that you can enter “(local)\sqlexpress” to log in. Well, I quickly found that VMware doesn’t use the default instance name, but examining the Services installed on the server gave me the clue I needed. In order to log in, I had to use “(local)\VIM_SQLEXP” as my Server name. I was logged in to the system with an account that had Local Administrator privileges, so the default “Windows Authentication” worked fine for me.

Upon getting logged in, I had to do 3 things. I had to change the server to allow SQL Authentication, I had to create a new database for Composer and I had to create an account with which to access that database. Changing the server was easy – just open Properties on the root object in the Object Explorer (the “(local)\VIM_SQLEXP…” object), go to “Security” and select the “SQL Server and Windows Authentication mode” option. Restart your SQL Services for it to take effect, and you’re good to go.

Creating the database was easy too – just right click on the “Databases” node and select “New Database”. Give it a meaningful name and adjust the location of the database files as you wish. I set the initial size of the database at 100 MB and the logs at 50 MB. That’s almost certainly overkill, but since it’s dedicated to this single purpose, I figured it wouldn’t hurt. I left the growth rates alone, figuring that it would take care of sizing issues if I did set them too small for some reason.

In order to create the User, I had to do it in 2 places. First, under the server’s “Security” node, I right clicked and selected New -> Login. I entered a meaningful name, selected the SQL Server authentication Radio Button and entered a strong password. I cleared out the “User must change password at next login box”, and for good measure cleared out the “Enforce password expiration” and “Enforce password policy” boxes (I don’t want this password to expire and I’m not quite sure what password policies might apply. I initially left that one checked and it liked my password… but I feared some other mechanism behind the scenes and so eventually disabled it). I also changed the “Default Database” at the bottom to my newly created database. Under “User Mapping”, I selected my new database and added roles. I read an article from VMware saying that the account needs “write” and “read” permissions, but I can’t find it now. 

Anyway, I gave my account the “db_owner”, “db_datareader”, and “db_datawriter” roles on that database (as well as the default “public” role) – it probably didn’t need all of those roles, but I was in a time crunch and this database is just for Composer, so I didn’t want to lose even more time with exhaustive tests.

Once the login was created on the SQL Server, I expanded my database, then went to its Security node and selected New -> User. Under “User name” I entered my same user name and I browsed for the SQL Login name that I had just created. Under “Database role membership” I believe that it automatically picked up the roles as defined above, although I may have specified them there too.

Once all of that was in place, I was able to create my ODBC connection as per normal. I created a System DSN using the SQL Native Client, connecting to my \VIM_SQLEXP. I authenticated with SQL Server authentication and entered the credentials for my account. I verified that the default database was my new database and finished the wizard. The connection test was successful, so I moved forward with the Composer install. Composer installed with no issues (after I remembered to “run as Administrator” – stupid me for forgetting about UAC on 2008) and everything was up and running.

So, there it is. As I said at the start of this post – I am by no means a DBA. I don’t know anything about databases and I wouldn’t be surprised if I violated all sorts of SQL best practices in this process. But, this process got things going well enough for me to move the project forward. If anyone reading this has any suggestions about how to improve this process, I’d love to hear them.