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.
- 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.
- 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.
Transact-SQL123456789101112131415161718use [master]goCREATE DATABASE [VCDB] ON PRIMARY(NAME = N'vcdb', FILENAME = N'<strong>H:\Program Files\Microsoft SQL Server\MSSQLSERVER\MSSQL\Data\VCDB.mdf</strong>', SIZE = 10MB, FILEGROWTH = 10% )LOG ON(NAME = N'vcdb_log', FILENAME = N'<strong>I:\Program Files\Microsoft SQL Server\MSSQLSERVER\MSSQL\Data\VCDB.ldf</strong>', SIZE = 1000KB, FILEGROWTH = 10%)COLLATE SQL_Latin1_General_CP1_CI_ASgouse VCDBgoCREATE LOGIN [vpxuser] WITH PASSWORD=N'<strong>!long_and_complex_password1</strong>', DEFAULT_DATABASE=VCDB, DEFAULT_LANGUAGE=us_english, CHECK_POLICY=OFFgoCREATE USER [vpxuser] for LOGIN [vpxuser]gouse MSDBgoCREATE USER [vpxuser] for LOGIN [vpxuser]go
- Click New Query and paste script from above.
- After that click execute and wait for script to complete.
- 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.
- Right mouse button click on vpxuser. Select User Mapping, choose msbd and assign db_owner role membership and click ok.
- 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).
- Select System DSN and click Add. Choose SQL Server Native Client 11.0 and click Finish.
- We need to provide name for vCenter Server Database, optional description and to which SQL Server we would like to connect.
- Click Next and select desired SQL Server authentication – I use Integrated Windows authentication. Click next.
- Change default database to one we created before – in my case VCDB and click next.
- If you know what you are doing you can adjust some settings – otherwise click Finish.
- 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.
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://wojcieh.net/vmware-vcenter-server-6-on-windows-server-2012-r2-with-microsoft-sql-server-2014-part-3/.
My experience and skills has been proven by leading vendor certifications like VMware Certified Advanced Professional 5 – Data Center Administration, VMware Certified Advanced Professional 5 – Data Center Design, VMware Certified Professional 6 - Data Center Virtualization, VMware Certified Professional 6 - Network Virtualization, Microsoft MCITP Server Administrator, ITIL V3, VMware vExpert 2014, 2015, 2016 and 2017 Award.
My blog wojcieh.net - was voted #50 in TopvBlog 2016 contest!
Latest posts by Wojciech Marusiak (see all)
- How to add NFS export to VMware ESXi 6.5 - 12 April, 2017
- Migration of Windows vCenter Server 6.x to vCenter Server 6.5 Appliance - 10 March, 2017
- Altaro VM Backup 7.0 released with new great features - 7 March, 2017