VMware vCenter Server 6 on Windows Server 2012 R2 with Microsoft SQL Server 2014 – Part 2

VMware vCenter Server 6 on Windows Server 2012 R2 with Microsoft SQL Server 2014 - logo]1

In second post VMware vCenter Server 6 on Windows Server 2012 R2 with Microsoft SQL Server 2014 - Part 2 I will describe how to configure Microsoft SQL Server to be used for vCenter Server and we will create vCenter database.

Create a SQL Server Database and User for vCenter Server

We will begin with SQL Server Database creation.

  1. Login to SQL Server Management Studio as user with administrative privileges. Once you will see login screen connect to SQL Server Management Studio by selecting correct authentication type and proper account. In my case it is domain user which I used to install SQL Server.

    VMware vCenter Server 6 on Windows Server 2012 R2 with Microsoft SQL Server 2014 - Part 2 - 1]2

  2. Open vCenter Server installation media and navigate to /vCenter-Server/dbschema/DB_and_schema_creation_scripts_MSSQL.txt file. Please remember to change location of database file and log file. Adjust vpxuser password - I highlighted it for you.
    Remember that by default SQL Server use strong password policy and if you will not meet complexity of password SQL Query will fail. You can read more about it on Microsoft website - https://msdn.microsoft.com/en-us/library/ms161959(v=sql.120).aspx. Below you will find adjusted query with database file and log location changed.

    use [master]
    go
    CREATE DATABASE [VCDB] ON PRIMARY
    (NAME = N’vcdb’, FILENAME = N’H:\Program Files\Microsoft SQL Server\MSSQLSERVER\MSSQL\Data\VCDB.mdf’, SIZE = 10MB, FILEGROWTH = 10% )
    LOG ON
    (NAME = N’vcdb_log’, FILENAME = N’I:\Program Files\Microsoft SQL Server\MSSQLSERVER\MSSQL\Data\VCDB.ldf’, SIZE = 1000KB, FILEGROWTH = 10%)
    COLLATE SQL_Latin1_General_CP1_CI_AS
    go
    use VCDB
    go
    CREATE LOGIN [vpxuser] WITH PASSWORD=N’!long_and_complex_password1’, DEFAULT_DATABASE=VCDB, DEFAULT_LANGUAGE=us_english, CHECK_POLICY=OFF
    go
    CREATE USER [vpxuser] for LOGIN [vpxuser]
    go
    use MSDB
    go
    CREATE USER [vpxuser] for LOGIN [vpxuser]
    go
    ```

    1. Click New Query and paste script from above.

    VMware vCenter Server 6 on Windows Server 2012 R2 with Microsoft SQL Server 2014 - Part 2 - 2]3 4. After that click execute and wait for script to complete.

    VMware vCenter Server 6 on Windows Server 2012 R2 with Microsoft SQL Server 2014 - Part 2 - 4]4 5. Next step is to grant user we created - in my case vpxuser db_owner rights to VCDB and MSDB databases. We will do it simply by clicking to Logins and selecting vpxuser.

    VMware vCenter Server 6 on Windows Server 2012 R2 with Microsoft SQL Server 2014 - Part 2 - 6]5 6. Right mouse button click on vpxuser. Select User Mapping, choose msbd and assign db_owner role membership and click ok.

    VMware vCenter Server 6 on Windows Server 2012 R2 with Microsoft SQL Server 2014 - Part 2 - 7]6 7. Once we granted vpxuser db_owner rights we can create ODBC connection for vCenter Server installation. In order to do it navigate to Administrative Tools and select **ODBC Data Sources (64-bit).

    VMware vCenter Server 6 on Windows Server 2012 R2 with Microsoft SQL Server 2014 - Part 2 - 8]7** 8.  Select System DSN and click Add. Choose SQL Server Native Client 11.0 and click Finish.

    VMware vCenter Server 6 on Windows Server 2012 R2 with Microsoft SQL Server 2014 - Part 2 - 9]8 9. We need to provide name for vCenter Server Database, optional description and to which SQL Server we would like to connect.

    VMware vCenter Server 6 on Windows Server 2012 R2 with Microsoft SQL Server 2014 - Part 2 - 10]9 10. Click Next and select desired SQL Server authentication - I use Integrated Windows authentication. Click next.

    VMware vCenter Server 6 on Windows Server 2012 R2 with Microsoft SQL Server 2014 - Part 2 - 11]10 11. Change default database to one we created before - in my case VCDB and click next.

    VMware vCenter Server 6 on Windows Server 2012 R2 with Microsoft SQL Server 2014 - Part 2 - 12]11 12. If you know what you are doing you can adjust some settings - otherwise click Finish.

    VMware vCenter Server 6 on Windows Server 2012 R2 with Microsoft SQL Server 2014 - Part 2 - 13]12 13. Last step is to test data source connectivity. Just click on Test Data Source and if you configured everything correctly you should see success as result.

    VMware vCenter Server 6 on Windows Server 2012 R2 with Microsoft SQL Server 2014 - Part 2 - 14]13

    Final word

    We managed to finish vCenter Server database creation and SQL Server configuration and we are ready to do vCenter Server installation. As you probably suspect this will be done in third part which you can find here: https://www.wojcieh.net/vmware-vcenter-server-6-on-windows-server-2012-r2-with-microsoft-sql-server-2014-part-3/.