Southern New England SQL Server Users Group

January 11, 2012 Speakers

Mike Hillwig

Topic: "Operational mistakes to avoid"
NEIT East Greenwich Campus (www.neit.edu)

How often do you launch a remote desktop session to your SQL Server? And how often do you ALTER a TABLE during peak hours? These can cause performance problems. Mike Hillwig has a top ten (and growing!) list of operational mistakes to avoid and will help you understand why you should avoid them. And in many cases, he will show you how to work around them.

Biography:Mike Hillwig is a 15-year IT veteran, having spent the last ten in the world of databases. He works as a contract DBA for a Boston-based provider of software and cloud-based solutions for the financial industry where he manages customer database environments. He specializes in making these environments grow and scale, where he believes that consistency and sanity are very closely linked.

Outside of work, he is an avid blogger, a passionate cook, and dad to two Recue Pugs, Reggie and Ruby.

December 2011 Speakers

Brent Ozar

Topic: "Virtualization and SAN Basics for Database Administrators"
New place at the new East Greenwich Campus (www.neit.edu)

These two technologies can make a very big – and very bad – difference in how your SQL Server performs.

Wouldn’t it be great if you could get the real, honest lowdown from a virtualization administrator, a SAN administrator, and a DBA? Wouldn’t it be even better if one person had done all three, and could give you the pros and cons of each point of view? That person is Brent Ozar, a Microsoft Certified Master who’s been there and done that.

Biography:Brent Ozar helps companies like StackOverflow and AllRecipes go fast. He really enjoy helping developers push SQL Server to its limits, and then figure out how to make it go even faster. Doing that requires coordination between programmers, sysadmins, SAN administrators, and project managers. It takes one guy who speaks all of those languages, and does it with tact and diplomacy to avoid throwing people under the bus. That’s what Brent does. Brent has walked in your shoes. He know what it’s like to see a bunch of people pointing fingers at the database, the SAN, the hardware, the code, and nobody really having good proof. He has over a decade of experience with these niche technologies. With a solid track record of finding the real underlying problem, explaining it in fun, easy-to-understand ways, and bringing the entire team together to deliver the solution. Brent know you don’t have weeks to play politics, and Brent helps you get it done in a matter of hours.

November 2011 Speakers

Andy Novick

Topic: "Partitioning Techniques "
New place at the new East Greenwich Campus (www.neit.edu)

Partitioning techniques in SQL Server help the developer and DBA manage large quantities of data and improve query performance in a variety of situations. This presentation demonstrates the techniques used for

Partitioned Tables
Partitioned Views
Partition aligned Indexed Views

Reasons for partitioning in both OLTP and data warehouse applications will be disused along with these topics:

Partitioning Basics
Hash Partitioning
Aligned vs. unaligned indexes
Index Views Basic
Index placement in Aligned and unaligned partitioning

The topics include features from SQL Server 2000, 2005 and new features from 2008. Come learn about these powerful schema design techniques.

Biography:Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience. His consulting practice focuses on building applications, including software products that use SQL Server's capabilities to the maximum. He has developed both OLTP and data warehouse databases with multi-terabytes scale. He has particular expertise in automating data management for such large databases. Other recent projects have included ETL, Security and SQL Injection prevention. Andy is the originator of the popular "SQL Server Load-fest" event conduced in Waltham, MA.

October 2011 meeting is Cancelled

Tues, 11 OCT through Fri, 14 Oct is PASS Summit 2011 in Seattle, WA. Many speakers and some regulars of SNESSUG are attending this event and hence this month's meeting is cancelled.

September 2011 Speaker

John Miner

Topic: "How to audit and prevent unwanted user."
New place at the new East Greenwich Campus (www.neit.edu)

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. How do you prevent and audit unwanted user actions to key data?
This presentation will review techniques on how to prevent and/or audit data and schema changes. The following topics will be presented with examples.
1. Granting correct user access is vital
2. Using DML triggers to keep an DATA audit trail.
3. Using DDL triggers to keep an SCHEMA audit trail.
4. Preventing unwanted DATA modifications
5. Preventing unwanted SCHEMA changes
6. Preventing table TRUNCATIONS
7. New Feature in 2008 - change data capture

Presentation slides

Biography: John Miner has twenty years of data processing and proven project management experience, specializing in the banking, health care, and government areas. His architecture expertise encompasses all phases of the project life cycle, including design, development, implementation, and maintenance. His credentials include a Masters degree in Computer Science from the University of Rhode Island with concentrations in database technologies and programming languages. He has Microsoft Certificates for Database Administration (MCDBA) and System Administration (MCSA).

Room CT231 August 2011 Speakers DOUBLE FEATURE

John Miner

Topic: "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. The following topics will be presented with examples.
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.
Managing your data well can lead to savings in disk space as much as 75%.

Presentation slides

Biography:John Miner has twenty years of data processing and proven project management experience, specializing in the banking, health care, and government areas. His architecture expertise encompasses all phases of the project life cycle, including design, development, implementation, and maintenance. His credentials include a Masters degree in Computer Science from the University of Rhode Island with concentrations in database technologies and programming languages.
He has Microsoft Certificates for Database Administration (MCDBA) and System Administration (MCSA).

Grant Fritchey

Topic: "Seven Different Solutions for Bad Parameter Sniffing"

Parameter sniffing is a misunderstood issue on SQL Server. Most of the time parameter sniffing is helping performance on your servers. But sometimes, circumstances change and what was helping you is now hurting you, bad.
In this session we’ll gain an understanding of what exactly parameter sniffing is and why it’s usually so helpful. Then, we’ll explore how parameter sniffing can go wrong and I’ll show you seven different ways you can deal with it when it does.
You’ll bring back a wealth of knowledge so that you can identify and resolve bad parameter sniffing in your own environment. Everyone has heard about twitter, facebook and linkedin but how or why does one get started?

Biography:Grant Fritchey has twenty+ years experience in IT. That time was spent in technical support, development and database administration. He works for Red Gate Software as a Product Evangelist and writes articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. He has published two books, “Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” He is one of the founding officers of the Southern New England SQL Server Users Group and its current president and works on part-time, short-term, off-site consulting contracts.

July 2011 Speakers DOUBLE FEATURE

Southern New England SQL Server & CRM User Groups – Combined Meeting

Andy Novick

Topic: "The Biggest Loser: Database Edition"

Fat! That’s how you’d have to describe most databases these days. Gigabytes piled on megabytes until the terabytes add up and up. In one way, we think of disk space as cheap. After all you can go down to the local office supply store or on-line and buy a terabyte drive for less than $100. But can you really use just that for a database? Not of you want to get the kind of performance, redundancy and disaster recovery that are demanded of today’s applications, so you use a SAN, or a multi-drive array or solid-state storage and pretty soon you’re spending some real money. Add it up: the san, the fiber network, SSD’s, the disaster recovery system, the development system, the QA system, backup space and staff to manage it all. Pretty soon that $100 terabyte turns into around $75,000. Managing your data well can lead to savings in disk space as much as 75%.

The Biggest Looser: Database Edition presentation shows you how to put your database on a diet using the features of SQL Server: •Compression •Filtered indexes •Maintenance, REBUILD and REORGANIZE •Index management: Do you really need all those indexes? •Application Changes. Topics include •How to calculate the storage required for each row? •How to measure the actual storage of each row •How to find hidden overhead such as Snapshot Isolation bytes •How to recover extra space •Why NOT to SHRINK your database files •Why log files grow and how to keep them under control •How to shrink log files the right way •Rebuilding files to recover all the extra space

Anne Stanton

Topic: "Joining the Conversation in a world of Social Media"

Everyone has heard about twitter, facebook and linkedin but how or why does one get started?

Why do people care about this trend and what is really in it for you? This session is going to be focused not only on the social media tools and their potential integration to Dynamics CRM, but also on some of the business reasons why joining into the conversation can increase profits and reduce marketing costs.

Biography:Anne Stanton has a combination of business and technical expertise. She started her career in the 80s as a programmer working with ancient languages like Fortran 77, Basic, Turbo Pascal and Cobol and then built out her expertise as a master of software applications. Mid stride in 2005, she summarized her mix of computer science and business experience by going back to school. She completed her MBA with a sub specialty in Accounting. Anne has spent twenty six years working with technology and is still very passionate about all that it can do to help businesses excel. Her recent focus has been working with the Microsoft Dynamics xRM platform and Microsoft Dynamics CRM software. She was awarded as the 7th Microsoft MVP for CRM and has a long running blog, (www.crmlady.com) and twitter feed (crmlady) on the subject. She has worked with Microsoft Dynamics CRM as a customer, partner practice leader and consultant starting with version 1.2 which was released in 2004. Anne is currently a Principal Business Analyst for FM Global in Johnston, RI..

June 2011 Speaker

Aaron Bertrand - Senior consultant for SQL Sentry, Inc.

Topic: "What's new in SQL Server 2011 (Denali)?"

Presentation cover many of the new features coming our way in the next major version of SQL Server. From contained databases to high availability to T-SQL enhancements, we will cover the good, the bad, the missing and the ugly. Presentation includes several demos and no marketing.

Introduce people to some of the new features they will be getting in Denali. Prepare people for some negative changes (e.g. mirroring now becomes most useful in Windows Enterprise Edition only). Discuss issues of migrating / upgrading from current editions.

Biography:Aaron Bertrand is a senior consultant for SQL Sentry, Inc, makers of performance monitoring and event management software for SQL Server, Analysis Services, and Windows. He has been blogging at sqlblog.com since 2006, focusing on manageability, performance, and new features; tweets as “@AaronBertrand;” and speaks frequently at user group meetings and SQL Saturday events.

May 2011 Speaker

John Miner

Topic: "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!

This presentation will review how to build a custom maintenance plans from the ground up using TSQL commands.

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.

We will be reviewing best practices for daily, weekly and monthly tasks. This includes tape rotation schemes and restoring those backups to make sure they really work.

April 2011 Speaker

Steve Simon

Topic: "Database Design for mere Developers"

Efficient and effective database design is so important that leaving it solely to the DBAs is simply not a solution. We as developers will more than likely have to create applications to access the data stores and as such must ensure that all application related considerations are taken into account within the database design. In this session, (aimed at software developers) we shall be touching upon several concepts of special importance to the developer. These include database development "patterns and anti-patterns", final proofing of design and creating versatile deployment scripts that can and will be utilized in future endeavors.

Biography:Steve Simon is an AVP with State Street Corporation Boston. He has been involved with database design and analysis for over 20 years. Steve has presented papers at eight PASS Summits and two at PASS Europe 2009. He has just recently presented his PASS 2008 Summit presentation in Johannesburg South Africa. Steve has also presented 5 papers at the Information Builders' (IBI) International Technology Summits. He has also had 2 papers published in IBI Journal & is a regular Webinar presenter for IBI

March 2011 Speaker

John Miner

Topic: "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. Defining the Database: Defining file groups for the database, Creating schemas to separate tables by functionality, Creating normalized tables to hold the BSA data, Adding data integrity to the design with constraints/triggers, Adding referential integrity to the design with primary/foreign keys, Adding logins/users and granting rights Loading initial Data: Loading data into staging area with BULK INSERT, OPENROWSET or BCP, Quick review of TSQL statements for TRANSACTIONS, CONTROL FLOW, and ERROR HANDLING, Writing Stored Procedures to move staging data to production, Schedule Procedures using SQL Server Agent Giving Developers Access: Using stored procedures to lock down CRUD (create, read, update, and delete) operations, Using functions and views to look at the data differently. In my next presentation, I will be going over advance topics such as PARTITIONING – for keeping track of all those Christmas Trees they sell each year, FILESTREAM – for showing images of the merit badges they might earn, SPATIAL – for keeping track of GPS latitude and longitude coordinates when hiking, and XML for interacting with other systems via messages.

In my next presentation, I will be going over advance topics such as PARTITIONING – for keeping track of all those Christmas Trees they sell each year, FILESTREAM – for showing images of the merit badges they might earn, SPATIAL – for keeping track of GPS latitude and longitude coordinates when hiking, and XML for interacting with other systems via messages.

Biography:John Miner craftydba.com has twenty years of data processing and proven project management experience, specializing in the banking, health care, and government areas. His architecture expertise encompasses all phases of the project life cycle, including design, development, implementation, and maintenance. His credentials include a Masters degree in Computer Science from the University of Rhode Island with concentrations in database technologies and programming languages. He has Microsoft Certificates for Database Administration (MCDBA) and System Administration (MCSA).

February 2011 Speaker

Anne Stanton - Principal CRM Business Analyst, FM Global

Topic: "Microsoft Dynamics CRM, The Product"

Microsoft Dynamics CRM is both a Customer Relationship Management application and a development platform. It is utilized by over a million users worldwide and is available in both an On Premise and Online bundle. Microsoft Dynamics CRM is packed with all that you would expect in a CRM application including integration to Outlook, Service Tracking, basic marketing and complex relationship management. Gartner ranks it right up there with SAP, Siebel and Oracle CRM, but it also does so much more.

Developing with and Extending Microsoft Dynamics CRM is a standard practice for many of the Microsoft partners who specialize in the solution. In fact leveraging the Microsoft Dynamics xRM platform for custom software solutions that are relationship oriented is an entire niche that is growing in leaps and bounds; for instance tracking projects, parts, services or events and training and the list continues. For a great glance at some of the xRM solutions check out the xRM Showcase