Getting data from multiple databases (How to link databases)

Document created by Gordonn_Lamothe Employee on Sep 9, 2015
Version 1Show Document
  • View in full screen mode

 

How to link 2 DB Servers, to be able to run Reports using Both

[In my Example… I will link the DB2(LAMGO01-U110747) to the DB1(LAMGO01-U100928)]

 

This can be done for multiple reasons:

  • Compare data from Source and Target, for troubleshooting SQL Bridge issues.
  • Compare data from DM and EM, for troubleshooting Replication issues
  • Creating reports from multiple database, without having to replicate the data
  • Creating reports from multiple database, for data that is not replicated and /or synch by default.

 

 

Here are the steps for linking 2  Databases:

 

From the Database 1(DB1)  –Right-click on Linked Servers and Select New Linked Server…….

(you will need to be a sysadmin on both machines)

   

 

Under the General Tab on Right -      Put the Name of the Database 2 (DB2) MDB Server and Select SQL Server

 

Now click the security Tab on Right Click the Security Option that makes sense for your environment:

Click OK

 

Now both MDB’s are linked and you can run Queries based on both MDB’s.

 

 

Running Reports using the 2 Databases:

 

Since in my Example, I am on the DB1 machine when I want a DB1 table I can just use the table name.

 

When I want info from the DB2 table in need to use [Server Name].databasename.owner.tablename

 

Example of 2 tables with owner ca_itrm and 2 with owner dbo

 

How to run selects for each Database

 

A normal select command:       Select * from ca_agent where agent_type =1

 

 

DB1:                                           select  *  from ca_agent   where agent_type =1

DB2:                                           select  *  from [DB2].mdb.dbo.ca_agent where agent_type =1

 

 

This also allows you to compare the results from the DB1 and DB2

For Example :   Give me all the records from a certain table that are on DB1, but not on DB2

 

 

DB1 not on DB2:

  use mdb

select *  from ca_discovered_hardware

where dis_hw_uuid not in (select dis_hw_uuid from [DB2].mdb.dbo.ca_discovered_hardware)

and dis_hw_uuid in (select dis_hw_uuid from ca_discovered_hardware)

 

 

 

DB2 not on DB1:

use mdb

select *  from [DB2].mdb.dbo.ca_discovered_hardware

where dis_hw_uuid in (select dis_hw_uuid from [DB2].mdb.dbo.ca_discovered_hardware)

and dis_hw_uuid not in (select dis_hw_uuid from ca_discovered_hardware)

 

 

 

 

 

 

 

 

Attachments

    Outcomes