1 / 20

SQL Compare & SQL Refactor

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:

axel-montgomery
Download Presentation

SQL Compare & SQL Refactor

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. 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%c3%a1s-belokosztolszki/

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

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

  5. Database Development

  6. DEMO

  7. 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)

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

  9. 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

  10. Database Development With Problems

  11. Where Is the Schemata Stored? ?

  12. 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

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

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

  15. 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)

  16. 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

  17. DEMO

  18. 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?)

  19. 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

  20. Questions ? Andras.Belokosztolszki@red-gate.com http://www.red-gate.com http://www.simple-talk.com/community/blogs/andras/default.aspx http://www.simple-talk.com/author/andr%c3%a1s-belokosztolszk

More Related