Sql Dependency Tracker and SQL Doc: dependency across databa

johnchantha

Sql Dependency Tracker and SQL Doc: dependency across databases


Can SQL Dependency Tracker v 2.7 tracks objects across databases?

i.e. I have these scenario below:

serverA with tableA

serverA with viewA from tableA

serverB with ViewB from viewA (viewA from serverA)

ServerC with ViewC from viewB (viewB from ServerB).

(Please do not ask why view within a view?)

If tableA is dropped from serverA, what is the impact to other objects across the databases?

Thanks,
[/b]

James B

Thanks for your post - in answer to your query, it's unfortunately a bit of an "it depends".

I set up a simple test -

Table in Instance1\DB1 with a view that selects from it.
View in Instance2\DB2 that selects from the view in DB1 using the linked server reference.

If I add tables and views from both databases into dependency tracker, the link is correctly resolved- but having said this, we do get reports from people where this doesn't seem to correctly work.

I think it can often come down to complexities of how the objects are linked, the security settings involved, the permissions you have on both instances etc. etc., so the short answer is really to try it and see!

johnchantha

We want to know if SQL Doc and SQL Dependency Tracker can tracks
objects across databases on the same server for SQL Server 2008 R2.

Below is the sample/demo script.

As part of the reversed engineering, I only know ATable in ADatabase (see below).

If I am to drop Atable from ADatabase, what is the impact to other objects across databases BDatabase and CDatabase (see script).

My initial test appears to indicate that the current version of both SQL Doc and SQL Dependency Tracker have no functionality to perform this task.

Please advise.

Thanks
John Chantha


use master
go

create database ADatabase

use ADatabase
go

create table ATable (Id int identity(1,1) not null, IdValue varchar(10))

Insert into ATable(IdValue) values('A Value')

create view vw_A
as
select * from ADatabase.dbo.ATable
go

use master
go

create database BDatabase
go

use BDatabase
go


create view vw_B
as
select * from ADatabase.dbo.vw_B
go

use master
go


create database CDatabase
go

use CDatabase
go

create view vw_C
as
select * from BDatabase.dbo.vw_B
go

James B

Thanks for that script. I had to modify it slightly (vw_B should select from vw_A) but in any case, both tools seem to pick this up.

SQL Dependency Tracker shows this:



In addition, SQL Doc shows the reference, for instance going to vw_C shows it "uses" vw_B.

The important thing to note is to add all the DBs to the tool when working with them. If you're only adding one database I'm not sure it'll so easily resolve the other objects. Adding just ADatabase won't show the objects from BDatabase or CDatabase. It works slightly better the other way around- adding just CDatabase will show an unresolved link back to BDatabase. [/img]