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)