Transcript Managing change in the database word

Managing change in the
database world
András Belokosztolszki
6/10/2007
[email protected]
Platinum
Learn & Enjoy
www.sqlbits.com
Gold
[Put your phone on Vibrate!]
Group BY:
[Food and Drink at Reading Bowl, see you there!]
Silver
Feedback Forms:
[Voucher for £30 book on return of Form]
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]
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
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)

The Ideal World
Design the “perfect”
database
Talk to
customers
• ER
• ORM
• Normalization
• Domain restrictions
• Everything you need (tables,
views, stored procedures) is
there
Build
applications
on top
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

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!

Database Development
Development on the
database
Compare
development
database with
production/staging
database
Synchronize/Deploy
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

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
Explore: Tables
SQL Server 2000
dbo.sysobjects
dbo.syscolumns
SQL Server 2005
sys.objects
sys.columns
sys.foreign_keys
sys.default_constraints
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


Compare: Tables
Scripts: Textual comparison
 Documentation: Scripts or extended
properties
 Generated constraint names (defaults,
foreign keys, check constraints)
 2000 vs. 2005: users vs. schema

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)
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
Explore: Stored Procedures,
Views, …
SQL Server 2000
SQL Server 2005
dbo.sysobjects
sys.objects
sys.sql_modules
dbo.syscomments




sys.parameters
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
Problem: Stored Procedures (1)
They are stored as text
 Dependencies may change:

TableA
Schema binding
Stored
procedure B
– 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!
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
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
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
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
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
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

Processed Objects: Defaults

Default values for tables are parsed and processed by SQL
Server:
Input
SQL Server 2000
SQL Server 2005
2
(2)
((2))
1+2+3
(1+2+3)
(((1)+(2))+3)
cast(3 as int)
(convert(int,3))
(CONVERT([int],(3),0))

Note that there are:
–
–
–
–

Different cases
Different function calls
Extra parameters
Extra parentheses
See my blog: http://www.simpletalk.com/community/blogs/andras/default.aspx
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

CLR
One can write .Net code and execute it
from the database
 It is compiled code 
 Well, it can be very fast

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

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)
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
Assembly
CLR Procedure
Table with DATA
Synchronize: Routes
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

DECLARE @RouteLifetime INT
DECLARE @CreateRouteCommand Nvarchar(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
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

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
<2002
…
2003
…
2004
…
2005
…
2006
…
2007<
…
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] )
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

Questions




[email protected]
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/
Platinum
www.SQLBits.com
www.sqlbits.com
Gold
[Conference Web site]
www.SQLBlogCasts.com
[Becoming the premier Blogging site for SQL professionals]
Silver
www.SQLServerFAQ.com
[UK SQL Server Community Website]
UK SQL Bloggers
cwebbbi.spaces.live.com
sqlblogcasts.com/blogs/simons
sqlblogcasts.com/blogs/tonyrogerson
[email protected]
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!!