What’s new in SQL Server 2019

Now generally available, here’s what’s new.

Each version of SQL Server tends to build and add additional capabilities onto the prior version. SQL Server 2019 is no exception this trend.

Probably the most interesting enhancements are:

SQL Server 2019 introduces Big Data Clusters for SQL Server.

While you can still use SQL Server 2019 in the traditional way, it now expands to offer far more capabilities.

 

New feature or update

Details

Scalable big data solution

Deploy scalable clusters of SQL Server, Spark, and HDFS containers running on Kubernetes.
Read, write, and process big data from Transact-SQL or Spark.
Easily combine and analyze high-value relational data with high-volume big data.
Query external data sources.
Store big data in HDFS managed by SQL Server.
Query data from multiple external data sources through the cluster.
Use the data for AI, machine learning, and other analysis tasks.
Deploy and run applications in Big Data Clusters.
The SQL Server master instance provides high availability and disaster recovery for all databases by using Always On availability group technology.

Data virtualization with PolyBase

Query data from external SQL Server, Oracle, Teradata, MongoDB, and ODBC data sources with external tables, now with UTF-8 encoding support. For more information, see What is PolyBase?.

What does this mean? Consider the following scenarios:

Scenarios

SQL Server Big Data Clusters provide flexibility in how you interact with your big data. You can query external data sources, store big data in HDFS managed by SQL Server, or query data from multiple external data sources through the cluster. You can then use the data for AI, machine learning, and other analysis tasks. The following sections provide more information about these scenarios.

Data virtualization

By leveraging SQL Server PolyBase, SQL Server Big Data Clusters can query external data sources without moving or copying the data. SQL Server 2019 (15.x) introduces new connectors to data sources.

clip_image002

Data lake

A SQL Server big data cluster includes a scalable HDFS storage pool. This can be used to store big data, potentially ingested from multiple external sources. Once the big data is stored in HDFS in the big data cluster, you can analyze and query the data and combine it with your relational data.

clip_image004

Scale-out data mart

SQL Server Big Data Clusters provide scale-out compute and storage to improve the performance of analyzing any data. Data from a variety of sources can be ingested and distributed across data pool nodes as a cache for further analysis.

clip_image006

Integrated AI and Machine Learning

SQL Server Big Data Clusters enable AI and machine learning tasks on the data stored in HDFS storage pools and the data pools. You can use Spark as well as built-in AI tools in SQL Server, using R, Python, Scala, or Java.

clip_image008

Improvements:

It also provides additional capability and improvements for the SQL Server database engine, SQL Server Analysis Services, SQL Server Machine Learning Services, SQL Server on Linux, and SQL Server Master Data Services.

Resources:

What’s new in SQL Server 2019

SQL Server 2019: Not your Grandpa’s SQL Server

Posted in SQL Server, SQL Server 2019 | Leave a comment

The ConfigMgr Accidental DBA

Or, Be Your Own DBA!

An Administrator for Microsoft System Center Configuration Manager (ConfigMgr) either becomes the lead administrator for ConfigMgr because it was planned, or unplanned.

Once they assume control of ConfigMgr, in most cases, they are now the DBA (Database Administrators) as well.

database-administrator-jobs

If the ConfigMgr administrator role was planned, then fantastic! The assumption is made that with this planning the admin understands what is required to properly administer the SQL Server component. If the SQL Server component was not really considered, (I’ve seen this a few times) then this article could still help.

After all, ConfigMgr is an application (granted a complex application) that uses SQL Server as the backend to store information on clients, inventory, settings, packages, applications, etc.

If the ConfigMgr administrator role was unplanned – as in; “Your manager reports that Sam the lead ConfigMgr admin left the company to pursue making a fortune as a consultant, and you (Jane) from the help desk have been promoted to lead ConfigMgr Admin (sorry Jane ;)”. Although it could be a great opportunity for Jane. Not only is the newly minted ConfigMgr admin responsible for the CM product, they are now responsible for SQL Server.

The Accidental DBA, or Involuntary DBA, now has to administer SQL Server.

There is another consideration in writing this article, in some companies, the DBA’s administer ALL SQL Server instances. Sometimes the existing DBA’s do a great job at this, other times the DBA will start locking things down to the point where ConfigMgr no longer works, and CM admins can no longer do their jobs!

Another case reported in the community; the DBA team would take longer than 1 week to restore a CM database for Disaster Recovery. In a hierarchy, the database MUST be restored within 3-4 days. A restore greater than 1 week would be completely unacceptable (to me).

A subtitle for “The ConfigMgr Accidental DBA” could be “Be your own DBA!”. Take control of the ConfigMgr SQL Server instance and manage it yourself. If you are new to SQL Server, this article will help you.

IMO, this would be a great use case to start managing your own CM SQL infrastructure.

Now what?

The DBA (Database Administrator) role does require some unique skills. The remainder of this article is providing resources and links (which will likely be updated over time) to assist.

Some immediate suggestions:

Define very clear RTO and RTO guidelines, these will greatly help your ability to recover.

  • RTO – Recovery Time Objectives (how long will it take)
  • RPO – Recovery Point Objectives (how much data can I afford to loose)

Create a backup plan. Immediately!

Where to start?

Community articles

SQLSkils has a fantastic set of guides written for SQL Server. Writing by leading industry experts, these guides are designed to be consumed 1 day at a time for 30 days. If you are just starting out, you NEED to read and implement the SQL Server Backup section first.

Troubleshooting SQL Server: A Guide for Accidental DBAs – a free ebook is available from Redgate (some awesome tools for SQL BTW), while dated, it still contains information relevant to supporting SQL Server today.

Configuration Manager SQL Server Backup guidelines – make sure you have solid backups in place. Be sure to test restores on a regular basis.

Optimizing SQL Server for ConfigMgr – hey, it’s my blog. You’ll want to get very familiar with Ola Hallengrens’ solution. Once you have a handle on backup, you want it to run fast, right?

Microsoft SQL Server Versions List – Monitor for, and apply updates for SQL Server.

DBATools – PowerShell module. Automate SQL Server tasks…

Community support

Twitter – post your SQL related question with a #sqlhelp hashtag and you should get some assistance.

Member Note: You’re in the right place to get answers from a pretty huge community of SQL Server DBAs – that is invaluable.

Microsoft SQL Server support forums

Community suggestions

In preparing this article, I polled the twitter alias asking what the Accidental DBA needs to know. Some of these points I’ve touched on. For the thread, click here.

Notes from the Twitter alias and SQLSkills link:

You can’t do much better than starting here https://www.sqlskills.com/help/accidental-dba/ … -I would add that you should choose the order that suits you best (imho backups should be first!) #sqlskills #sqlhelp [Mark Broadbent @retracement]

Make sure you set your backup schedule so that you can meet your RTO and RPO. And test your restores. Your backups aren’t any use if they won’t restore. [Frank Gill @skreebydba]

For testing restores, sp_DatabaseRestore is handy, as you can do the restore, run CHECKDB, then drop the restored DB after in a few lines of tSQL. That reduces the space requirements if you perform for each DB serially. Working on a blog post to share an example. [Allen McGuire @SQLTrooper ] + next as well.

If you do it in an Agent job, you can have output files for each DB tested on top of the steps being logged to the CommandLog – part of the free Ola backup/maintenance solution (which sp_DatabaseRestore relies on anyway).

Posted in DBA, SCCM, SQL Server | 2 Comments

MMS 2019–Planned Sessions

MMSLogo

Are coming to one of the premier System Center Configuration Manager events, MMS 2019 (Midwest Management Summit)?

If so, consider coming to see Benjamin Reynolds and myself for one or all of the following sessions. Stop by and say hello.

AMA – Ask Me Anything

Note: we first ran one of these sessions at the MMS Desert Edition and it was very well received. Bring your questions!

Description:
Have a question about SQL, T-SQL, Reporting, or SQL/Site Server HA? Now’s your chance to ask Steve and Benjamin anything you want!
What you will learn:

  • SQL (basic or advanced)
  • SSRS
  • Power BI
  • SQL Server HA (Availability Groups)

SQL Performance turning techniques for ConfigMgr and more!

Note: we’ll likely cover suggested server and SQL Server configuration as well.

Description:
Have you ever had SQL queries that ran slow, and weren’t sure what to do? Learn techniques, tips and tricks on how to enhance your SQL Server query performance. How to troubleshoot, available tools, extended events, execution plans and more!
What you will learn:

  • Troubleshooting techniques
  • What makes a bad vs. good query?
  • Indexes – how to identify and how to use?
  • Extended events

HA for ConfigMgr/SQL Server Update

Description:
Have you wondered about SQL Always On Availability Groups? What is it? How does SQL Server High Availability benefit your environment? What considerations are involved in the design? How do you install it? Learn how can you use this to extend ConfigMgr high availability capabilities. Tips and tricks. Come learn the latest update to this technology from two industry experts.
What you will learn:

  • SQL High Availability Options – FCI vs Always On Availability Groups
  • Efficiently moving the ConfigMgr DB to an Availability Group
  • ConfigMgr HA
Posted in MMS, MMS 2019 | Leave a comment

SSRS ReportServer log file uncontrolled growth

When installing SSRS, there are two databases that get installed (default); ReportServer and ReporServerTempDB.

With SSRS, whether for SCCM or reporting in general, by default the ReportServer database Recovery model is enabled as FULL. Which means that all activity in the ReportServer database will get logged to the transaction file.

The side affect of this, the Transaction log file can get quite large with time. Recently, I assisted a client with a low disk space issue on a logical drive. After research, we determined that the ReportServer transaction log file had grown quite large… >60GB in size.

The fix? Change the ReportServer database  Recovery model to Simple. To reclaim the space on disk, shrink the ReportServer transaction log file.

To back up the ReportServer database, and perhaps custom reports; DO enable regular database backups, here is one approach.

To change the recovery model, from SSMS, right click the ReportServer database > Properties. Click on Options, then change the Recovery Model to Simple. Then, OK.

image 

To shrink the Transaction log file, right click the ReportServer database > Tasks > Shrink > Files

image

From the Shrink file dialog, choose file type of LOG. Shrink action of Release Unused space, then OK.

image

Note: Thanks Ken. Smile

Posted in SCCM, SQL Server, SSRS | Leave a comment

Microsoft MVP Anniversary – 23 years!

In cleaning out some old paperwork, I re-discovered my original MVP award letter, dated 12-13-1995. Yes, it was a real letter, mailed to me!

MVPAwardLetter

The following year, this letter from Bill Gates was included. Some very insightful comments here… one I like:

“The interactive network will allow us to work together, to instantly communicate with each other, and stay connected, no matter where we are.”

Quite a profound statement given it was 1996, and I received a letter from Microsoft the year before!

MVPLetterFromBillGates

It has been an amazing 23 years, have met a LOT of really nice people during this time. Being part of the community has had a very positive impact on my career.

I’ll also offer a shout-out to the MVP leads and the product teams that really value our opinions.

Posted in MVP | Leave a comment

SQL Server – Configure Backup on Availability Replicas

For a recent project, I needed to create a backup maintenance task for databases in a SQL Server Always On Availability group. In three separate AO environments.

Now there are several options available:

  • Use custom backup jobs
  • Use Ola Hallengren’s backup scripts
  • Use SQL Maintenance tasks

After investigating each of these options, I decided to use the SQL Maintenance tasks. Primary reason, the client won’t have to update Ola’s scripts as revisions take place, and I wanted to keep the solution simple and easily repeatable.

Using the SQL Maintenance Wizard, for each node in the SQL Availability Group, I created three (3) separate tasks.

The keys to making this work are:

  • Using a UNC location to backup the user databases and transaction logs files for each environment
  • Scheduling each SQL Server Agent Job to run at the same time
    • In the Always On environment, only one of the jobs will actually run

Here are the build notes:

SQL Server Backup

Important maintenance plan creation notes:

· Use default Availability Group backup priority options

· Use compression

· Create a folder for each database

· User Database(s) Transaction log file back up to *environment share*

· Create a folder for each database

1. On each node configure the following three maintenance plan backup tasks.

a. BackupSystemDBs

· System DB backups created locally (on each server)

· Schedule: Run daily @ 5:00PM

○ Retention 1 week

image

SQL Maintenance Task

b. BackupDB

· User Database(s) back up to *environment share*

· Use Copy-only database Option

· Note: For more information on copy-only database option

· Schedule: Run daily @ 6:00PM

○ Retention 1 week

image

c. BackupDBTrans

· User Database(s) Transaction log file back up to *environment share*

· Schedule: Run Hourly from 7:00PM to 5:30PM

○ Retention 48 hours

image

Reference: Configure Backup on Availability Replicas (SQL Server)

From <https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-backup-on-availability-replicas-sql-server?view=sql-server-2017>

Posted in High Availability, SCCM, SQL Server | Leave a comment

Failed to create Availability Group Listener

In implementing a SQL Server Always On availability group; I know I’ve run into this issue in the past, so figured I’d document the solution.

The Windows Server Failover cluster was properly created.

The SQL Server Always On Availability Group was created.

Adding the SQL Listener for the AOAG, and received the following:

image

Here is the error:

 

Error

The WSFC cluster could not bring the Network Name resource with DNS name ‘<SQLAOAGName>’ online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.

The attempt to create the network name and IP address for the listener failed. If this is a WSFC availability group, the WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. Otherwise, contact your primary support provider. (Microsoft SQL Server, Error: 19471)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.3038&EvtSrc=MSSQLServer&EvtID=19471&LinkId=20476

Solution

From Users and Computers MMC, enable Advanced Properties.

Pre-create the SQL AOAG Listener name in Active Directory.

Right check the SQL AOAG Listener name > Properties > Security tab

Add the Windows Server Cluster Name (for the SQL HA) Note: enable computer objects

Grant the Windows Server Cluster Name Full Control for the SQL AOAG Listener name

The create listener step should now work.

Posted in Always ON, High Availability, SQL Server | Leave a comment