Creating a linked server for the BMC Server Automation database

To connect to the 

database from the reports data warehouse, you must create a linked server on the reports warehouse database (via the product installer or manually). The linked server is required for primary sites in Microsoft SQL Server installations and for all additional sites for all database type installations.

If the linked server is for the primary site, the linked server must connect to the 

database of the primary site. The linked server to the primary site can be created in the following ways:

• Use the installer to create the linked server during the product installation.
  The installer creates the linked server by using credentials of the data warehouse user. The warehouse user must be assigned the sysadmin role for the installer to create the linked server. You can revoke this role assignment when the installation is complete.
• Create the linked server manually. 

To manually create the linked server

1. If you have not already done so, copy the install scripts from BDSSA82-servicepackVersion-external-files.zip into a directory.
   If you extract the entire contents of BDSSA82-servicepackVersion-external-files.zip, the scripts are copied into the /BDSSA/Db_Scripts/sqlserver/schema directory, under the specified directory.
2. From the data warehouse database location, log on to SQL Server Management Studio as sa.
3. Navigate to the directory in which you extracted the install scripts.
4. Locate the create_linked_server_for_om.sql file and open it.
   The script file contains variables related to the 

   The macro unmigrated-inline-wiki-markup from Confluence is no longer available.

   database, such as user name or database name.
5. Open New Query. 
6. Copy the contents of the script file and paste the contents into the query window.

7. For each parameter in the file, provide the appropriate value for the 

   The macro unmigrated-inline-wiki-markup from Confluence is no longer available.

   database, and save the changes to a new file.

   Parameter	Description
   @om_sqlsvr_instance	The name of the SQL Server instance on which the BMC Server Automation database resides If this is a default instance on a computer, use the computer name, for example, computer1. If this is a named instance, use the computer name and instance, with the format shown in the following example: computer1\Instance1.
   @om_db_name	The name of the BMC Server Automation database
   @om_sqlsvr_login	The SQL Server login to connect to the BMC Server Automation database
   @om_sqlsvr_pwd	The clear text password for the previous login

   The following example depicts how to include these parameters in the new file:

   set @om_sqlsvr_instance=N'computer1\Instance1'
   set @om_db_name=N'bladelogic'
   set @om_sqlsvr_login=N'sa'
   set @om_sqlsvr_pwd=N'sa'
   

   Note

   Do not save your changes to the original script file; you might need that file later.

8. Run the newly created script.
9. In the left pane of SQL Server Management Studio, navigate to Server Object/Linked Server and verify that the new linked server is added.

10. In a SQL Server Management Studio query window, test the linked server by executing the following statement:

    Select * from db_site1_link.<blDatabase>.dbo.agent_state

    where <blDatabase> is the same database name you used when you replaced the <Database name for OM> variable in step 7.
    If the linked server was successfully added, a table with a description of values that indicate possible agent states is displayed. The table contents vary based on the version of BMC Server Automation used for the database.

11. Exit the SQL Server Management Studio.