Archive for Database

Migrating a vCenter and Update Manager Database from SQL 2008 to SQL 2012

September 5, 2016 Database No comments

SQLDB

Migration Options

There are 3 options available to migrate a SQL server database

  • Backup and Restore
  • Detach and Attach the Database
  • Copy the Database using SQL Server Management Studio

I am simply going to use the Copy the Database using SQL Server Management Studio option as this is quickest and easiest in my opinion

Instructions

  • On the Windows Server 2012 server, open the SQL Management Studio console and connect to the old SQL server instance. From the File menu choose Connect Object Explorer, or click the icon from the Object Explorer window.

Screen Shot 2016-08-22 at 10.36.48

  • Connect to your Windows Server 2008 R2 SQL Server

Screen Shot 2016-08-22 at 10.37.14

  • Now that we are connected to the old SQL server, right-click the database and choose Tasks > Copy Database.
  • You will get the Welcome to Copy Database wizard

Screen Shot 2016-08-22 at 10.41.18

  • The wizard will automatically knows the source server, but make sure you check it anyway. If is not the one you want, type it in the Source server box
  • Select whether to use Windows or SQL Authentication

Screen Shot 2016-08-22 at 11.42.09

  • On the destination server, the server name most likely will be wrong, so we need to type the correct one. Click Next when you’re done
  • Select whether to use Windows or SQL Authentication

Screen Shot 2016-08-22 at 11.42.21

  • Next you are on the Select a Transfer Method
  • Once source & destination server details given, you need to select the way by which you are going to copy move the database.
  • Detach Attach Faster methods, requires db to be offline. Users will be disconnected and physical files of the db will be copied to the destination server
  • SMO Slower method, db will be in online state. This will create the db in the destination server with the same name and copy all the datas from source. I used this method

Screen Shot 2016-08-22 at 11.42.35

  • Next Select the Database you want to move or copy. I kept mine as Copy as when it has finished copying I can simply take the original database offline

IMG_1358

  • On the Configure Destination Database Page, you need to provide the new db name and the path where CDW should place the physical files in the destination serve

IMG_1359

  • If there are any related objects to this database, select them, then press the arrow to move them to the right, to the Selected related objects section.

IMG_1360

  • On the Configure a Package

IMG_1361

  • In the next page you need to provide the package name and the log file for this process, so that you can review any failures.

IMG_1362

  • Check the details in the final wizard and click Finish

IMG_1363

  • All actions should have success next to them

IMG_1365

  • Refresh the console and you should see the database up and running on the new server.
  • There is one more step that applies to all three migration methods. The database needs to be put in a 2012 mode, or the latest version of your SQL server in case you are not using SQL 2012. This is to take advantage of all the features that the latest SQL edition provides. After the database has been moved, right-click it and choose Properties.
  • Click the Options page and on the Compatibility level box choose the latest edition of SQL server. In mine case is 2012. You have to be careful with this, because if you ever wanted to migrate the database to an older SQL version is not going to work. There are going to be incompatibility problems, so again…caution.

Screen Shot 2016-08-22 at 11.49.59

  • On the vCenter server, open the ODBC Connection and adjust the connection to point to the new SQL server
  • If the connection doesn’t work, check the logins on the new server as these can come across as disabled. Right click on the user account, select Properties > Status > Under Login, select Enabled
  • Next go to the original database and select to take offline. if you have problems taking the database offline then follow the link below to kill existing connections to the database

How to fix a SQL Server Database stuck going offline

Considerations

  • You cannot move system databases
  • Selecting move option will delete the source db once it moves the db to destination server
  • If you use SMO method to move full text catalogs then you need to repopulate it
  • SQL Server Agent should be running or else it will fail in job creation step
  • You cant move encrypted objects (like objects, certificates etc) using CDW

What is an ODBC Connection?

May 29, 2012 Database No comments

ODBC stands for Open Data Base Connectivity. It is a connection that is created to define a connection between a computer and a database stored on another system. The ODBC connection contains information needed to allow a computer user to access the information stored in a database that is not local to that computer. You need to define the type of the database application – like Microsoft SQL or Oracle or FoxPro or mySQL. Once you have defined the type of database you need to select or supply the appropriate driver for a connection (Windows already contains many of these) and then supply the name of the database file and the credentials needed to access the database.
Once the ODBC connection is created, you can tell specific programs to use that ODBC connection to access information in that database.

It was developed by the SQL Access Group in 1992 to standardize the use of a DBMS by an application. ODBC provides a universal middlewarelayer between the application and DBMS, allowing the application developer to use a single interface. If changes are made to the DBMS specification, only the driver needs updating. An ODBC driver can be thought of as analogous to a printer or other driver, providing a standard set of functions for the application to use, and implementing DBMS-specific functionality.

An application that can use ODBC is referred to as “ODBC-compliant”. Any ODBC-compliant application can access any DBMS for which a driver is installed. Drivers exist for all major DBMSs and even for text or CSV files.

VMware ODBC Connection Example

The database used for vCenter can be installed on a dedicated host, or the same host depending on the size of your environment.  For scalability, I recommend installing vCenter and SQL Server on separate hosts unless your running a very small environment and know that it will never grow

Instructions

  • Note SQL Server must be installed and a DB created for vCenter
  • Note: The ODBC source vCenter uses must be created with the SQL Native Client driver (the SQL driver provided with Windows will not work.)  The SQL Native Client driver can be found on the SQL Server 2005 installation media. Install this first
  • Open Control Panel on your vCenter Server followed by Administrative Tools
  • Open the ODBC Connection icon

  • Click the System DSN tab and click the “Add” button

  • Choose SQL Native Client

  • Provide a Name for the driver (you will reference this name during the vCenter install) and provide the Hostname/IP of the SQL Server instance you created when you installed SQL Server

  • Click Next and Select the “SQL Server authentication” type and provide the Login and password created from installing SQL Server – You will have logged into SQL Management Studio and created a new user (SQL Server Authentication)

  • Click Next and Change the default database to the name of your previously created vCenter database

  • Click Next and keep the following settings

  • Click Finish

  • Test Data Source. It should show the below

VMware DB Scripts for Performance Stats

May 21, 2012 Database, VMware No comments

We reinstalled our SQL Server 2008 Database completely and restored the Virtual Center DB and the Virtual Update Manager DB mainly due to someone installing the SQL Server 2008 software in Evaluation Mode which then expired and caused us no end of problems!

What you need to remember if you rebuild and restore the databases is to add the performance scripts back into SQL Server Management Studio for Weekly, Monthly and Yearly

The Issue

Following a SQL DB re-installation and restore we were doing the following

  • Click on Host
  • Click on Performance Tab
  • Click Advanced
  • Click on Chart Options
  • Choose Week, or Past Month

It comes up with “Performance Data is currently not available for this entity”

Instructions

  • Depending on how you are setup. (We had a separate VM for our DB and vCenter Server)
  • Log into SQL Management Studio on your Database Server
  • On the DB Server, create a shortcut to your vCenter Server to the following path. (This contains the scripts you need and may be on the C or D Drive)
  • D:\Program Files\VMware\Infrastructure\VirtualCenter Server
  • Make sure you have the Virtual Center DB selected in SQL Management
    Studio. Then just double click the file, it should open against the VCDB.
  • Check this link for the script names

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1004382

  • If you have problems with that for any reason you can create a new query
    against the VCDB and then open up the SQL scripts in notepad and copy
    across.
  • The scheduled jobs trigger the stored procedures which are already part of the DB. Amongst other things they clear out the raw data after it’s been processed and is no longer needed, they will also remove old tasks and events from the DB.

Scripts

This is what you should see in your SQL Server Management Studio Application

SQL Database Tables getting too big in VMware

May 18, 2012 Database No comments

Checking vCenter DB Table sizes

Sometimes you can experience issues with the vCenter Database getting too large. If you want to check the table sizes to confirm this, please do the following.

  • Open SQL Management Studio
  • Select vCenter DB name
  • Execute the following query to get Table sizes

Create Table #Temp(Name sysname, rows int, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100))

exec sp_msforeachtable ‘Insert Into #Temp Exec sp_spaceused ”?”, ”true”’

Select * From #Temp

Drop Table #Temp

Purge Scripts

There are a set of purge scripts where you can set how much historical data you keep and it will remove everything else from the DB.

Take a look at this KB, it runs through the process and has a link to the scripts:

http://kb.vmware.com/kb/1025914

One thing I will say is that the scripts can take a long time to run, If the size of your DB is quite large, it could take quite a while to complete. I would kick it off and let it run over the weekend if it hasn’t completed by the end of the day. Also the scripts are perfectly safe but I would take a backup of the DB before doing anything just for peace of mind

DB Maintenance

You should run a shrink on the DB to remove empty space and a regular backup is a good idea as MS SQL uses this to do maintenance on the DB. If you check out the Microsoft documentation for your version of MS SQL there should be detail and recommendation for setting that up

Useful Links

Defragmenting VirtualCenter performance data indexes on a Microsoft SQL database:

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1003990

Unable to get an exclusive access to the vCenter Server repository:

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1006369

Changing the IP Address of the SQL Database Server

May 14, 2012 Database, VMware No comments

If like us, you need to change the IP Address of you vCenter Database server, here are a few pointers

vCenter accesses the DB through an ODBC connection. That’s normally
configured with a FQDN or shortname  rather than an IP address so the
change shouldn’t cause a problem.

You can test it by going to Start –> Administrative Tools –> Data source
(ODBC). Open that up and click on the ‘System DSN’ tab.

Select the vCenter DSN and click on configure. From there you can run
through the wizard without changing anything and at the end there is ‘Test
connection button’. If that works you should be good to go

As long as your DNS servers are up to date there shouldn’t be an issue.

Should vCenter and the vCenter DB be on the same subnet as the hosts

February 8, 2012 Database, VMware One comment

Because vSphere is not a single stand-alone server, application, or isolated computing system, the pieces of the puzzle will require some form of communication between them. There are many possible configuration scenarios depending on the environment in which vSphere is being deployed.

A vCenter Server must be able to communicate with each host and each vSphere client. Furthermore, if a remote database server is utilized rather than a local instance of the database, the required TCP/IP ports for that database installation are also required.

If an instance of vCenter Server is installed on Windows Server 2008, you must either disable the Windows Firewall or make an exception to allow communication between all of the required pieces of the environment.

vCenter Server requires several ports to be open when you select a default installation. Each of these ports will be used for a different portion of the overall communications path. To enable proper communication between each of the components, consult a network engineer to ensure the appropriate ports are open for communication.

Web ports that are required to be open include the following:

Port

 

Description

80

 Required for the purpose of redirecting nonsecure requests to vCenter Server on a secure port

443

 The default port used to communicate with vSphere Client and to look for data from vSphere Web Access Client and other VMware Software Development Kit (SDK) applications such as the VI Toolkit. You can change this port, but vSphere Client and any SDK applications must use the vCenter Server name, followed by the nondefault port number

8080

 The port used by Web Services HTTP.

8443

 The port used by Web Services HTTPS

389

 The standard port number used for Lightweight Directory Access Protocol (LDAP) services. This port is used for the Directory Services component of vCenter Server. It must be available to vCenter Server, even if vCenter Server is not part of a Linked Mode Group. You can change from port 389 to any available port ranging from 1025 to 65535. This is the normal LDAP port that the vCenter Server Active Directory Application Mode (ADAM) instance listens on.

636

 Used when using vCenter in Linked Mode. This is the Secure Sockets Layer (SSL) port of the local vCenter Server ADAM Instance. It is the preferred port number, but it can also be changed to any available port ranging from 1025 to 65535.

902

 Used for multiple tasks. It is used to manage ESX and ESXi hosts and send data to them. vCenter Server also receives a heartbeat at regular intervals from hosts on port 902 over User Datagram Protocol (UDP). This port must not be blocked between vCenter Server and hosts, or between hosts. Port 902 is also used for providing remote console access to virtual machines from vSphere Client.

903

 Used in the same fashion as 902: it provides remote console access of virtual machines to vSphere Client. These ports must be open for proper communication to occur between vCenter Server and vSphere Client, as well as from vSphere Client and the ESX and ESXi hosts

vCenter and the vCenter Database

If you want or need to have vCenter and the vCenter Database on separate VLAN’s, you only need to be sure you have enough network bandwidth and speed between them so that the VC performance will not be affected

A host interacts with the vCenter Server through two host management agents: hostd and vpxa. Hostd is started on the host during ESX boot up. It is primarily responsible for bookkeeping of the host-level entities like VMs, datastores, networks, and so on. It is also responsible for implementing the host-level functions of the vSphere Infrastructure API. The vCenter Server dispatches host-related operations to a host over the Web using a SOAP interface. On the host, another agent called vpxa listens to these SOAP requests and dispatches them to hostd using the vCenter Server API. When a host is added to a vCenter Server inventory, vpxa is installed and started on the host. The resource consumption of hostd and vpxa can be monitored using esxtop.
Because vCenter Server communicates with an ESX host through the vSphere Infrastructure API using a SOAP interface, one of the key contributors to the operational latencies is the number of network hops between vCenter Server and the ESX host. If the ESX host is located multiple network hops away from the vCenter Server, the operational latencies may increase significantly. It is therefore recommended that the ESX host resides as few network hops away from the vCenter Server and the DB as possible