June 2004 Melbourne SQL Server SIG - Replication Lessons from the Real World

Here's my brief overview of Tuesday's Melbourne SQL Server SIG on "Replication Lessons from the Real World", presented by Adam Thurgar at Microsoft Melbourne Offices on Chapel Street:

Adam did a great job presenting the topic (which I have absolutely zero prior experience with), and quickly established his credibility by talking about the work he does with Match.com and it's 90GB database (leaves my largest, at 2GB, looking pretty pathetic). Adam spoke candidly about problems he'd encountered and alluded to several improvements in Yukon, all the while infusing his talk with humour and trying to keep the audience involved. His presentation was mostly theory (no code, no screenshots, no demos) but he was quick to take questions from the group gathered.

The highlight of the talk for me was when he covered three or four "don't do this at home" scenarios where he was able to work around some of the limitations/correct replication mistakes using the SQL Server System Tables. I went along with a particular business need in mind, and so was able to extract enough information to make a decision (my decision was yes, we'll use replication, but I need to do some more research first). I'm looking forward to reviewing the PowerPoint slides Adam used, when they get posted (at the Australian SQL Server User Group site).

There were fewer attendees than the last time when Reporting Services was being discussed, which is no problems because bigger doesn't necessarily mean better. Myself, I struggle with large crowds and have absolutely zero networking skills. Maybe it's time to learn some!

Overall I enjoyed the night from a technical side, and I'm looking forward to the future SIG's which will be targeted at Yukon.

Upcoming June 2004 Melbourne SQL Server SIG - Replication Lessons from the Real World

Tonight is the Melbourne SQL Server SIG on replication, presented by Adam Thurgar. Replication is not something I know a great deal about - I'm hoping to learn ways to sync our production and development servers as currently I use multiple DTS jobs coded by hand and scheduled with SQL Server Agent. The DTS/SQL Agent approach is very flexible, but I want simple: whenever a new table is added, data is synchronised, and the two servers are kept exactly the same.

Hopefully replication is the answer! I've printed an article from DatabaseJournal called Setting Up Merge Replication: A Step-by-step Guide to read while I'm waiting around beforehand, to at least open my eyes to what can be done.

Anyway I should have more to report on in the coming days.

Finding Stored Procedure/Table Dependencies

Recently I had a problem that I needed to update my stored procedures to point at a new set of tables. I systematically worked through my 3 stored procedures for populating the tables, but I couldn't remember all the stored procedures that accessed them for data retrieval.

Enter information_schema.routines and a tip from Experts Exchange:

select * from information_schema.routines where routine_definition like '%yourtablename%'

The downside is that this searches the stored procedure text (well, the first 4000 characters in the routine_definition column) for the table name, so you'll get hits even where the table name is used in a comment. The upside is that dynamic SQL is accounted for.