Troubleshooting SQL Server on-premises Performance

Posted on March 29, 2018 by George Walters

Are you trying to figure out if you have SQL Server performance issues?

Is your server overloaded?

There are free tools to help you diagnose this.  Generally, I would tell you to get a support case opened, but you can do some diagnosis yourself if you wish.

Do not run in production, unless directed to by a Microsoft Support Engineer, and permitted by your organization.

The built in collection tool is called SQLDiag. The configuration tool for it is “DiagManager”.

“The SQLdiag utility is a general purpose diagnostics collection utility that can be run as a console application or as a service. You can use SQLdiag to collect logs and data files from SQL Server and other types of servers, and use it to monitor your servers over time or troubleshoot specific problems with your servers. SQLdiag is intended to expedite and simplify diagnostic information gathering for Microsoft Customer Support Services.”

https://docs.microsoft.com/en-us/sql/tools/sqldiag-utility

DiagManager will help configure SQLDiag to collect appropriate data to troubleshoot any performance issues.

Do not run in production, because it will slow it down.

“Pssdiag/Sqldiag Manager is a graphic interface that provides customization capabilities to collect data for SQL Server using sqldiag collector engine. The data collected can be used by SQL Nexus tool which help you troubleshoot SQL Server performance problems. This is the same tool Microsoft SQL Server support engineers use to for data collection to troubleshoot customer’s performance problems.”

https://github.com/Microsoft/DiagManager

How to run the tool, and collect into the output folder is detailed here.

https://github.com/Microsoft/DiagManager/wiki/Running-PSSDiag

Please zip and return the output folder to Microsoft support for further analysis.

The further analysis Microsoft does may include the following tools.  Microsoft will do the analysis, but feel free to play with these tools as well.

SQL Nexus reads output of SQLDiag to show performance issues.

https://github.com/Microsoft/SqlNexus

PAL is a tool to read perfmon files and compare against known good metrics.

https://github.com/clinthuffman/PAL

I would encourage anyone doing this performance troubleshooting to test on a test system.  Do not run in production, unless directed to by a Microsoft Support Engineer, and permitted by your organization.

Posted in Data, SQL Server | Leave a comment

Developer tools and training for SQL Server, Mobile Development, and cross platform. Where are they?

Posted on February 14, 2018 by George Walters

Are you a developer, or wish to become one?  Maybe you want to learn how to access data with modern tools.

The following are a set of links to free tools and training that can help you get started around data, mobile, and cloud.

At a high level, Microsoft has developed courseware that you can use for free:

https://academy.microsoft.com/en-us/professional-program/tracks/

There are Immersion projects, to get your feet wet.

https://github.com/Microsoft/developer-immersion-data

The Microsoft parent site for SQL Server developers is here:

https://www.microsoft.com/sql-server/developer-get-started

As an IDE, Visual Studio is superior.  Also Visual Studio Code is now available on any platform:

https://www.visualstudio.com/

Visual Studio has plugins for different areas of development.  One area is Xamarin (Cross platform mobile)

https://www.visualstudio.com/xamarin/

More than just a developer IDE, Developer Essentials gets you free Azure cloud usage, and other stuff.

https://www.visualstudio.com/dev-essentials/

Finally, EDx.org is a fantastic resource, with free training from many schools as well as Microsoft professional training. You only pay if you want a verified certificate. Auditing courses is free!

https://www.edx.org/course?search_query=microsoft

Hopefully these will help you get started on your journey of data and/or mobile development.

 

Posted in Azure, Data, Development | Tagged , , , , , | Leave a comment

Azure DataFest! March 1st, Burlington MA!

Posted on February 9, 2018 by George Walters

On March 1st, there will be an event, “Azure DataFest”, all day from 9-5 at Microsoft’s office at 5 wayside road, Burlington, MA.

If you wish to attend, please go here. We are charging 22 dollars for this event plus tax:

https://boston-azuredatafest2018.eventbrite.com

Thanks to the following user groups, who are responsible for helping put this event together. Please consider going to get connected to these great groups!

New England SQL Server User Group https://www.meetup.com/NESQLUG/

Boston SQL Server User Group https://massdatatraining.org/boston-sql/

North of Boston Azure User Group https://www.meetup.com/North-Boston-Azure-Cloud-User-Group/

Boston Azure User Group https://www.meetup.com/bostonazure/

Boston BI User Group https://www.meetup.com/Boston_BI/

Rhode Island SQL Server User Group https://www.meetup.com/Rhode-Island-SQL-Server-Users-Group/

Rhode Island BI User Group https://www.meetup.com/The-RI-Microsoft-BIUG/

Boston .NET User Group https://www.meetup.com/DevBoston/

See the source imageSee the source imageSee the source image

Posted in Azure, Business Intelligence, Data | Leave a comment

Homage to the DBA

Posted on September 19, 2017 by George Walters

To my Database Administrator friends… #Parody #Humor

“The DBA Rules” sung to the tune of “The Mob Rules” by black sabbath.

Oh come on
Restrict the database and tell the people that something's coming to call
Performance and slowness are taking a bite from the BI users, oh
You've nothing to say
They're breaking away
If you listen to Data
The DBA rules
The DBA rules
Kill the SPID and you'll be thanked, the end is always the same
Play with indexes, you burn your fingers and lose your hold of the trace, oh
It's over, it's done
The transaction began
If you listen to Data
The DBA rules
You've nothing to say
Oh, they're breaking away
If you listen to Data
Break the rebuild and stop the autogrow, the storage is overrun
Just remember it might start growing and take you right back around
You're all Data, Insights, Performance, Throughput
The DBA rules
Posted in Uncategorized | Leave a comment

Upgrading to SQL 2016 (and 2017) and Windows Server 2016

Posted on August 8, 2017 by George Walters

A lot of engineering work has gone into easing upgrades for SQL Server and Windows at Microsoft. In general, a lot of breaking changes no longer happen especially around the database engine. But what about other components? What about Windows?

Many folks are wondering where to get the documents to help guide them on software upgrades.

I compiled a few links for a support manager in Microsoft, and am sharing them here.  I think this will help you get started.

Public Docs:

What’s new in SQL 2016 (and SQL 2017 is on the left menu) https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2016

SQL Release Notes for a bunch of versions https://docs.microsoft.com/en-us/sql/release-notes/sql-server-release-notes

Upgrade to 2016 from 2008R2, 2012, 2014…. https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-sql-server

“Are you upgrading from SQL 2005?” https://docs.microsoft.com/en-us/sql/database-engine/install-windows/are-you-upgrading-from-sql-server-2005

Windows Server technical content… 2016, 2012r2, 2012, 2008r2, 2008, 2003 https://docs.microsoft.com/en-us/windows-server/windows-server-versions

Windows Server installation and upgrade https://docs.microsoft.com/en-us/windows-server/get-started/installation-and-upgrade

Posted in Business Intelligence, Data | Tagged , , , | Leave a comment

SQL Saturday! Albany July 29, 2017 – Come and learn Databases and BI!

Posted on July 23, 2017 by George Walters

Are you a data professional? Promoted into a DBA or BI role?

SQL Saturdays are free training events, put on by volunteers who deliver great content to you, the audience, all around Microsoft SQL Server, and surrounding technologies.

I am speaking at this saturday’s SQL Saturday, and have two major sessions:

Organizational Security & Compliance with SQL 2012 through 2016

Power BI: Architecture and demo

The technology stack can range from the relational engine, to Business Intelligence, Security, and out to adjacent technologies such as Powershell (for scripting), Hadoop and Big Data, and data lakes.
The people who volunteer range from Microsoft MVPs (recognized community contributors) and regular folks like yourself who got bit by the teaching bug.
Please come, and sign up here:
Note: Most of the people who teach at these events are on Twitter, and can be discovered just by searching for PASS (Professional Association of SQL Server) or SQL Saturday.
Thanks, and happy learning!
Posted in Azure, Business Intelligence, Data, Uncategorized | Tagged , , , , , | Leave a comment

Power BI: Pro Versus Premium

Posted on June 20, 2017 by George Walters

I have been sharing Power BI with customers for some time now.  A quick tour is here.

At a high level, Power BI lets you pull together one or more data sources, and create visualizations that are interactive. With our cloud capabilities, we enable english-language querying of these data models, and also offer statistical insights.

As of May 3rd, we announced some changes to the pricing and added a premium service option.

The raw components to make this work include the free Power BI Desktop, the Power BI Service, and Power BI Premium.  Reports that are to be shown outside of your organization can leverage the Power BI API for external report consumption.

Once an organization gets used to mashing together data in this desktop, they will need to share with others inside the organization.

Sharing and publishing shared content requires Power BI Pro licensing. This is a per-user license, and can be added onto Office 365, or purchased as a stand-alone option.

At some level, there may be a large number of “occasional readers” of reports.  The pricing for these people may not make sense to consume content with the per-user pro license.  For the readers at scale, Power BI Premium makes great sense.

More deep-dive links are below, around administering and provisioning Power BI and Premium capacity.  Feel free to ping me for more information!

Latest Video from Adam Saxton on announcements from the Data Insights Summit:

Power BI Premium, and what is it. (Pro versus premium tables are here)

https://powerbi.microsoft.com/en-us/documentation/powerbi-premium/

Power BI Premium white paper

http://download.microsoft.com/download/8/B/2/8B23B40C-E94B-49D6-AF19-456AC4D5DB00/Power%20BI%20Premium%20whitepaper%20v1a.pdf

A Power BI Administrator needs to “purchase” the premium capacity.

How to purchase Power BI Premium

https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-premium-purchase/

Administering Power BI in your organization

https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-administering-power-bi-in-your-organization/

Posted in Business Intelligence, Data | Leave a comment

Why migrate from Oracle to SQL Server? Especially in a hybrid scenario?

Posted on April 26, 2017 by George Walters

I was asked this question, and figured out a few things have moved forward since I thought about this in depth.

There are so many reasons why to get rid of your expensive exadata and oracle licenses, and Microsoft does offer help if you are interested.  Total cost of ownership has been shown to be much lower, and the throughput is excellent.

What I shared with my customer came to the following links:

Why else would the NASDAQ throw a 2 PETABYTE data warehouse on SQL (2014)?

SQL Server SURPASSED Oracle in the “Operational Database Systems” magic quadrant since 2014.  We have been excellent in the data warehousing category as well.

https://www.microsoft.com/en-us/sql-server/sql-license-migration

SQL 2016 is extremely fast. Check the TPC-H results for Data Warehousing

https://blogs.technet.microsoft.com/dataplatforminsider/2016/07/18/sql-server-2016-posts-world-record-tpc-h-10-tb-benchmark/

SQL Server is more secure than Oracle in terms of vulnerabilities

We also have “Always Encrypted”

“In SQL Server 2016, for the first time, you will hear about a capability that we call Always Encrypted,” he said. “This is about securing data all the way from the client, into the database and keeping it secure even when query processing is being done. At the database site, the data is never decrypted, even in memory, and you can still do queries over it.”

https://adtmag.com/articles/2016/03/10/sql-server-2016.aspx

Lufthansa used Azure to host an availability group replica of SQL Server to cut DR recovery times

http://customers.microsoft.com/en-us/story/lufthansa-systems-cuts-data-recovery-time-from-minutes

SQL in Hybrid: On-prem doing AlwaysOn to Azure example:

https://gallery.technet.microsoft.com/scriptcenter/Deploy-a-SQL-Server-5608b684

More Hybrid SQL 2016 examples

http://sqlmag.com/scaling-success-sql-server-2016/sql-server-2016-hybrid-cloud-integration

SBI Liquidity migrated from SQL 2005 to SQL 2016, and engaged Premier Support to ensure success

http://customers.microsoft.com/en-us/story/sbilm-sql-server-2016-premier-support-banking-capital-markets

Amway went to AlwaysOn in SQL plus Azure to get to a 30 second cutover time for their data.

“These data centers support about 34 terabytes of information spread across 100 instances of Microsoft SQL Server software, with the data load growing at an annual rate of about 15 percent.”

http://customers.microsoft.com/en-us/story/global-direct-seller-sees-100-percent-availability-wit

As always, feel free to reach out with thoughts or feedback on this article!

Posted in Azure, Data, Security | Leave a comment

Compliance and Security in Microsoft SQL Server 2012-2016

Posted on April 5, 2017 by George Walters

I present often to user groups and SQL Saturdays.  One topic which seems popular is the ability to secure and gain compliance in SQL Server.

This presentation is best delivered by myself or someone from Microsoft, with authority on the topics inside.

Here is my slideshare:

Please contact me if you’d like more information.

Posted in Data, Security | Tagged , , , , , | Leave a comment

Azure (and SQL) Patterns and Practices

Posted on January 5, 2017 by George Walters
Customers ask for, and NEED, guidance and patterns for deploying into Azure (And SQL).
Patterns and Practices are written down, and created by the AzureCAT team, who help customers do cutting-edge deployments on Azure!
Feel free to use this link, to help understand the different areas where Azure has been deployed, and how you can do so as well!
There are also great articles written down for SQL Server, and SQL on Azure, at SQLCAT:
Thanks!
Posted in Architecture, Azure, Data | Leave a comment