SQL Compare SQL Refactor Andrs Belokosztolszki 12032008 Andrs

SQL Compare & SQL Refactor András Belokosztolszki 12/03/2008

András Belokosztolszki • Software architect at Red Gate Software • Responsible for SQL tools: – – SQL Compare SQL Log Rescue SQL Refactor … many others • Events (User groups, SQL Bits) • SQL Server Central • Blog: http: //www. simple-talk. com/community/blogs/andras/default. aspx • Articles: http: //www. simple-talk. com/author/andr%c 3%a 1 s-belokosztolszki/

Agenda • Two case studies – SQL Compare – SQL Refactor • • Motivation Features Demo Problems

SQL Compare • Compares and synchronizes the schemata of two SQL Server databases. • Flagship product • Started as a side-project

Database Development on the database Compare development database with production/staging database Synchronize/Deploy

DEMO

Challenges • • • SQL Server 7, 2000, 2005, 2008 system tables differ Object dependencies - ordering Textual objects (with options) Defaults DBO; to read all meta information Table rebuilds (identity) Double table rebuilds (partition schemes) CLR Assemblies and rules for their alters Unnamed constraints Impossible cases (default not null)

Difficulties • Complex object dependencies – 25 object types, long dependency chains • Complex rebuild conditions • Massive databases – People. Soft: • 200, 000 stored procedures • 6. 8 M parameters • 23, 000 tables

DB Development Problems: • It is NOT compiled code with public/private qualifiers – Dependencies can be broken • • Where are these? What is using this object? Public API Can I modify this? What is in this object? Who changed this the last time? – Problems are detected during production? • • • Garbage (unused, possibly unusable code, possibly maintained) Documentation: Where to store? How to retrieve? Legacy code No versioning No audit Data

Database Development With Problems Compare Development on development databases/files • Where is the schema stored during development • Explore • What is in the database • Where is the schema stored in the database • Are two objects the same? • Processed objects (e. g. defaults and constraints) • Generated objects (e. g. symmetric keys) Synchronize/Deploy Problems: • Data • CLR Assemblies • Permissions/users

Where Is the Schemata Stored? In the database itself Creation scripts • Modified directly • Table columns are added • Stored procedures added • Some validation by SQL Server • Execute procedures • Query views • Source control integration is hard • Garbage • All development is on SQL files • Can be stored in source control • Audit log (who, when, what) • Validation is a problem • Data? Scripts need to populate data • MS Data Dude; SQL Compare 6. 0 ?

Source Control For Databases • Source control works with files, the database schema is not in files • Problem: identifying the difference between the files and the database schema itself • Problems: keeping the files in sync with the database

Reading Backup Files • Large demand • Backup format is proprietary • Database files do differ (2000, 2005, 2008)

SS 2008 Support • • Many new features and changes POWERSUM private (system function) FILESTREAM -> Table build order COLUMNSETS

Development • History (Neil, Me, team) • Other projects depending on us – – – – – SQL Data Compare SQL Dependency Tracker SQL Refactor SQL Data Generator SQL Doc SQL Prompt SQL Packager SQL Toolkit SQL Changeset • Continuous integration • 1 -1 dev/tester • Unit testing (over 10000 nightly tests, 4 -5 servers, daily reports)

SQL Refactor • Productivity tool for SQL Server – – – Lay out SQL Smart Rename Smart column rename for views and tables Smart parameter rename for procedures and functions Split Table, can also create referential integrity tables Uppercase Keywords Summarize Script Expand Wildcards in SELECT statements Qualify Object Names Find Unused Variables and Parameters Encapsulate As New Stored Procedure

DEMO

Problems and Challenges • Management Studio Extensibility – DDL Hell in. Net • • About 20 refactorings, priorities No other tools like this on the market Some refactorings are complex (Table Split) Lay Out (we support nearly 40 options, but is this enough? )

Table Split • Motivation: – The table rows are very large – The database design is changing, and an entity is separated into two with a 1: N or M: 1 – Domain restriction needs to be added retrospectively

Questions ? Andras. Belokosztolszki@red-gate. com http: //www. simple-talk. com/community/blogs/andras/default. aspx http: //www. simple-talk. com/author/andr%c 3%a 1 s-belokosztolszk