The following presentations were given at events sponsored by SQL PASS:
DEC 2015 (RI SSUG)
New Backup Features in SQL Server 2014
Database administrators have been able to use transparent data encryption (TDE) since SQL Server 2008. However, backups are still saved in a format that anyone can read. This version of SQL Server now supports encrypted backups for those security conscious companies.
The data explosion of the 21st century has administrators concerned with where to save all those backups. This version of SQL Server has introduced the URL clause to the BACKUP and RESTORE commands.
This means that cheap, secure, and replicated Azure blob storage can be used store those backups.
This thirty minute talk will cover the following topics.
1 – Power Shell to create Azure objects.
2 – Playing with Cloud Berry explorer.
3 – Looking at Azure Storage explorer.
4 – Backing and restoring up using the new URL command.
5 – Failed backups may cause locked partial blob files.
6 – Create master key and server certificate.
7 – Encrypting backups using server certificate.
8 – Delete certificate. Show failed restore.
9 – Restore certificate. Show successful restore.
Sep 2015 (RI BIUG) / Nov 2015 (BCC #24) / Dec 2015 (Hartford)
Power BI Desktop Designer
You have been asked by upper management to provide new dashboards to describe how the North Wind Traders company is doing, find new business opportunities, and make sure sales are going in the right direction.
How can you leverage the Power BI Desktop Designer to complete this request?
1. Load company data into the model
2. Load additional data for new analysis
3. Use the M(ashup) language to format data
4. Create relationships between the datasets
5. Use the DAX language to add additional measures
6. Graph the results using cool visual elements
7. Collate a bunch of graphs into a dashboard presentation
8. Make the dashboard dynamic with slicers
9. Publish the results to the cloud for sharing.
Mar 2015 (BCC #23)
Database Software As A Service
Are you tired of installing, configuring and patching Microsoft SQL Server? Azure SQL database maybe a way to move some of your existing infrastructure to the cloud.
Depending upon your companies philosophy, it might be great for development, testing, and possibly production environments.
The exciting thing about this service is that new features will be released on this platform first before trickling down to the on-premise editions.
1 – Create Azure Account
2 – Create Database Server
3 – Adding IP Rules for client access
4 – Capturing the connection string
5 – Using SSMS, SSDT or SQLCMD
6 – Creating a database from TSQL
7 – Sample database with common objects
8 – Good, bad and ugly truths
9 – Exciting new features in Azure SQL DB
10 – Various Migration patterns
Feb 2015 (SNESSUG)
Basic Database Design
You just found out that a client needs a SQL database to be designed from scratch. However, your existing DBA has given two weeks notice.
This presentation is meant for the Accidental DBA that has little to no experience with creating a database from the group up.
Topics are focused on data storage structures, security, and integrity. After this presentation, you will be ready to meet that clients need.
1 – Files & File Groups
2 – Logins & Users
3 – Security using schemas
4 – Database roles for access.
5 – Table design via SSMS or TSQL.
6 – Default, Check, and NULL constraints
7 – Handling table relationships (PK vs FK).
8 – To index or not to index?
9 – Testing database integrity using DML.
Oct 2014 (SNESSUG) / Jul 2014 (SAT 302) / Jun 2014 (SAT 293) / Jun 2014 (BCC #21)
Basic Database Programming
You just found out that a client needs a SQL database to be designed from scratch. However, you existing DBA has given two week notice. This presentation is meant for the Accidental DBA that has little to no experience with creating Transaction SQL objects. After this presentation, will be ready to meet that clients need.
This presentation will review the following topics to get you ready to code.
1 – How is a view better than ad-hoc SQL?
2 – Abstracting underlying tables via a view.
3 – Avoiding DML updates to views.
4 – Using triggers to enforce business rules.
5 – Using triggers to prevent data modifications.
6 – What are the different types of functions?
7 – Simple use of each type.
8 – Using stored procedures as an application interface.
9 – Batch programming using stored procedures.
10 – How to schedule batch jobs.
May 2014 (RI BIUG) / Apr 2014 (SNESSUG)
A brief introduction to Power Tools.
In recent months, there has been a big push for self service business intelligence tools by Microsoft. The center of this universe is Microsoft Excel, a common application used by the typical data analyst.
The following four products started off as add-ins for Excel. Now, some of them are an integral part of MS Office 2013.
• Power Query
• Power Pivot
• Power View
• Power Map
It is not surprising that these four business intelligence products have a cloud offering in Office 365.
Today, we will explore how a typical BI request for the Adventure Works 2012 DW database can leverage these tools.
1 – Grabbing data with Power Query.
2 – Transforming data with Power Query.
3 – Storing data with Power Query.
4 – De-normalizing data for speed.
5 – DAX language is in the tabular data model.
6 – Power Pivot tables and charts.
7 – Adding slicers to Power Pivot.
8 – Adding the WOW effect with Power View
9 – Various Power View reports.
10 – Filtering vs drill down.
11 – Using the map to plot real world data.
12 – Story telling with tours.
Presentation and Code
Excel Worksheet (rename & uncompress)
Jan 2014 (DBA Virtual Chapter)
Designing Custom Maintenance Plans using the Ola Hallengren Scripts
Have you ever received a call from a client asking for help when their SQL Server database is corrupted? You suggest that they restore the database from the last good backup.
There is a long pause on the phone as the client states that the only backups/tapes they have are bad. Do not let this situation happen to you!
We will investigate how to build a custom maintenance plans from the ground up using Ola’s scripts as a starting point. What are the best practices for daily, weekly and monthly tasks?
This presentation includes tape rotation schemes and restoring those backups to make sure they really work. Some topics will be covered in depth while others will be given as homework.
1 – How to install the Ola Hallengren scripts.
2 – Identifying system versus user databases.
3 – Making a backup schedule (full, diff, log) based on size and business requirements.
4 – Various backup options (verify, checksum, compression, cleanup, copy only, etc).
5 – Backup read/write file groups only for data warehouse systems.
6 – Advance options to speed up the backup (file striping, buffer count, max transfer size).
7 – One missing option I would like to see added is ‘MIRROR TO’.
8 – Why backup to disk then swipe to tape?
9 – Tape rotations such as ‘Tower of Hanoi’ versus ‘Grandfather-Father-Son’.
10 – Checking the integrity of your database.
11 – Intelligent Index Maintenance; Reorganize versus Rebuild.
12 – Updating statistics on your table.
13 – Using RESTORE for FILE LIST ONLY (information) and VERIFY ONLY (integrity).
14 – How do I restore my database?
15 – Testing your backups via monthly restores.
16 – What is a tail backup? When is it used?
17 – How to do a point in time recovery?
18 – What are the best practices for daily, weekly and monthly tasks?
Nov 2013 (SNESSUG) / Nov 2013 (HARTFORD) / Dec 2013 (STAMFORD)
How isolated are your sessions?
Have you ever executed a T-SQL program that crashed due to an integrity error? Upon inspecting the job history, the error code states you have duplicate key values. However; re-running the job does not reproduce the error. You probably had an transaction isolation level issue without knowing it.
I will be covering the following topics in this presentation.
1 – Maintaining the ACID quality of transactions.
2 – How SQL Server implements transaction durability?
3 – System versus User transactions
4 – Transaction basics
5 – Exploring the various transaction modes
6 – Exclusive versus Shared locks
7 – Blocking versus Deadlocks
8 – How to detect them with my free code.
9 – How Isolation levels affect transaction behavior.
10 – What is a dirty read versus a phantom read?
At the end of the talk, you will know how to fix the above scenario by changing the isolation level.
Feb 2015 (HARTFORD) / May 2014 (SAT 294) / Mar 2013 (RI BIUG) / Sep 2013 (SAT 213) / Oct 2013 (SeaCoast)
Effective Data Warehouse Storage Patterns
Many companies start off with a simple data mart for reporting. As the company grows, users become dependent on the data mart for monitoring and making decisions on Key Performance Indicators (KPI).
Unexpected information growth in your data mart may lead to a performance impacted reporting system. In short, your users will be lining up at your cube for their daily reports.
How do you reduce the size of your data mart and speed up data retrieval?
This presentation will review the following techniques to fix your woes.
1 – What is horizontal partitioning?
2 – Database sharding for daily information.
3 – Working with files and file groups.
3 – Partitioned views for performance.
4 – Table and Index partitions.
5 – Row Data Compression.
6 – Page Data Compression.
7 – Programming a sliding window.
What are Federations in Azure SQL?
Table partitioning in Azure SQL v12.
Sep 2011 (SNESSUG) / Mar 2013 (SAT 184) / Apr 2013 (SAT 203) / Aug 2013 (SAT 235)
How to audit and prevent unwanted user actions.
Many corporations are composed of small divisions located in countries throughout the world. While you might be the lead DBA for your corporation, there are several other employees who have the keys to the kingdom. This presentation will review techniques on how to prevent and/or audit data and schema changes.
1 – Granting correct user access is vital.
2 – DML triggers to keep a DATA audit trail.
3 – DDL triggers to keep a SCHEMA audit trail.
4 – Preventing unwanted DATA modifications.
5 – Preventing unwanted SCHEMA changes.
6 – Preventing table TRUNCATIONS.
7 – LOGIN triggers to control who, what, and when.
8 – Change data capture (CDC) an alternative to triggers.
Dec 2012 (SNESSUG)
Don’t lose your integrity
This is a lightning talk covering database integrity.
I will be covering these various T-SQL techniques to enforce database integrity. A toy databases called AUTOS will be implemented two ways: one without integrity and one with integrity. The issues with the first design will be examined and corrected with the second design.
1 – Primary Keys.
2 – Foreign Keys.
3 – Unique constraints.
4 – Check constraints.
5 – Default constraints.
6 – Data type constraints.
7 – DML Triggers.
Nov 2014 (BCC #22) / Jul 2014 (SAT 302) / Oct 2012 (SNESSUG) / Oct 2012 (SAT 146)
Effective use of temporary tables
Every developer eventually comes against business logic that can’t be handled with a single simple or complex query. TSQL provides the developer with several constructs that can store temporary result sets that are passed to the next query in the script.
I will be covering these various TSQL techniques with examples using the [AdventureWorks] database as well as a toy database called AUTOS. The pros and cons of each construct will be examined. Advanced options like enabling trace flag 1118 will be explored.
1 – Derived tables.
2 – Local temporary tables.
3 – Global temporary tables.
4 – Table variables.
5 – Common Table Expressions (CTE).
6 – User databases in [tempdb].
7 – Trace Flag 1118.
Jun 2012 (SNESSUG) / Aug 2012 (SAT 158) / Sep 2012 (SAT 156) / Sep 2013 (SAT 213)
Full Text Indexing Basics
Today’s large data fields (LDF) are full of unstructured information stored in varchar, text, varbinary or xml data types. How do you write an application to search the column for patterns? Traditional SQL techniques using a column INDEX and LIKE operator result in a query plan that contains a full table scan.
I will be introducing the brother’s grimm database that has the full text of each fairy tale. I will create a full text catalog / index, select a change tracking strategy, define optional stop list / thesaurus file, and then populate the index. I will use CONTAINS and FREETEXT operators in SELECT queries to leverage the FTI. This resulting query plan performs index seek.
1 – Creating a database from scratch.
2 – Creating a table with LOB field.
3 – Loading files via BULK INSERT.
4 – Performance via traditional techniques.
5 – Creating a full text index.
6 – Performance with the full text index.
Aug 2011 (SNESSUG)
Working with bit patterns.
In today’s manufacturing environment, production lines are automated with robotics and sensors. Many of these low end microprocessors and/or integrated circuits are designed for specific tasks such as temperature and pressure control. This presentation will review how to store the memory buffer in a table and use a view to interpret the results.
1 – Using the VARBINARY data type to store the bit pattern.
2 – Storing a version number just in-case the decode changes.
3 – Breaking the pattern into registers and nibbles.
4 – Left shifting of the bits.
5 – Right shifting of the bits.
6 – Combining bits from two registers.
7 – Putting it all together with a view.
May 2011 (SNESSUG)
Designing Custom Maintenance Plans with TSQL.
Have you ever received a call from a client asking for help when their SQL Server database is corrupted? You suggest that they restore the database from the last good backup. There is a long pause on the phone as the client states that the only backups/tapes they have are bad. Do not let this situation happen to you!
We will review how to build a custom maintenance plans from the ground up using TSQL commands. What are the best practices for daily, weekly and monthly tasks? This presentation includes tape rotation schemes and restoring those backups to make sure they really work.
1 – Verify integrity of databases.
2 – Backup databases (full versus differential).
3 – Backup logs
4 – Maintain database indexes.
5 – Maintain index/column statistics.
6 – Remove older data from [msdb].
7 – Remove older backups from file system.
Mar 2011 (SNESSUG)
Leveraging Transaction SQL 2008 to solve business problems (Part I)
This presentation will review how to build a database solution from the ground up using SQL commands. What if you were a SQL Server developer at a local consulting company tasked with creating a database for a local Boy Scout of America (BSA) Troop, where would you begin? The following topics will be reviewed to make sure you have a successful data storage solution.
1 – Defining file groups for the database,
2 – Creating schemas to separate tables by functionality,
3 – Creating normalized tables to hold the BSA data
4 – Adding data integrity to the design with constraints/triggers
5 – Adding referential integrity to the design with primary/foreign keys
6 – Adding logins/users and granting rights
1 – Loading data into staging area with BULK INSERT, OPENROWSET or BCP
2 – TSQL statements for TRANSACTIONS, CONTROL FLOW, and ERROR HANDLING
3 – Writing Stored Procedures to move staging data to production
4 – Schedule Procedures using SQL Server Agent
1 – Using stored procedures to lock down CRUD (create, read, update, and delete) operations
2 – Using functions and views to look at the data differently.
To be posted.