The following presentations were given at events sponsored by SQL PASS:<\/p>\n
<\/p>\n
DEC 2015 (RI SSUG)<\/span><\/strong><\/p>\n Topic:<\/span><\/p>\n New Backup Features in SQL Server 2014<\/p>\n Abstract:<\/span><\/p>\n 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. <\/p>\n 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.<\/p>\n This means that cheap, secure, and replicated Azure blob storage can be used store those backups.<\/p>\n This thirty minute talk will cover the following topics.<\/p>\n Coverage:<\/span><\/p>\n 1 – Power Shell to create Azure objects. Presentation Bundle<\/a> Sep 2015 (RI BIUG) \/ Nov 2015 (BCC #24) \/ Dec 2015 (Hartford) <\/span><\/strong><\/p>\n Topic:<\/span><\/p>\n Power BI Desktop Designer<\/p>\n Abstract:<\/span><\/p>\n 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.<\/p>\n How can you leverage the Power BI Desktop Designer to complete this request?<\/p>\n Coverage:<\/span><\/p>\n 1.\tLoad company data into the model Presentation Bundle<\/a> Mar 2015 (BCC #23)<\/span><\/strong><\/p>\n Topic:<\/span><\/p>\n Database Software As A Service<\/p>\n Abstract:<\/span><\/p>\n 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. <\/p>\n Depending upon your companies philosophy, it might be great for development, testing, and possibly production environments.<\/p>\n 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.<\/p>\n Coverage:<\/span><\/p>\n 1 – Create Azure Account Details:<\/p>\n presentation bundle<\/a><\/p>\n <\/p>\n Feb 2015 (SNESSUG)<\/span><\/strong><\/p>\n Topic:<\/span><\/p>\n Basic Database Design<\/p>\n Abstract:<\/span><\/p>\n 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.<\/p>\n This presentation is meant for the Accidental DBA that has little to no experience with creating a database from the group up. <\/p>\n Topics are focused on data storage structures, security, and integrity. After this presentation, you will be ready to meet that clients need.<\/p>\n Coverage:<\/span><\/p>\n 1 \u2013 Files & File Groups presentation bundle<\/a><\/p>\n <\/p>\n Oct 2014 (SNESSUG) \/ Jul 2014 (SAT 302) \/ Jun 2014 (SAT 293) \/ Jun 2014 (BCC #21)<\/span><\/strong><\/p>\n Topic:<\/span><\/p>\n Basic Database Programming<\/p>\n Abstract:<\/span><\/p>\n 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. <\/p>\n This presentation will review the following topics to get you ready to code.<\/p>\n Coverage:<\/span><\/p>\n 1 – How is a view better than ad-hoc SQL? presentation bundle<\/a><\/p>\n <\/p>\n May 2014 (RI BIUG) \/ Apr 2014 (SNESSUG)<\/span><\/strong><\/p>\n Topic:<\/span><\/p>\n A brief introduction to Power Tools.<\/p>\n Abstract:<\/span><\/p>\n 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.<\/p>\n The following four products started off as add-ins for Excel. Now, some of them are an integral part of MS Office 2013.<\/p>\n \u2022 Power Query It is not surprising that these four business intelligence products have a cloud offering in Office 365. <\/p>\n Today, we will explore how a typical BI request for the Adventure Works 2012 DW database can leverage these tools.<\/p>\n Coverage:<\/span><\/p>\n 1 \u2013 Grabbing data with Power Query. Screen Shots Presentation and Code Excel Worksheet (rename & uncompress) <\/p>\n Jan 2014 (DBA Virtual Chapter)<\/span><\/strong><\/p>\n Topic:<\/span><\/p>\n Designing Custom Maintenance Plans using the Ola Hallengren Scripts<\/p>\n Abstract:<\/span><\/p>\n 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. <\/p>\n 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!<\/p>\n We will investigate how to build a custom maintenance plans from the ground up using Ola\u2019s scripts as a starting point. What are the best practices for daily, weekly and monthly tasks? <\/p>\n 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.<\/p>\n Coverage:<\/span><\/p>\n 1 \u2013 How to install the Ola Hallengren<\/a> scripts. Presentation Bundle<\/a><\/p>\n <\/p>\n Nov 2013 (SNESSUG) \/ Nov 2013 (HARTFORD) \/ Dec 2013 (STAMFORD)<\/span><\/strong><\/p>\n Topic:<\/span><\/p>\n How isolated are your sessions?<\/p>\n Abstract:<\/span><\/p>\n 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.<\/p>\n I will be covering the following topics in this presentation.<\/p>\n Coverage:<\/span><\/p>\n 1 – Maintaining the ACID quality of transactions. At the end of the talk, you will know how to fix the above scenario by changing the isolation level.<\/p>\n presentation bundle<\/a><\/p>\n <\/p>\n Feb 2015 (HARTFORD) \/ May 2014 (SAT 294) \/ Mar 2013 (RI BIUG) \/\u00a0Sep 2013 (SAT 213) \/ Oct 2013 (SeaCoast) <\/span><\/strong><\/p>\n Topic:<\/span><\/p>\n Effective Data Warehouse Storage Patterns<\/p>\n Abstract:<\/span><\/p>\n 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).<\/p>\n 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.<\/p>\n How do you reduce the size of your data mart and speed up data retrieval?<\/span><\/p>\n This presentation will review the following techniques to fix your woes.<\/p>\n Coverage:<\/span><\/p>\n 1 – What is horizontal partitioning? presentation bundle<\/a><\/p>\n <\/p>\n Sep 2011 (SNESSUG) \/ Mar 2013 (SAT 184) \/ Apr 2013 (SAT 203) \/\u00a0Aug 2013 (SAT 235)<\/strong><\/span><\/strong><\/p>\n Topic:<\/p>\n How to audit and prevent unwanted user actions.<\/p>\n Abstract:<\/p>\n 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.<\/p>\n Coverage:<\/p>\n 1 – Granting correct user access is vital. Details:<\/p>\n presentation bundle<\/a><\/p>\n <\/p>\n Dec 2012 (SNESSUG)<\/span><\/strong><\/p>\n Topic:<\/p>\n Don’t lose your integrity<\/p>\n Abstract:<\/p>\n This is a lightning talk covering database integrity.<\/p>\n 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.<\/p>\n Coverage:<\/p>\n 1 – Primary Keys. Details:<\/p>\n snessug bundle<\/a><\/p>\n <\/p>\n Nov 2014 (BCC #22) \/ Jul 2014 (SAT 302) \/ Oct 2012 (SNESSUG) \/ Oct 2012 (SAT 146)<\/span><\/strong><\/p>\n Topic:<\/p>\n Effective use of temporary tables<\/p>\n Abstract:<\/p>\n Every developer eventually comes against business logic that can\u2019t 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.<\/p>\n 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.<\/p>\n Coverage:<\/p>\n 1 – Derived tables. Details:<\/p>\n presentation bundle<\/a><\/p>\n <\/p>\n Jun 2012 (SNESSUG) \/ Aug 2012 (SAT 158) \/ Sep 2012 (SAT 156) \/ Sep 2013 (SAT 213)<\/span><\/strong><\/p>\n Topic:<\/p>\n Full Text Indexing Basics<\/p>\n Abstract:<\/p>\n 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.<\/p>\n 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.<\/p>\n Coverage:<\/p>\n 1 – Creating a database from scratch. Details:<\/p>\n presentation bundle<\/a><\/p>\n <\/p>\n Aug 2011 (SNESSUG)<\/span><\/strong><\/p>\n Topic:<\/p>\n Working with bit patterns.<\/p>\n Abstract:<\/p>\n In today\u2019s 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.<\/p>\n Coverage:<\/p>\n 1 – Using the VARBINARY data type to store the bit pattern. Details:<\/p>\n presentation<\/a> <\/p>\n May 2011 (SNESSUG)<\/span><\/strong><\/p>\n Topic:<\/p>\n Designing Custom Maintenance Plans with TSQL.<\/p>\n Abstract:<\/p>\n 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!<\/p>\n 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.<\/p>\n Coverage:<\/p>\n 1 – Verify integrity of databases. Details:<\/p>\n presentation<\/a> <\/p>\n Mar 2011 (SNESSUG)<\/span><\/strong><\/p>\n Topic:<\/p>\n Leveraging Transaction SQL 2008 to solve business problems (Part I)<\/p>\n Abstract:<\/p>\n 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.<\/p>\n Database Crafting:<\/p>\n 1 – Defining file groups for the database, Data Loading:<\/p>\n 1 – Loading data into staging area with BULK INSERT, OPENROWSET or BCP Granting Access:<\/p>\n 1 – Using stored procedures to lock down CRUD (create, read, update, and delete) operations Details:<\/p>\n To be posted.<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" The following presentations were given at events sponsored by SQL PASS: DEC 2015 (RI SSUG) Topic: New Backup Features in SQL Server 2014 Abstract: 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…<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","meta":{"footnotes":""},"class_list":["post-880","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/pages\/880","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=880"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/pages\/880\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=880"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n2 – Playing with Cloud Berry explorer.
\n3 – Looking at Azure Storage explorer.
\n4 – Backing and restoring up using the new URL command.
\n5 – Failed backups may cause locked partial blob files.
\n6 – Create master key and server certificate.
\n7 – Encrypting backups using server certificate.
\n8 – Delete certificate. Show failed restore.
\n9 – Restore certificate. Show successful restore.<\/p>\n
\n <\/p>\n
\n2.\tLoad additional data for new analysis
\n3.\tUse the M(ashup) language to format data
\n4.\tCreate relationships between the datasets
\n5.\tUse the DAX language to add additional measures
\n6.\tGraph the results using cool visual elements
\n7.\tCollate a bunch of graphs into a dashboard presentation
\n8.\tMake the dashboard dynamic with slicers
\n9.\tPublish the results to the cloud for sharing.<\/p>\n
\n <\/p>\n
\n2 – Create Database Server
\n3 – Adding IP Rules for client access
\n4 – Capturing the connection string
\n5 – Using SSMS, SSDT or SQLCMD
\n6 – Creating a database from TSQL
\n7 – Sample database with common objects
\n8 – Good, bad and ugly truths
\n9 – Exciting new features in Azure SQL DB
\n10 – Various Migration patterns<\/p>\n
\n 2 \u2013 Logins & Users
\n 3 \u2013 Security using schemas
\n 4 \u2013 Database roles for access.
\n 5 \u2013 Table design via SSMS or TSQL.
\n 6 \u2013 Default, Check, and NULL constraints
\n 7 \u2013 Handling table relationships (PK vs FK).
\n 8 \u2013 To index or not to index?
\n 9 \u2013 Testing database integrity using DML.<\/p>\n
\n2 – Abstracting underlying tables via a view.
\n3 – Avoiding DML updates to views.
\n4 – Using triggers to enforce business rules.
\n5 – Using triggers to prevent data modifications.
\n6 – What are the different types of functions?
\n7 – Simple use of each type.
\n8 – Using stored procedures as an application interface.
\n9 – Batch programming using stored procedures.
\n10 – How to schedule batch jobs.<\/p>\n
\n\u2022 Power Pivot
\n\u2022 Power View
\n\u2022 Power Map<\/p>\n
\n2 \u2013 Transforming data with Power Query.
\n3 \u2013 Storing data with Power Query.
\n4 – De-normalizing data for speed.
\n5 \u2013 DAX language is in the tabular data model.
\n6 \u2013 Power Pivot tables and charts.
\n7 \u2013 Adding slicers to Power Pivot.
\n8 \u2013 Adding the WOW effect with Power View
\n9 \u2013 Various Power View reports.
\n10 \u2013 Filtering vs drill down.
\n11 \u2013 Using the map to plot real world data.
\n12 \u2013 Story telling with tours.<\/p>\n
\nSteps 1 to 12<\/a>
\nSteps 13 to 25<\/a><\/p>\n
\nDocument Bundle<\/a><\/p>\n
\npower-tools-introduction.zip.001<\/a>
\npower-tools-introduction.zip.002<\/a>
\npower-tools-introduction.zip.003<\/a>
\npower-tools-introduction.zip.004<\/a>
\npower-tools-introduction.zip.005<\/a><\/p>\n
\n2 \u2013 Identifying system versus user databases.
\n3 \u2013 Making a backup schedule (full, diff, log) based on size and business requirements.
\n4 \u2013 Various backup options (verify, checksum, compression, cleanup, copy only, etc).
\n5 \u2013 Backup read\/write file groups only for data warehouse systems.
\n6 \u2013 Advance options to speed up the backup (file striping, buffer count, max transfer size).
\n7 \u2013 One missing option I would like to see added is \u2018MIRROR TO\u2019.
\n8 \u2013 Why backup to disk then swipe to tape?
\n9 \u2013 Tape rotations such as \u2018Tower of Hanoi\u2019 versus \u2018Grandfather-Father-Son\u2019.
\n10 \u2013 Checking the integrity of your database.
\n11 – Intelligent Index Maintenance; Reorganize versus Rebuild.
\n12 \u2013 Updating statistics on your table.
\n13 \u2013 Using RESTORE for FILE LIST ONLY (information) and VERIFY ONLY (integrity).
\n14 \u2013 How do I restore my database?
\n15 \u2013 Testing your backups via monthly restores.
\n16 \u2013 What is a tail backup? When is it used?
\n17 \u2013 How to do a point in time recovery?
\n18 \u2013 What are the best practices for daily, weekly and monthly tasks?<\/p>\n
\n 2 – How SQL Server implements transaction durability?
\n 3 – System versus User transactions
\n 4 – Transaction basics
\n 5 – Exploring the various transaction modes
\n 6 – Exclusive versus Shared locks
\n 7 – Blocking versus Deadlocks
\n 8 – How to detect them with my free code.
\n 9 – How Isolation levels affect transaction behavior.
\n 10 – What is a dirty read versus a phantom read?<\/p>\n
\n2 – Database sharding for daily information.
\n3 – Working with files and file groups.
\n3 – Partitioned views for performance.
\n4 – Table and Index partitions.
\n5 – Row Data Compression.
\n6 – Page Data Compression.
\n7 – Programming a sliding window.
\n8 – What are Federations in Azure SQL?<\/strike>
\n Table partitioning in Azure SQL v12.<\/p>\n
\n2 – DML triggers to keep a DATA audit trail.
\n3 – DDL triggers to keep a SCHEMA audit trail.
\n4 – Preventing unwanted DATA modifications.
\n5 – Preventing unwanted SCHEMA changes.
\n6 – Preventing table TRUNCATIONS.
\n7 – LOGIN triggers to control who, what, and when.
\n8 – Change data capture (CDC) an alternative to triggers.<\/p>\n
\n2 – Foreign Keys.
\n3 – Unique constraints.
\n4 – Check constraints.
\n5 – Default constraints.
\n6 – Data type constraints.
\n7 – DML Triggers.<\/p>\n
\n2 – Local temporary tables.
\n3 – Global temporary tables.
\n4 – Table variables.
\n5 – Common Table Expressions (CTE).
\n6 – User databases in [tempdb].
\n7 – Trace Flag 1118.<\/p>\n
\n2 – Creating a table with LOB field.
\n3 – Loading files via BULK INSERT.
\n4 – Performance via traditional techniques.
\n5 – Creating a full text index.
\n6 – Performance with the full text index.<\/p>\n
\n2 – Storing a version number just in-case the decode changes.
\n3 – Breaking the pattern into registers and nibbles.
\n4 – Left shifting of the bits.
\n5 – Right shifting of the bits.
\n6 – Combining bits from two registers.
\n7 – Putting it all together with a view.<\/p>\n
\nmicroprocessor layout example<\/a>
\nscript<\/a><\/p>\n
\n2 – Backup databases (full versus differential).
\n3 – Backup logs
\n4 – Maintain database indexes.
\n5 – Maintain index\/column statistics.
\n6 – Remove older data from [msdb].
\n7 – Remove older backups from file system.<\/p>\n
\nscripts<\/a><\/p>\n
\n2 – Creating schemas to separate tables by functionality,
\n3 – Creating normalized tables to hold the BSA data
\n4 – Adding data integrity to the design with constraints\/triggers
\n5 – Adding referential integrity to the design with primary\/foreign keys
\n6 – Adding logins\/users and granting rights<\/p>\n
\n2 – TSQL statements for TRANSACTIONS, CONTROL FLOW, and ERROR HANDLING
\n3 – Writing Stored Procedures to move staging data to production
\n4 – Schedule Procedures using SQL Server Agent<\/p>\n
\n2 – Using functions and views to look at the data differently.<\/p>\n