Vmware 软件定义的单点登录数据库创建

我不管理vSphere数据中心的大规模生产任何更长的时间，但我仍然管理几个较小的环境中，特别是在实验室中。我的痛点之一的vSphere5.1发布以来一直SSO（单点登录）数据库的创建。这并不是说创建一个SSO数据库是非常困难的，但成功并不需要更高层次的对细节的关注。这有几个原因：

1. 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.

VMware提供的vCenter文档中，一切都很好，但只适合安装一个单一SSO每个SQL Server实例的数据库。提出了自己的问题是面临着站起来多个SSO环境中使用单一SQL Server时，你需要知道什么调整保证实例的唯一性，更重要的是 - 没有什么调整提供的脚本。例如，我们要更改文件名和也许SQL登录，但错误地改变表空间或文件组信息肯定会导致数据库无用的SSO应用程序。

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, )

– 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