Software Defined Single Sign On Database Creation

July 2nd, 2013 by jason No comments »

I don’t manage large scale production vSphere datacenters any longer but I still manage several smaller environments, particularly in the lab.  One of my pain points since the release of vSphere 5.1 has been the creation of SSO (Single Sign On) databases.  It’s not that creating an SSO database is incredibly difficult, but success does require a higher level of attention to detail.  There are a few reasons for this:

  1. VMware provides multiple MS SQL scripts to set up the back end database environment (rsaIMSLiteMSSQLSetupTablespaces.sql and rsaIMSLiteMSSQLSetupUsers.sql).  You have to know which scripts to run and in what order they need to be run in.
  2. The scripts VMware provides are hard coded in many places with things like database names, data file names, log file names, index file names, SQL login names, filegroup and tablespace information.

What VMware provides in the vCenter documentation is all well and good however it’s only good for installing a single SSO database per SQL Server instance.  The problem that presents itself is that when faced with having to stand up multiple SSO environments using a single SQL Server, one needs to know what to tweak in the scripts provided to guarantee instance uniqueness, and more importantly – what not to tweak.  For instance, we want to change file names and maybe SQL logins, but mistakenly changing tablespace or filegroup information will most certainly render the database useless for the SSO application.

So as I said, I’ve got several environments I manage, each needing a unique SSO database.  Toying with the VMware provided scripts was becoming time consuming and error prone and frankly has become somewhat of a stumbling block to deploying a vCenter Server – a task that had historically been pretty easy.

There are a few options to proactively deal with this:

  1. Separate or local SQL installation for each SSO deployment – not really what I’m after.  I’ve never been much of a fan of decentralized SQL deployments, particularly those that must share resources with vSphere infrastructure on the same VM.  Aside from that, SQL Server sprawl for this use case doesn’t make a lot of sense from a financial, management, or resource perspective.
  2. vCenter Appliance – I’m growing more fond of the appliance daily but I’m not quite there yet. I’d still like to see the MS SQL support and besides that I still need to maintain Windows based vCenter environments – it’s a constraint.
  3. Tweak the VMware provided scripts – Combine the two scripts into one and remove the static attributes of the script by introducing TSQL variables via SQLCMD Mode.

I opted for option 3 – modify the scripts to better suit my own needs while also making them somewhat portable for community use.  The major benefits in my modifications are that there’s just one script to run and more importantly anything that needs to be changed to provide uniqueness is declared as a few variables at the beginning of the script instead of hunting line by line through the body trying to figure out what can be changed and what cannot.  And really, once you’ve provided the correct path to your data, log, and index files (index files are typically stored in the same location as data files), the only variable needing changing going forward for a new SSO instance is the database instance prefix.  On a side note, I was fighting for a method to dynamically provide the file paths by leveraging some type of system variable to minimize the required.  While this is easy to do in SQL2012, there is no reliable method in SQL2008R2 and I wanted to keep the script consistent for both so I left it out.

Now I’m not a DBA myslef but I did test on both SQL2008R2 and SQL2012 and I got a little help along the way from a few great SMEs in the community:

  • Mike Matthews – a DBA in Technical Marketing at Dell Compellent
  • Jorge Segarra – better known as @sqlchicken on Twitter from Pragmatic Works (he’s got a blog here as well)

If you’d like to use it, feel free.  However, no warranties, use at your own risk, etc.  The body of the script is listed below and you can right-click and save the script from this location: SDSSODB.sql

Again, keep in mind the TSQL script is run in SQLCMD Mode which is enabled via the Query pulldown menu in the Microsoft SQL Server Management Studio.  The InstancePrefix variable, through concatenation, will generate the database name, logical and physical file names, SQL logins and their associated passwords.  Feel free to change any of this behavior to suit your preferences or the needs of your environment.

————————————————————————————-

— The goal of this script is to provide an easy, consistent, and repeatable

— process for deploying multiple vSphere SSO databases on a single SQL Server

— instance without having to make several modifications to the two VMware provided

— scripts each time a new SSO database is needed.

— The following script combines the VMware vSphere 5.1 provided

— rsaIMSLiteMSSQLSetupTablespaces.sql and rsaIMSLiteMSSQLSetupUsers.sql scripts

— into one script. In addition, it removes the static database and file names

— and replaces them with dynamically generated equivalants based on an

— InstancePrefix variable declared at the beginning of the script. Database,

— index, and log file folder locations are also defined with variables.

— This script meets the original goal in that it can deploy multiple iterations

— of the vSphere SSO database on a single SQL Server instance simply by modifying

— the InstancePrefix variable at the beginning of the script. The script then uses

— that prefix with concatenation to produce the database, .mdf, .ldf, .ndf, and

— two user logins and their required SQL permissions.

— The script must be run in SQLCMD mode (Query|SQLCMD Mode).

— No warranties provided. Use at your own risk.

— Jason Boche (@jasonboche, http://boche.net/blog/)

— with special thanks to:

— Mike Matthews (Dell Compellent)

— Jorge Segarra (Pragmatic Works, @sqlchicken, http://sqlchicken.com/)

— VMware, Inc.

————————————————————————————-

 

:setvar InstancePrefix “DEVSSODB”

:setvar PrimaryDataFilePath “D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\”

:setvar IndexFilePath “D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\”

:setvar LogFilePath “D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\”

 

USE [master];

GO

 

————————————————————————————-

— Create database

— The database name can also be customized, but cannot contain

— reserved keywords like database or any characters other than letters, numbers,

— _, @ and #.

————————————————————————————-

CREATE DATABASE [$(InstancePrefix)_RSA] ON

PRIMARY(

NAME = N’$(InstancePrefix)_RSA_DATA’,

FILENAME = N’$(PrimaryDataFilePath)$(InstancePrefix)_RSA_DATA.mdf’,

SIZE = 10MB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10% ),

FILEGROUP RSA_INDEX(

NAME = N’$(InstancePrefix)_RSA_INDEX’,

FILENAME = N’$(IndexFilePath)$(InstancePrefix)_RSA_INDEX.ndf’,

SIZE = 10MB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10%)

LOG ON(

NAME = N’$(InstancePrefix)_translog’,

FILENAME = N’$(LogFilePath)$(InstancePrefix)_translog.ldf’,

SIZE = 10MB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10% );

GO

 

— Set recommended performance settings on the database

ALTER DATABASE [$(InstancePrefix)_RSA] SET AUTO_SHRINK ON;

GO

ALTER DATABASE [$(InstancePrefix)_RSA] SET RECOVERY SIMPLE;

GO

 

————————————————————————————-

— Create users

— Change the user’s passwords (CHANGE USER PASSWORD) below.

— The DBA account is used during installation and the USER account is used during

— operation. The user names below can be customised, but cannot contain

— reserved keywords like table or any characters other than letters, numbers, and _ .

— Please execute the scripts as a administrator with sufficient permissions.

————————————————————————————-

 

USE [master];

GO

 

CREATE LOGIN [$(InstancePrefix)_RSA_DBA] WITH PASSWORD = ‘$(InstancePrefix)_RSA_DBA’, DEFAULT_DATABASE = [$(InstancePrefix)_RSA];

GO

CREATE LOGIN [$(InstancePrefix)_RSA_USER] WITH PASSWORD = ‘$(InstancePrefix)_RSA_USER’, DEFAULT_DATABASE = [$(InstancePrefix)_RSA];

GO

 

USE [$(InstancePrefix)_RSA];

GO

 

ALTER AUTHORIZATION ON DATABASE::[$(InstancePrefix)_RSA] TO [$(InstancePrefix)_RSA_DBA];

GO

 

CREATE USER [$(InstancePrefix)_RSA_USER] FOR LOGIN [$(InstancePrefix)_RSA_USER];

GO

 

The .vmfsBalloon File

July 1st, 2013 by jason No comments »

One year ago, I wrote a piece about thin provisioning and the role that the UNMAP VAAI primitive plays in thin provisioned storage environments.  Here’s an excerpt from that article:

When the manual UNMAP process is run, it balloons up a temporary hidden file at the root of the datastore which the UNMAP is being run against.  You won’t see this balloon file with the vSphere Client’s Datastore Browser as it is hidden.  You can catch it quickly while UNMAP is running by issuing the ls -l -a command against the datastore directory.  The file will be named .vmfsBalloonalong with a generated suffix.  This file will quickly grow to the size of data being unmapped (this is actually noted when the UNMAP command is run and evident in the screenshot above).  Once the UNMAP is completed, the .vmfsBalloon file is removed.

Has your curiosity ever got you wondering about the technical purpose of the .vmfsBalloon file?  It boils down to data integrity and timing.  At the time the UNMAP command is run, the balloon file is immediately instantiated and grows to occupy (read: hog) all of the blocks that are about to be unmapped.  It does this so that during the unmap process, none of the blocks are allocated during the process of new file creation elsewhere.  If you think about it, it makes sense – we just told vSphere to give these blocks back to the array.  If during the interim one or more of these blocks were suddenly allocated for a new file or file growth purposes, then we purge the block, we have a data integrity issue.  More accurately, newly created data will be missing as its block or blocks were just flushed back to the storage pool on the array.

VMware Extends Lab Manager 4.x Support 1 Year

June 25th, 2013 by jason No comments »

A few years ago, with the release of VMware vCloud Director 1.0, VMware announced the end of support date for Lab Manager 4.x (I wrote about that here).  That date had actually come and gone last month.  This morning I received a communication from VMware that Lab Manager 4.x support has been extended for another year with the new end of support date being May 21, 2014. It would appear VMware had more existing Lab Manager end users not willing to immediately migrate to vCloud Director than originally thought.

The text from the email can be found below:

June 2013

Dear VMware Valued Customers,

VMware is pleased to announce a 1-year extension to the support for VMware vCenter Lab Manager 4.x. As reference, the original end of support date for this product was May 1, 2013. The new official end of support date will be May 21, 2014. This new end of support date aligns with VMware vSphere 4.x (noted in the support lifecycle matrix below as VMware ESX/ESXi 4.x and vCenter Server 4.x) end of support. This new date also allows the vCenter Lab Manager customer base more time to both use the 4.x product and evaluate options for moving beyond vCenter Lab Manager in the near future.

Additional Support Materials:

vEXPERT 2013 Thoughts and Thanks

June 17th, 2013 by jason No comments »

Snagit CaptureThe ballots have been counted and the 2013 vEXPERTS were announced in a VMTN blog post by John Troyer.  I was fortunate enough to be awarded this honorable designation for a fifth consecutive year (I’m going to link that Five Timers club skit from SNL because I still get a laugh out of it).  What’s interesting about this journey for me is that for quite a while I had worked in and contributed towards Microsoft Windows, Active Directory, Networking, Design, and related communities striving for Microsoft MVP recognition.  That never happened.  Quite honestly I probably didn’t give it enough time and there are metric ton of brilliant Microsoft people already with their MVP status to compete against.  Once VMware came into my life, I quickly gained interest in the technology and its potential for businesses as well as end users world wide.  As a result, I shifted my career to focus solely on VMware and datacenter virtualization which did not stop short of leaving a great company I had been at for 11 years to make that change stick.  Although John Troyer at one time denied it and may still, I think the vEXPERT program is very much like Microsoft’s MVP program and the individuals who are awarded vEXPERT are very much like MVPs in terms of giving back and community involvement.  Although I appreciated the recognition and gifts going back to the first vEXPERT awards in February 2009, I think I took for granted what the award really meant for me as an individual.  With my virtualization blog already successful and my name pretty well known from spending a few years on the VMTN forums, an accolade here or there was quickly put in the trophy case and with the motor perpetually running, I moved on to the next thing.  In the back of my mind I knew what awards meant but I didn’t really take the time to stop and recognize that what I had tried to accomplish in the Microsoft programs and failed, I’ve now achieved many times over in the VMware community.  In the long run I think it has been a lot more beneficial for me and hopefully for the relatively new and growing virtualization community as well.  I’ve learned a lot, met a lot of people, made many good friends, have a great job, and I sincerely hope that I can continue making a positive community impact into the future.  My thanks to John Troyer, VMware, and the incredible community that I am a part of.  I’d also like to thank TrainSignal, Tintri, and Veeam for their generous gifts to vEXPERTs current and past.

Available Lab Gear

May 29th, 2013 by jason No comments »

Heads up to any locals looking for server grade vSphere hardware infrastructure.  I’ve been doing some lab spring cleaning the past few weeks and after some consolidation efforts, I’ve got some hardware available that’s not being put to good use any longer.  All of these are 64-bit and will run vSphere.  All have Ethernet and/or Fibre Channel options.

  • 2x HP DL385 (1x AMD DC Opteron, 4GB RAM, rails, RPS)
  • 2x HP DL385 G2 (2x AMD QC Opteron (Barcelona), 34GB RAM, rails, RPS)
  • 2x HP DL585 G2 (4x AMD DC Opteron, 64GB RAM, RPS, power cables)

If you have any questions not pertaining to power or heat, please ask.

You pick up – Lakeville, MN.

The price is right – email me if interested.

Publisher Error In Your Favor

May 20th, 2013 by jason No comments »

Snagit Capture

I’m not promoting this on Twitter – Let’s see who actually reads my blog on a Monday morning or at least still employs RSS technology.

A short while ago, I received on my doorstep a copy of Scott Lowe’s Mastering VMware vSphere 5.  I’ve already got my own copy and I’d like to make sure this book ends up in the hands of someoneone who:

A) needs a copy

B) will read it and put the tremendous knowledge it contains to good use

C) won’t ask me for an electronioc handheld version

Respond in the comments section below on 1) your role and 2) your thoughts and/or opinions (good or bad) of VMware’s endeavors into both Software Defined Storage and Software Defined Networking.  The 5th response snags the copy which I will mail to you.  Good luck and thank you for your feedback.

Update 5/20/12:  Thank you for the responses.  It’s good to see so many people attentive on a Monday.

I expect anyone could argue that the first response from Andy wasn’t an actual opt-in response for the contest, nor did it conform to the contest rules.  This creates a problem because whether or not I include Andy’s comment means either Miguel or Kris are winners.  The easiest way to settle this is to declare you both winners.  Send me an email detailing your full mailing address and each of you will receive a copy of Mastering vSphere 5 by Scott Lowe.

Thank you,

Jas

vSphere 5.1 Update 1 Update Sequence

May 6th, 2013 by jason No comments »

Not so long ago, VMware product releases were staggered.  Major versions of vSphere would launch at or shortly after VMworld in the fall, and all other products such as SRM, View, vCloud Director, etc. would rev on some other random schedule.  This was extremely frustrating for a vEvangelist because we wanted to be on the latest and greatest platform but lack of compatibility with the remaining bolt-on products held us back.

While this was a wet blanket for eager lab rats, it was a major complexity for production environments.  VMware understood this issue and at or around the vSphere 5.0 launch (someone correct me if I’m wrong here), all the development teams in Palo Alto synchronized their watches & revd product in essence at the same time.  This was great and it added the much needed flexibility for production environment migrations.  However, in a way it masked an issue which didn’t really exist before by virtue of product release staggering – a clear and understandable order of product upgrades.  That is why in March of 2012, I looked at all the product compatibility matrices and sort of came up with my own “cheat sheet” of product compatibility which would lend itself to an easy to follow upgrade path, at least for the components I had in my lab environment.

vSphere 5.1 Update 1 launched on 4/25/13 and along with it a number of other products were revd for compatibility.  To guide us on the strategic planning and tactical deployment of the new software bundles, VMware issued KB Article 2037630 Update sequence for vSphere 5.1 Update 1 and its compatible VMware products.

Snagit Capture

Not only does VMware provide the update sequencing information, but there are also exists a complete set of links to specific product upgrade procedures and release notes which can be extremely useful for planning and troubleshooting.

The vCloud Suite continues to evolve providing agile and elastic infrastructure services for businesses around the globe in a way which makes IT easier and more practical for consumers but quite a bit more complex on the back end for those who must design, implement, and support it.  Visit the KB Article and give it 5 stars.  Let VMware know this is an extremely helpful type of collateral for those in the trenches.