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>

Advertisements
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

Power BI Editions Explained

We’ve recently worked with a client to get clear on licensing requirements for Power BI.

Learned some interesting facts about Power BI versions that are a bit unclear on the Microsoft site, and wanted to share them as this *will* have an impact on creating Power BI Projects.

First, lets discuss Power BI Free vs Pro

clip_image001

This chart might give the impression that with Free edition, is it possible to consume PBI dashboards created by Pro users. This is not the case!

A non Pro user will get prompted to upgrade to PBI pro, they cannot access content unless they are a Pro user.

image

There are a couple of other options – read on…

How do we position our clients that want to use PBI with all employees? Or, perhaps externally with their clients?

Power BI offers Premium capacity model, which allows this sharing of PBI dashboards with other users. Effectively, each user in the company can have Power BI Pro functionality. Three service tiers are available. PBI Premium also optionally, includes a PBI report server.

Additionally, Power BI offers a Power BI Embedded model, this is another type of capacity model with six service tiers available with varying vCores. Targeted at companies that have developer resources, the PBI dashboard, published to the App workspace, can be pushed to an iFrame using .NET or JavaScript SDKs. This in turn can be used by non Pro users, and is only limited by the number of page renders per hour. Refer to the “How to plan capacity for Power BI Embedded” white paper for a description of v-cores. Pricing calculator.

Another option includes Power BI dashboards used in SharePoint Online. I’m including this one only for completeness. While you can embed PBI visuals in SharePoint Online, and user accessing this content must be either be  PBI Pro user, or the company is licensed for PBI Premium.

While planning a Power BI project, be sure the client understands the costs involved.

Posted in Power BI Report Server, PowerBI | Leave a comment

Congratulations 2018-2019 Microsoft MVP!

Proud and honored to be awarded EMS (Enterprise Mobility + Security) MVP on Jul 1, 2018. Still humbled after 23 consecutive awards in three different technical product areas.

Such a great community, congratulations to all that were awarded, and re-awarded.

For those that did not get re-awarded, work hard and reapply!

Here is a snippet of the announcement:

image

What is an MVP?

Overview

Who are MVPs?

Microsoft Most Valuable Professionals, or MVPs, are technology experts who passionately share their knowledge with the community. They are always on the “bleeding edge” and have an unstoppable urge to get their hands on new, exciting technologies. They have very deep knowledge of Microsoft products and services, while also being able to bring together diverse platforms, products and solutions, to solve real world problems. MVPs make up a global community of over 4,000 technical experts and community leaders across 90 countries and are driven by their passion, community spirit, and quest for knowledge. Above all and in addition to their amazing technical abilities, MVPs are always willing to help others – that’s what sets them apart.

What is the MVP Award?

For more than two decades, the Microsoft MVP Award is our way of saying “Thanks!” to outstanding community leaders. The contributions MVPs make to the community, ranging from speaking engagements, to social media posts, to writing books, to helping others in online communities, have incredible impact. Key benefits to MVPs include early access to Microsoft products, direct communication channels with our product teams and an invitation to the Global MVP Summit, an exclusive annual event hosted in our global HQ in Redmond. They also have a very close relationship with the local Microsoft teams in their area, who are there to support and empower MVPs to address needs and opportunities in the local ecosystem. Other benefits include an executive recognition letter, an MSDN technical subscription, and an Office 365 subscription.

Posted in Microsoft, MVP | Leave a comment

SCCM High Availability Role

At MMS2018, one of the more interesting items was the introduction of the new SCCM HA Role (hint: this is in 1805 TP).

Now, it could be that I’m only slightly partial, given that it was one of sessions that I jointly presented with Kerim Hanif and Benjamin Reynolds.

After we got done covering the existing SCCM High Availability capabilities (good background here); Kerim did a great job describing and demonstrating this role.

If we examine the following architecture:

image

You’ll note that the content library was moved to an SMB share (one additional step to make this HA and new functionality). Also, note that SQL Server Always On Availability Groups are added for SQL HA.

Another Server (Primary2) is added to the SCCM hierarchy with the new Site Server HA role added. Initially, it will be operating in Passive Mode.

Tip: this approach can be used to rename a site server, or upgrade the site server OS!

Once the new role, on the new server, is running the original SCCM site will operate in Active mode.

image

The process to promote (failover) from Primary1 to Primary2 is presently manual, and the net result will be the second server will now be operating in Active Mode.

image

This framework opens a lot of possibilities for future enhancements in the realm of High Availability!

When time permits, I’ll cover the steps involved in taking advantage of this functionality.

Posted in High Availability, SCCM | 4 Comments

Giving Back – T-SQL Tuesday

This month’s topic is giving back to the community, or if you routinely give back, how did you get started?

For background information.

clip_image002

I’ve been fortunate in many ways to be acknowledged as an MVP, now for about 23 consecutive years! How did that start?

Years ago, in a distant galaxy, we used CompuServe to gain access to online services. In a company role as Product Manager for Microsoft Access, I started closely monitoring all threads on Access to learn more about the product. After a while, I started noticing answers that were less than ideal… in fact some would have caused additional issues. In turn, I would answer the original poster, giving the best information I had. In time those replies, were noticed by Microsoft.

While at a conference in Phoenix AZ, sometime mid-1995, I was approached by a Microsoft PM, who read my name badge, and said “You are going to become an MVP”. Knowing very little about the MVP program (this was only the 2nd yr. of the MVP program), I asked what is that!? He explained the program to me, and later that year (Dec 95) I received my first MVP welcome kit, and a letter (still have it, may have to scan it at some point).

Fast forward, I moved from MS Access into the SQL Server MVP camp when it appeared that no further development was going to occur to Jet (the Access DB engine). Trivia time – how many know that Jet blue was used in DNS, WINS and other infrastructure services? Smile

While a SQL Server MVP, I was given an opportunity to design and deploy a large SMS 1.2 (Systems Management Server) environment for an insurance company. Throughout this time, I focused on participating in the community forums to share knowledge, and through sharing, I found I often learned more! In time, this involvement led to moving from the SQL Server group to SCCM MVP community, generally with a focus on SQL.

Getting involved as a conference speaker has been a tremendous benefit; not only for my career advancement, but for the connections and friendships that have been forged. I’ve been fortunate to have had the opportunity to have spoken at many conferences through the years; Microsoft Management Summit, Microsoft TechEd and the Midwest Management Summit (will be there next week).

My advice to those that would like participate in the community; start small, we all have something that we can share, someone that can benefit by what we know. If you are not, start blogging, create a twitter account, and get involved. Most of all have fun.

Posted in MVP, SQL, T-SQL Tuesday | Leave a comment

Enhancing WSUS database cleanup performance SQL script

There have been many reports of slow and sluggish clean up and removal of expired updates. An old WSUS database can include 1000’s of updates that need to be removed.

Turns out that adding two non-clustered indexes on SUSDB can DRAMTICALLY improve removal of expired updates.

This is a fine example of a community solution.

Some background reading:

Wsus Delete Obsolete Updates – where the key is the addition of the added indexes to SUSDB.

Fixing WSUS – When the Best Defense is a Good Offense – Johan does an excellent job of documenting the many things folks have tried, with varying degrees of success.

Finally, an awesome solution that includes creating the Indexes needed if they do not exist. Heavily commented. Copy and paste the following into SQL Server Management Studio.

Authored by Benjamin Reynolds (Microsoft) – be sure to follow him on Twitter.

USE [SUSDB];

DECLARE @BatchSize int = NULL; -- NULL = do all;  <<-- Control batches here

SET NOCOUNT ON;

-- Check to see if the delete indexes exist; if not don't try to run the script:
DECLARE @IndexRetry bit = 1;
IndexRetry:
IF INDEXPROPERTY(OBJECT_ID(N'tbRevisionSupersedesUpdate'),N'IX_tbRevisionSupersedesUpdate',N'IndexID') IS NULL
   OR INDEXPROPERTY(OBJECT_ID(N'tbLocalizedPropertyForRevision'),N'IX_tbLocalizedPropertyForRevision',N'IndexID') IS NULL
GOTO NeedIndexes;

-- Create tables/variables:
DROP TABLE IF EXISTS #Results; -- This will only work on newer versions of SQL Server 2016+
DECLARE  @UpdateId int
        ,@CurUpdate int
        ,@TotalToDelete int
        ,@Msg varchar(2000);
CREATE TABLE #Results (RowNum int IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL, UpdateId int NOT NULL);
INSERT INTO #Results (UpdateId)
EXECUTE dbo.spGetObsoleteUpdatesToCleanup;

-- If a batch size was provided update the table so we only take care of that many items during this run:
IF @BatchSize IS NOT NULL
DELETE #Results
 WHERE RowNum > @BatchSize;

-- Assign working variables:
SELECT @TotalToDelete = MAX(RowNum)
  FROM #Results;
-- 
SELECT @Msg = 'Total Updates to Delete: '+CONVERT(varchar(10),@TotalToDelete);
RAISERROR (@Msg,0,1) WITH NOWAIT;

-- Create the loop to delete the updates one at a time:
WHILE EXISTS (SELECT * FROM #Results)
BEGIN
    -- Grab the "current" item:
    SELECT  TOP 1 @CurUpdate = RowNum
           ,@UpdateId = UpdateId
      FROM #Results
     ORDER BY RowNum;
    
    -- Provide some info during the script runtime:
    SELECT @Msg = CONVERT(varchar(30),GETDATE(),20) + ': Deleting ' + CONVERT(varchar(5),@CurUpdate) + '/' + CONVERT(varchar(5),@TotalToDelete) + ' = ' + CONVERT(varchar(10), @UpdateId);
    RAISERROR(@Msg,0,1) WITH NOWAIT;
    
    -- Delete the current update from the DB:
    EXECUTE dbo.spDeleteUpdate @localUpdateID = @UpdateId;
    
    -- Delete the current update from the table so we can get the next item:
    DELETE #Results
     WHERE RowNum = @CurUpdate;
END;
GOTO EndScript;

NeedIndexes:
-- If the indexes don't exist we'll try to create them and start over or end if we already tried once:
IF @IndexRetry = 0
BEGIN
    PRINT N'Indexes Required to run this script do not exist! Create them and re-run for optimal performance!';
    GOTO EndScript;
END;
ELSE
BEGIN
    IF INDEXPROPERTY(OBJECT_ID(N'tbRevisionSupersedesUpdate'),N'IX_tbRevisionSupersedesUpdate',N'IndexID') IS NULL
    BEGIN
        SELECT @Msg = CONVERT(varchar(30),GETDATE(),20) + ': Index "IX_tbRevisionSupersedesUpdate" does not exist; Creating it...';
        RAISERROR(@Msg,0,1) WITH NOWAIT;
        EXECUTE (N'USE [SUSDB]; CREATE NONCLUSTERED INDEX IX_tbRevisionSupersedesUpdate ON dbo.tbRevisionSupersedesUpdate(SupersededUpdateID);');
        SELECT @Msg = CONVERT(varchar(30),GETDATE(),20) + ': ..."IX_tbRevisionSupersedesUpdate" created.';
        RAISERROR(@Msg,0,1) WITH NOWAIT;
    END;
    IF INDEXPROPERTY(OBJECT_ID(N'tbLocalizedPropertyForRevision'),N'IX_tbLocalizedPropertyForRevision',N'IndexID') IS NULL
    BEGIN
        SELECT @Msg = CONVERT(varchar(30),GETDATE(),20) + ': Index "IX_tbLocalizedPropertyForRevision" does not exist; Creating it...';
        RAISERROR(@Msg,0,1) WITH NOWAIT;
        EXECUTE (N'USE [SUSDB]; CREATE NONCLUSTERED INDEX IX_tbLocalizedPropertyForRevision ON dbo.tbLocalizedPropertyForRevision(LocalizedPropertyID);');
        SELECT @Msg = CONVERT(varchar(30),GETDATE(),20) + ': ..."IX_tbLocalizedPropertyForRevision" created.';
        RAISERROR(@Msg,0,1) WITH NOWAIT;
    END;
    
    SET @IndexRetry = 0;
    GOTO IndexRetry;
END;

EndScript:
DROP TABLE IF EXISTS #Results;

Posted in SCCM, SQL Server, Uncategorized, WSUS | 17 Comments