1 / 39

Managing change in the database world

This article discusses the challenges and best practices for managing changes in the database world. It explores the advantages and disadvantages of different approaches and provides tips for exploring, comparing, and synchronizing databases.

laverna
Télécharger la présentation

Managing change in the database world

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. Managing change in the database world András Belokosztolszki 6/10/2007 Andras.Belokosztolszki@red-gate.com

  2. 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]

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

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

  5. The Ideal World

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

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

  8. Database Development

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  37. 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/

  38. 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!!

More Related