How to setup MS SQL 2008 R2 for VMware vCenter – Part 2

The second post of this series shows you how to configure Microsoft SQL Server 2008 R2 for the use with VMware vCenter Server.


How to setup MS SQL 2008 R2 for VMware vCenter – Part 1
How to setup MS SQL 2008 R2 for VMware vCenter – Part 2 <<


1.) After you installed the Microsoft SQL Server (like described in Part 1), start the Microsoft SQL Server Management Studio, which you will find in your start menu.
By fact I started the Management Studio on the same server as the SQL Server is installed, I just enter localhost as server name. I´m using the “sa” user in this case, but if you followed Part 1, you can use the Windows authentication also, if you are signed in with the same user you used for the SQL Server installation (typically Administrator).

sql_1



2.) The first thing I´m going to do is to create the database, which I need for the vCenter installation. In the Object Explorer you will find the folder Databases. Right-click and select New Database.

sql_2


3.) In the upcoming window I define a database name. Before clicking OK go over to the next step….

sql_3


4.) Below Options on the left side I set the Recovery model from Full to Simple. Now I click OK and the database gets created.

sql_4


5.) Now I create a dedicated user, which gets used by vCenter to connect to the database. Below the folder Security you will find a folder Logins. Right-click and select New Login.

sql_5


6.) After defining a login name and a password (I don´t want to have any password policies for this user) I also need to set the default database and language. So select the database you just created and select your desired language. Before clicking OK go over to the next step….

sql_6


7.) Select User Mapping on the left side and use the checkboxes to map the user to the msdb and vcenter_db (or the name you have choosen). Set the column Default Schema for both entries to dbo. Also enable the ckeckbox for db_owner for both databases in the list on the bottom of this window and click OK.

sql_7


8.) The last steps I´m doing with the SQL Server Management Studio are some general configuration changes, which are not mandatory that your database / -connection is working and you should considering these for your environment.
I select the SQL Server itself in the Object Explorer (in my case named localhost, could be also the server name if you are connected with a remote Management Studio), right-click and choose Properties.

sql_8


9.) Below Memory I set the Maximum server memory. In my lab the SQL Server and vCenter Server are running on the same instance, which is in a productive environment mostly not the case. You should set the value to a number, which leaves you enough RAM for the OS and may other services (like vCenter in my case). Typically this server / VM is “just” running the SQL Server so set the value to available RAM minus 2GB for the Windows Server OS.

sql_9


10.) Also I change the Login auditing setting below the page Security to Both failed and successful logins. May you have some security guidelines at your company you need to consider for this option.

sql_10


11.) If vCenter Server and SQL Server are not running on the same box you also need to modify the TCP/IP setting for your SQL Server.
In the start menu you find the SQL Server Configuration Manager. Expand SQL Server Network Configuration on the left and make sure the TCP/IP protocol is enabled. Right-click and select Properties. Below the tab IP Addresses set Enabled to Yes and TCP Dynamic Ports to 0. If you have multiple network interfaces make sure to select the right one (the one which vCenter will use to connect).
You need to restart the SQL service to enable your changes. May a good time for a complete Windows restart 🙂

sql_11


12.) Before I can start now with the vCenter installation I need to add a ODBC System DSN, which I do via the ODBC Data Sources (Start menu -> Administrative tools -> Data Sources (ODBC) ). Select the System DSN tab and click Add.

Note: This needs to be down on the vCenter server (not on the SQL Server!).

Important: If you want to setup a DSN for VMware Site Recovery Manager 5.0 (SRM) you need to create a 32bit DSN. Instead of using the ODBC link in your start menu (on a 64bit system) follow this path: C:WindowsSysWOW64odbcad32.exe . All the other following steps remain the same.

sql_12


13.) Select the SQL Server Native Client and click Finish.

Note: If you don´t have this option you need to install the native client (in my case it was available because SQL Server is running on the same box, which includes the client). You will find it on the SQL Server DVD or on Microsoft´s website for download).

sql_13


14.) Enter a name for the DSN and the DNS name of your SQL Server (localhost in my case because vCenter and SQL are on the same box).

sql_14


15.) In the next step the user credentials are required, which we defined in the SQL Server Management Studio before.

sql_15


16.) No changes needed on the next page, because we defined the correct default database for that user.

sql_16


17.) No changes needed on the next page also, because we defined the correct default language for that user.

sql_17


18.) A summary is popping up and now you can test if the connection to the SQL Server works by clicking Test Data Source.

sql_18


19.) If you have done everything like suggested the steps before you should get back: TESTS COMPLETED SUCCESSFULLY!

sql_19


20.) That’s it! Time for the vCenter installation process (I´m just going to show the database related steps).

On the point when the installer asks for a database of course we select Use an existing supported database instead of the installation of the SQL Express version. In the drop down menu you will find the name of the DSN you just created.

sql_20


21.) Next you get asked for the database user again. Enter the credentials and click Next.

sql_21


That’s it!! If you followed both parts step by step, you should been able to install and configure MS SQL Server 2008 R2 to use it as database for your vCenter Server.


<< Back to Part 1



Comments

  1. Thank you so much for posting this tutorial! I’m just beginning my journey into vSphere and like most people, I started off with just using the SQL Express Edition for testing purposes. I knew that some day I would need some knowledge to perform a full blown database install of vCenter. I’ve looked over many different tutorials and many of them are all talk and no substance. Your tutorial pretty much covered all the basics I needed to know in order to get a basic vCenter server running! Awesome job! Please continue with the good work. =)

  2. I would like to echo John’s appreciation. This is a great quick start guide. It’s the perfect mix of instruction and screen shots.

  3. hi
    i did this steps, but this error occurred:
    “configure SQL server remote connections to both TCP/IP and named pipes.this can
    be done using Microsoft SQL server Surface Area Configuration tool,by selecting the database instance and navigating to Database Engines | Remote Connections Option.”
    :((
    please help me, it’s important for me…

  4. Thxx

  5. Madhoor says:

    Thank you so much for this post!

  6. Awesome, really! You made my life so simple with this outstanding guide! Bookmarked you site for the future!
    Thanks Again
    Claudio

  7. great job man! thank you a alot!

  8. I will take the time to thank vMario156. This post is well thought out, thorough and most of all…it’s informative. Tried other blogs and nope, not that this is terribly difficult to accomplish but this site is the truth. Yep…dare I say even more informative than anything I was able to find on the VMware website. Keep up the good work chief!

Trackbacks

  1. […] How to setup MS SQL 2008 R2 for VMware vCenter – Part 1 << How to setup MS SQL 2008 R2 for VMware vCenter – Part 2 […]

  2. […] Before I begin I already installed two independent vCenter Server instances, each with a local MS SQL 2008 R2 Std. installation. Also I created the required SRM database on the same SQL instance. If you need help with the SQL part (installation and / or configuration) you should take a look at my previous posts: How to setup MS SQL 2008 R2 for VMware vCenter – Part 1 + How to setup MS SQL 2008 R2 for VMware vCenter – Part 2. […]

Speak Your Mind

*