Introduction
SQL Server is being adopted by an ever wider user base. The upgrade to SQL Server 2005 has delivered a great deal more functionality and extensibility, and this has resulted in many more and larger companies rolling out the database platform right across their organization.
One typical result of scaling up a database deployment is that more people end up messing with the database. Adding humans also adds human errors, and so scaling up an installation usually brings with it the likelihood of unscheduled (and unintended) dependency impacts, crashes, and concomitant downtime.
A recent survey by Software Development Times (March 2006) revealed that in well over half of change management processes, the task of analyzing object dependencies and the likely knock-on effects of a schema change is carried out manually and in an ad hoc way. Over 20% of respondents said they rely on the personal memory of the guy who wrote the database.
So what systems and tools are there to provide clarity, confidence, and insurance against the impact of personnel changes or structural reorganization? SQL Dependency Tracker from Red Gate Software (maker of SQL Compare amongst other tools) makes it possible for anyone to view and understand a clear map of the database and its dependencies, across linked databases and even other servers.
Using a proprietary engine, (entirely independent of the sysdepends table) and offering reconfigurable layouts, it�s now simple for anyone in a database development and administration team to produce and save a graphical report showing all the objects in a given database, the column names present in each object, and the dependent relationships between those objects.
This is a very useful function for a range of different database tasks, including:
- recording database version history
- analyzing imminent changes to the schema
- mapping a dependency tree from an initial target object
- reporting to other team members or upwards through the organization
Selecting target objects
Setting up a dependency map using SQL Dependency Tracker is very simple. It opens with an �empty map� screen, and invites you to add objects to the project using the Add Objects wizard.
The dialog displays all the locally available SQL Servers and the databases present in each. On selecting a database, the lower window displays its constituent objects.
Let�s imagine you�re a development DBA who needs to add a column to a table so that credit card verification numbers can be stored. The numbers will need to be stored along with the credit card numbers as well as with the customer names. So adding this column to one table will necessitate changes to dependent tables, and not necessarily in the same database.
In this example, we�ll start by selecting just one table � the CustomerCards table - from a database called Northcards, where we store customer credit card details.
Hitting the Add Selection to Project button launches the layout engine, and your selected object will be mapped, along with any connected objects. In the snapshot below, we can see that the CustomerCards object has been added to the layout, with dependent objects grouped around it. An externally referenced object from another database (indicated by the green arrow here) has also been added. The Customers table from the Northwind database has been added because it is used by two stored procedures � sp_getnamesforcard and sp_createcustomercard � that also use our original CustomerCards table.
By right-clicking this external reference, we can add objects that are used by it. The antenna on the left hand side of the object indicates that there are such objects, and rolling over the object shows a tooltip with the names of those objects.
Tracking the dependencies
Let�s add the dependent objects to our diagram. Clicking on Reapply Layout will do just that, by producing an easier-to-read grid. As the number of objects increases, it is useful to be able to choose a different type of layout from the five available, for the sake of easily understanding the relationships between objects. For example, a hierarchical layout can be especially useful as it places different levels of dependencies at different levels in the graph, as illustrated in the picture below.
When you select an object and right-click it, there is also an option to Show the SQL Script for that object.
A quick inspection of the SQL scripts will tell you what parameters may need adjusting in which objects as a consequence of the new column you are planning to add to the CustomerCards table.
When you�re happy you�ve identified all the impacts that are going to occur as a result of your schema change, you can save a record of the state of the database and its dependencies exactly as they are at the time of the change. Simply click the Copy button in the toolbar, and the current grid diagram will be copied to the clipboard for you to paste wherever you need to save it, for example, in a spreadsheet or in MS Word. You can also export the diagram, either as a PNG image, or as an XML file.
Parallel text-based tracking
Returning to SQL Dependency Tracker, on the right-hand side of the screen are two key window-panes. Top right is the Objects in Project pane, itemising all the objects currently added to the project. Clicking on an object � e.g., the Orders Qry view in the picture below � populates the bottom-right Dependencies pane with all the objects that both use and are used by that object. This can be a fast and simple way to track objects when a graphical display is not required. You can also make the diagram even easier to understand at a glance, by selecting and highlighting certain objects, for example, stored procedures or views, according to your preferred color-coding scheme.
Now that you have identified any dependency issues, you can use Red Gate SQL Compare and SQL Data Compare to compare and synchronize your databases, with complete confidence that no hidden dependency surprises will crop up and cause problems.
