Managing change in the database world

1. Managing change in the database world András Belokosztolszki 6/10/2007 Andras.Belokosztolszki@red-gate.com

3. Platinum Learn & Enjoy [Put your phone on Vibrate!] www.sqlbits.com Group BY: [Food and Drink at Reading Bowl, see you there!] Gold Feedback Forms: [Voucher for £30 book on return of Form] Silver Lunch Time Sessions: [Idera in Everest, Quest in Memphis, Grok in Chic 1 and 2] Ask The Experts[Sessions need to finish on time, take questions to the ATE area]

4. Background • Working for Red Gate Software • Software Architect/PM • SQL Log Rescue • (reads the transaction log, and allows fine grained recovery) • SQL Compare • Compares and synchronizes databases • SQL Refactor • Productivity tool for DBAs and developers

5. Agenda • Motivation • Where is the schema stored • Database vs. Creation scripts • Advantages/disadvantages • Explore the database/make it explorable • Compare (scripts/db, db/db) • Synchronize (scripts/db, db/db)

6. The Ideal World

7. Requirements Change • Natural growth of the database • More data and physical limitations • Expansion: more information is stored • Access control restrictions • Database merges (two perfect databases need to be consolidated) • Turnover, new people? New ideas? • Change for the sake of change! • Poor documentation -> Re-implementing existing functionality

8. Changes Lead to Database Evolution • Database schemata change, i.e. existing schemata need to be extended and changed • Generally there is a deployed production database and one or many “freely modifiable” development databases • There is a need to identify or track changes!

9. Database Development

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

11. Database Development With Problems

12. Where Is the Schemata Stored? ?

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

14. What Is In the Database? • Processed objects [tables]: • syntax not preserved • Textual objects [views, triggers]: • Syntax preserved • Meta data not perfectly preserved -> runtime problems • Generated objects [symmetric keys] • CLR: • hard to explore, just a DLL

15. Explore: Tables • Tables are not textual objects • Comments and formatting in the creation scripts are lost • The information is stored in several system tables and are accessible via these or via system views

16. Compare: Tables • Scripts: Textual comparison • Documentation: Scripts or extended properties • Generated constraint names (defaults, foreign keys, check constraints) • 2000 vs. 2005: users vs. schema

17. Synchronization: Tables • Tables contain data, so a drop/create is not an option • Certain operations cannot be performed using an alter statement: • Change filegroup • Change identity property • Certain data type changes (image)

18. Sync Problem: Alter Object • In certain cases you cannot alter an object • Tables need to be rebuilt when identity columns need change • Table returning functions when the returning table schema changes • Underlying CLR assembly needs to be rebuilt • Be careful, because during the rebuild you may lose your: • Permissions • Extended properties

19. Explore: Stored Procedures, Views, … • Non-CLR stored procedures, functions, views and triggers are textual objects, i.e. they are stored as text • Comments and formatting in the creation scripts are preserved • The information is stored in several system tables and are accessible via these or via system views, but meta information may be inconsistent! • This is a problem

20. Problem: Stored Procedures (1) • They are stored as text • Dependencies may change: • Stored procedures can return bad result • Stored procedures can break (and we learn this only when we try to execute them) • This is very different from compiled software! Schema binding Stored procedure B TableA

21. Problem: Stored Procedures (2) • Sysdepends and sys.sql_dependencies track dependencies (DEMO) • No, they do NOT • Alters are not updating sysdepends (2000) • Stored procedures can reference nonexistent stored procedures

22. Understanding Stored Procedures & Textual Objects • Legacy code “someone” wrote sometime • Task: understand it fast and modify it • Formatted code easier to read • Documentation can be inlined unlike in tables • Modification is by a simple alter statement? • This is often sufficient, but there are problems with CLR and object dependencies

23. Problems: Views Views are also stored as textual objects • The owner (user or schema) if not specified is resolved differently for various users! • Solution: fully qualified names (DEMO) • Underlying tables may change! select * from dbo.tableA • Columns can change -> sp_refresh!! • Columns may be dropped • Solution: Expand * and schemabinding

24. Renaming Objects • In scripts: Search and replace • In database: • Need to identify dependencies (but sysdepends is wrong) • Rename in Management Studio uses sp_rename • Sp_rename is evil! It does update only the sysobjects/sys.objects tables/views. • Do not use it, or use it with care! • Drop and create the textual object

25. Roles, Users, Permissions • The syntax for creating roles is different for 2000 and 2005 (sp_addrole vs. CREATE ROLE) • Which one should be in the scripts? • Sp_adduser in 2005 creates a schema • Good policy: grant permissions to roles only • Do not compare user members • Problem: create role transactional, but sp_addrole and sp_addrolemember are NOT

26. Explore: Defaults • Two types of defaults, and they must be handled differently during synchronization • DRI default (in the column) • Bound default – can be reused • 2000: syscomments, sysobjects, syscolumns • 2005: sys.columns, sys.default_constraints

27. Processed Objects: Defaults • Default values for tables are parsed and processed by SQL Server: • Note that there are: • Different cases • Different function calls • Extra parameters • Extra parentheses • See my blog: http://www.simple-talk.com/community/blogs/andras/default.aspx

28. Compare and Sync:XML Schema Collections • SQL Server 2005 supports XML with schema validation • XML schema collection is a set of XML schemata • Problem: it is difficult to alter this object, i.e. you can only add to it! • If you want to modify it, you must unbind it from all dependent objects, like table columns, functions, etc

29. CLR • One can write .Net code and execute it from the database • It is compiled code  • Well, it can be very fast

30. Where are the CLR objects? • The CLR assemblies are stored in the database as binary files. See sys.assemblies and sys.assembly_files • Strongly named assemblies can be easier identified based on their version number • Dependencies are much better tracked for CLR objects

31. Problem: CLR Assembly • My database has assemblyXYZ, what is in it? • Documentation? • Get it out of the database and use Reflector? • What about the dependencies (Which tables does it reference? It is like dynamic SQL)

32. CLR UDT • One can create custom user defined types using .Net • These types can be used in a table as column types • Problem: How to update an assembly? • Alter assembly works sometimes! • What is the alternative? (See SQL Compare) Assembly UDT1 Table with DATA Assembly CLR Procedure

33. Synchronize: Routes DECLARE @RouteLifetime INT DECLARE @CreateRouteCommandNvarchar(4000) SET @RouteLifetime = CASE WHEN (DATEDIFF(s, GETUTCDATE(), CAST('20070110 09:23:45.823' AS DATETIME)) < 0) THEN 1 ELSE DATEDIFF(s, GETUTCDATE(), CAST('20070110 09:23:45.823' AS DATETIME)) END set @CreateRouteCommand = N'CREATE ROUTE [routeA] AUTHORIZATION [routeA_User] WITH ADDRESS=N''TCP://localhost'', SERVICE_NAME=N''serviceA'', BROKER_INSTANCE=N''broker_instance_identifier'', LIFETIME='+ cast(@RouteLifetime as nvarchar(12))+'' exec sp_executesql @CreateRouteCommand • Routes are used by the Service Broker • Routes have a lifetime which is: • Specified in seconds that indicate time to live • Stored as an absolute value

34. Explore: Certificates And Keys • SQL Server 2005 supports Certificates and (A)symmetric keys • These are mostly generated by their create statements • Can be used for encryption and authorization • Note that there is nothing about key rotation!!! • The system tables do not contain enough information to recreate these

35. Sync: Partitions • A table that is stored on several filegroups based on a partitioning column • Indexes are also partitioned • Some filegroups can be read only • Two objects that control this: partition schemes and partition functions

36. Sync: Partitions (2) • Partition functions specify the intervals • Partition schemes specify the filegroups for these intervals • E.g. CREATE PARTITION FUNCTION pf1 ( INT ) AS RANGE LEFT FOR VALUES ( 2000, 2001 ) CREATE PARTITION SCHEME ps1 AS PARTITION pf1 TO ( [PRIMARY], [PRIMARY], [PRIMARY] )

37. Sync Problem: Partitions • The alter operation splits and merges a partition scheme, but this affects both the partition function and the partition scheme • A split and a merge is a very resource intensive operation because data is involved (moving rows from one filegroup to another) • Space limitations

38. 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-belokosztolszki/

39. Platinum www.SQLBits.com[Conference Web site] www.sqlbits.com www.SQLBlogCasts.com[Becoming the premier Blogging site for SQL professionals] Gold www.SQLServerFAQ.com[UK SQL Server Community Website] Silver UK SQL Bloggers cwebbbi.spaces.live.comsqlblogcasts.com/blogs/simonssqlblogcasts.com/blogs/tonyrogerson 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-belokosztolszki/ Feedback Forms!!