Tag Archive for DB

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

Generate Database Reports using MS Excel or MS SQL

February 7, 2013 Objective 5 Operational Maintenance No comments

images

Generating Common Database Reports

Update Manager uses Microsoft SQL Server and Oracle databases to store information. Update Manager does not provide a reporting capability, but you can use a third-party reporting tool to query the database views to generate reports.

IMPORTANT The Update Manager database does not contain information about the objects in the inventory, but contains internal inventory entity IDs. To get the original IDs for virtual machines, virtual appliances, and hosts, you must have access to the vCenter Server system database. From the vCenter Server system database, you can retrieve the ID of the objects that you want to access. To obtain the Update Manager database IDs of the objects, Update Manager adds the prefix vm- (for virtual machines), va- (for virtual appliances), or host- (for hosts)

Generate Common Reports Using Microsoft Office Excel

Using Microsoft Excel, you can connect to the Update Manager database and query the database views to generate a common report.

Prerequisites

You must have an ODBC connection to the Update Manager database.

Procedure for Microsoft SQL Server (Express Procedure below)

Note: I am using Microsoft Office 2010

  • Log in to the computer on which the Update Manager database is set up.
  • From the Windows Start menu, select Programs > Microsoft Office > Microsoft Excel.
  • Click Data > Import External Data > New Database Query.
  • In the Choose Data Source window, select VMware Update Manager and click OK
  • If necessary, in the database query wizard, select the ODBC DSN name and enter the user name and password for the ODBC database connection.
  • In the Query Wizard – Choose Columns window, select the columns of data to include in your query

excel

  • Click Next
  • For example, if you want to get the latest scan results for all objects in the inventory and all patches for an inventory object, select the following database views and their corresponding columns from the Available tables and columns pane:
  • VUMV_UPDATES
  • VUMV_ENTITY_SCAN_RESULTS
  • Click OK in the warning message that the query wizard cannot join the tables in your query.
  • In the Microsoft Query window, drag a column name from the first view to the other column to join the columns in the tables manually.
  • For example, join the META_UID column from the VUMV_UPDATES database view with the UPDATE_METAUID column from the VUMV_ENTITY_SCAN_RESULTS database view.
  • A line between the columns selected indicates that these columns are joined.
  • The data is automatically queried for all inventory objects in the Microsoft Query window.

Procedure for Microsoft SQL Server Express

Note: I am using Microsoft Office 2010

  • Log in to the computer on which the Update Manager database is set up.
  • From the Windows Start menu, select Programs > Microsoft Office > Microsoft Excel.
  • Click Data > From Other Sources > From Microsoft Query

data

  •  Click VMware vSphere Update Manager

excel

  • Choose relevant colums etc
  • For example, if you want to get the latest scan results for all objects in the inventory and all patches for an inventory object, select the following database views and their corresponding columns from the Available tables and columns pane:
  • VUMV_UPDATES
  • VUMV_ENTITY_SCAN_RESULTS

VUM

  • Click OK to the message that they cannot be joined

VUM2

  • In the Microsoft Query window, drag a column name from the first view to the other column to join the columns in the tables manually.
  • For example, join the META_UID column from the VUMV_UPDATES database view with the UPDATE_METAUID column from the VUMV_ENTITY_SCAN_RESULTS database view.
  • A line between the columns selected indicates that these columns are joined.
  • The data is automatically queried for all inventory objects in the Microsoft Query window

join

  • And this is what you will see

metadata

Generate Common Reports Using Microsoft SQL Server Query

Using a Microsoft SQL Server query, you can generate a common report from the Update Manager database.

Procedure

To generate a report containing the latest scan results for all objects in the inventory and for all patches for an inventory object, run the query in Microsoft SQL Client.

excel3

  • The query displays all patches that are applicable to the scanned objects in the inventory.

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