Transcript SQLSaturdayMelbourne2017_MartinCatherallx

So, when did that change happen.
Recognising change, avoiding change and
embracing change.
Martin Catherall
[email protected]
www.sqldownunder.com
Sponsors
Please make sure you visit our fantastic sponsors to get your card stamped to be in the running for a
raffle prize:
Who am I?
•
•
•
•
•
Martin Catherall
Senior Consultant SQL Down Under
Data Platform MVP
PASS Regional mentor.
Former leader
– Christchurch SQL Server user group.
• Committee member (former Organiser)
– SQL Saturday South Island (Christchurch)
• Committee member (SQL PASS abstract review)
– 2014 - 2016
What this session is about
• As IT (and specifically data) people – we see a lot of
change.
– Some we expected – and maybe we wish to track.
– Some unexpected.
– Some we like to avoid.
•
•
•
•
The various change we encounter.
How we can detect that change.
Do we need to be concerned about the change.
How to be ready for change.
What We’ll Cover
•
•
•
•
•
•
•
•
Stored Procedures (objects)
Indexes and Statistics
SQL Audit
Triggers
Service Broker.
Temporal Tables.
The Query Store.
Summary.
Change, SQL – and you!
QU :- Where do we encounter change?
• Data with the database changes
• Probably the most obvious (but sometimes we need to take action)
• Meta-data changes
– Maybe our changing data had an effect on this (eg indexes)
– Maybe something else changed that had an unintended effect.
• Functionality changed
• With our database / application (refactoring – introduce bugs?)
• With SQL Server (eg. New version, update)
• Move to the cloud (eg Azure)
• External Resources Change
– (fileshares, linked servers, AD)
• Process Change
• People Change (Team Members)
• And more…..
Drop and Create (Demo)
• This can cause big problems (especially in production)
Drop and Create – How to recover
• If permission are gone, we’ll have to get them back.
– But HOW?
• Some DBAs have jobs to script these off regularly.
– These may be “out of date” – but it’s still a good start.
– Always double check though against an existing backup.
• But get up and running first if possible.
• Restore a backup (on another server)
– Script off the permissions.
• Either way – there is downtime involved
DROP and CREATE Summary
•
•
•
•
REALLY try to avoid a DROP on any object.
Try to ALTER instead.
If this is problematic then get creative!
DBAs
– Check your scripts (from developers) for DROP and CREATE.
– DO NOT RELY ON CHANGE MANAGEMENT TO PICK THIS UP
• Developers
– Be nice to your DBAs 
– Supply nice ALTER (and maybe re-runnable) scripts (ROBUST scripting)
Drop and Create - INDEX (Demo)
• This can cause big problems (especially in production)
Question
• Which statement is the best?
• Online operation NOT in all versions!
Homework
• Go have a look at In-Memory OLTP?
• The rules might just be a little different.
– No Need to ALTER INDEX REBUILD.
– If you need to add columns?
Statistics
• Do you randomly update statistics.
• Do you drop and create indexes
Triggers – different types.
• Server Trigger
– Database creation.
– Database dropped.
• Database Trigger
– Changes at a table level.
•
•
•
•
Insert.
Update.
Delete.
After.
Service Broker
• Because Triggers…..
– Often call synchronous code.
• Maybe ok on the same instance, machine, LAN
– Using linked servers may be problematic (slow)
– Calling disconnected systems may be tricky (slow and UNSAFE)
• Think webservices.
• Service Broker….
– Is asynchronous
• Great for long running code or disconnected systems.
– Can call other systems by passing XML (for instance)
– Message can be processed automatically
• Activation.
– Can call webservices and .NET code (asynchronously)
Service Broker
• Is worthwhile investing a little time in.
• CONVERSATION metaphor.
–
–
–
–
Message Type.
Contracts
Queues.
Services.
• Messages are delivered by Services to Queues.
• Queues are fully transactions.
• Messages are delivered in order
Service Broker (DEMO)
• CONVERSATIONS can be
– Within a database
– Within a Server Instance (different databases, same instance)
– Different instance
• Same network (eg WINDOWS AUTHENTICATION)
• Different networks (CERTIFICATES)
– Think mergers and acquisitions (different systems talking
Temporal Tables
ANSI 2011
compliant
No
change in programming model
CREATE temporal
TABLE PERIOD FOR
SYSTEM_TIME…
ALTER regular_table
TABLE ADD
PERIOD…
DML
SELECT * FROM
temporal
Performance
Temporal
Querying
INSERT / BULK INSERT
FOR SYSTEM_TIME
AS OF
FROM..TO
BETWEEN..AND
CONTAINED IN
UPDATE
DELETE
DDL
New Insights
MERGE
Querying
Query Store
Collects query texts (plus all relevant properties)
Stores all plan choices and performance metrics
Compile
Execute
Plan store
Runtime
stats
Works across restarts / upgrades / recompiles
Query
Store
schema
Dramatically lowers the bar for performance
troubleshooting
New Views
Intuitive and easy plan forcing
Durability latency controlled by DB option
DATA_FLUSH_INTERNAL_SECONDS
Performance
Other Methods
•
•
•
•
•
Jobs (run periodically, could miss something)
SQL profiler (deprecated – but still useful…)
Extended Events (SQL Audit uses this)
Event Notifications (via Service Broker)
Third party tools.
Call to Action !
• Get the SDU Tools
– Register at www.sqldownunder.com
• Thinks about change and what impact it has.
–
–
–
–
On you
On your team
On your company
On your users
Thanks for coming
Sponsors
Please make sure you visit our fantastic sponsors to get your card stamped to be in the running for a
raffle prize:
Thanks !
Thanks!
[email protected]
http://www.sqldownunder.com