Sql Dependency Tracker and SQL Doc: dependency across databa

Visualizes SQL Server object dependencies.

Moderators: David Atkinson, Anu Deshpande

Sql Dependency Tracker and SQL Doc: dependency across databa

Postby johnchantha » Tue Jun 12, 2012 2:59 pm

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]
johnchantha
 
Posts: 2
Joined: Tue Jun 12, 2012 2:25 pm

Postby james.billings » Wed Jun 13, 2012 6:09 pm

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!
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby johnchantha » Wed Jun 13, 2012 6:14 pm

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
johnchantha
 
Posts: 2
Joined: Tue Jun 12, 2012 2:25 pm

Postby james.billings » Wed Jun 13, 2012 7:00 pm

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:

Image

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]
james.billings
 
Posts: 1146
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.


Return to SQL Dependency Tracker 2

Who is online

Users browsing this forum: No registered users and 0 guests