SQL Server – Availability Group Issue

Spent part of a day chasing down an SQL Server Always On Availability Group manual failover, failure issue.

When running the failover wizard, the failure looks like this:

SNAGHTML445909

Some of the error description: Manual failover failed. Failed to perform a manual failover of the availability group ‘x’ to server instance ‘y’. Error code 5015.

Resolution:

The Availability Group and the Listener Name were named identically. That was causing the failure, to Windows Cluster, it was a name conflict as both resources could not be shared.

Renamed the AG name, problem solved!

Installing and configuring SQL Server for Configuration Manager

This guide is intended to assist administration in properly configuring SQL Server prior to installing Configuration Manager.

Too many make the mistake of miss-configuring SQL Server, then need to spend a LOT of time later, correcting it.

Article Contents

Installing and configuring SQL Server for Configuration Manager.

Planning.

ConfigMgr 2012 R2 Database Sizing Estimator.

How are we going to configure SQL.

Cap log files.

Other databases.

Conclusion.

SQL Server Installation.

Install SQL Server

Feature selection.

Instance Configuration.

Server Configuration Collation.

Install SQL Server Service Pack and latest Cumulative Update.

Install SQL Server 2012 Sp2.

Install SQL Server 2012 Sp2 CU4 (latest cumulative update).

Configure SQL Server.

Max memory setting.

Change location of TempDB.

Pre-create the ConfigMgr 2012 R2 database.

Post ConfigMgr 2012 R2 Installation Tasks.

 

Planning

You will want to install SQL Server on the same server that you plan on installing ConfigMgr 2012. If you need some background on why installing ConfigMgr 2012 on a remote SQL Server is a really bad idea, please read the following.

For a smaller ConfigMgr 2012 site, with perhaps less than 5,000 clients, the following server Physical or Virtual platform specifications will perform well.

ConfigMgr Site Server Specifications

Ø Windows Server 2012 R2

Ø SQL Server 2014

Ø 4 x CPU Cores

Ø 32GB memory (minimum)

Site Server Drive configuration guidelines:

Logical Drive

Minimum Size

(GB)

Purpose

C:

100

OS + *Page file

E:

100

Applications + Database

F:

50

Log Files

G:

500

SCCM Content OS images, app packages, etc.

*P:

50

Page File

*T:

50

Temp DB

*U:

50

Backups

*Note: Optional configuration for higher performance and/or higher client counts

It is recommended that a separate server be installed to provide the Distribution Point and Management Point roles. The following server Physical or Virtual platform specifications are indicated.

ConfigMgr Distribution Point Specifications

Ø Windows Server 2012 R2

Ø 2 x CPU Cores

Ø 8GB memory (minimum)

Distribution Point Drive configuration guidelines:

Logical Drive

Minimum Size

(GB)

Purpose

C:

100

OS + Page File + Binaries

E:

500

SCCM Content

ConfigMgr 2012 R2 Database Sizing Estimator

Prior to installing ConfigMgr 2012, It is recommended to pre-create the ConfigMgr 2012 database and pre-size the TempDB databases. One challenge, determining the correct size for the databases.

Shown is an example of the Database sizing worksheet, based on estimated client counts:

image 

Anthony has documented this approach as follows:

http://configmgr.com/configmgr-sql-server-sizing-and-optimization/

Using the estimator to calculate the number and size of the data files, begin the SQL Server Installation process.

SQL Server Installation

Prior to SQL Server installation:

Drives dedicated for SQL Server data files or log files, such as TempDB should be formatted to NTFS 64KB block size.

Reference: https://stevethompsonmvp.wordpress.com/2014/07/25/sql-server-ntfs-cluster-size/

Server 2012 platform – Install File Services & .NET 3.5 framework (required for SQL install)

Install SQL Server 2012 or SQL Server 2014

Note: example is from SQL Server 2012, however, SQL Server 2014 is very similar.

Browse to SQL Server 2012 Sp1 install media, right click setup.exe, run as administrator. Only key dialogs will be shown for this installation.

Feature selection clip_image003

Feature selection – choose the above options.

Instance Configuration

clip_image004

Instance Configuration – take the Default Instance option.

Change the Instance Root directory location (assuming you’ve followed the drive suggestions) to:

E:\Microsoft SQL Server

Server Configuration Collation

clip_image006

Collation – default should be correct, however, verify that ‘SQL_Latin1_General_CP1_CI_AS’ is selected.

Default security – Windows Authentication, add <SERVER Name>\Administrators & <DOMAIN Name>\Domain Admins for Sysadmin permissions. Note: there may be some variation for groups based on client requirements.

Set SQL Server Agent service startup type to Automatic.

Install SQL Server Service Pack and latest Cumulative Update

Note: example, use the latest supported SP and CU!

Install SQL Server 2012 Sp2

Recommend downloading SQL Service pack to a folder on the server to be updated. Browse to SQL Server 2012 Service Pack, right click setup.exe, run as administrator

clip_image008

Click Next>

clip_image010

Click "I accept the license terms" and Click Next >

clip_image012

Accept defaults, and Click Next >

clip_image014

When checking files is complete, and Click Next >

clip_image016

Ready to Update, and click Update

clip_image018

Completion Dialog, all features should show a status of Succeeded

clip_image020

To verify installation results, review SQL Server log directory, navigate to:

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log

Within that root folder, locate the Summary.txt file, review this file for component success status and proper SQL version.

Exit code = 0

SQL Server 2012 Sp2 build level = 11.2.5058.0

Install SQL Server 2012 Sp2 CU4 (latest cumulative update)

Note: Install latest SQL CU (reference link at end of this chapter)

Recommend downloading SQL Service pack to a folder on the server to be updated. Browse to the folde containing the hotfix, Right click setup.exe, run as administrator.

clip_image022

Setup Support Rules; and Click OK

clip_image024

And, Click Next >

clip_image026

Click "I accept the license terms" and Click Next >

clip_image028

Accept defaults, and click Next >

clip_image030

When checking files is complete, and Click Next >

clip_image032

Ready to Update, and click Update

(Note: highlight below – shows current patch level of SP2)

clip_image034

Completion Dialog, all features should show a status of Succeeded

clip_image036

Review Summary.txt

Exit code should read 0

Restart Server.

Verify that MSSQLServer service and SQL Server Agent services are running.

Summary

Always check for the latest updates!

Reference: https://technet.microsoft.com/en-us/sqlserver/ff803383.aspx

Configure SQL Server

Now that SQL Server is installed, it is time to configure SQL for optimal performance. In this section we will:

Ø Cap maximum memory

Ø Increased default size of TempDB and modified default file growth

Ø Give server administrators and domain admins Sysadmin equivalence

Ø Optionally, Install and configure SSRS (SQL Server Reporting Services)

o Increase default size of ReportingServer database and modified default file growth

o Change recovery mode to simple

Max memory setting
SQL Server Properties – Memory

clip_image038

Server properties > set min memory usage (8GB total)

Server properties > set max memory usage(32GB total – 8 GB = 26 GB for SQL)

Note: 26 GB * 1024 = 26624 MB

SQL Server Properties – Database Settings

clip_image040

SET Database Default Data, Log & Backup locations (as appropriate)

Change location of TempDB

From SSMS (SQL Server Management Studio), Start a New Query.

1. To get current location and names of tempdb files

Copy and Paste the following:

— To get current location and names of tempdb files

SELECT name, physical_name AS CurrentLocation

FROM sys.master_files

WHERE database_id = DB_ID(N’tempdb’);

GO

2. Change the location of each file by using ALTER DATABASE. Modify file paths as appropriate.

Copy and Paste the following:

USE Master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = ‘E:\SQLData\tempdb.mdf’);

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = ‘F:\MSSQL\LOGS\templog.ldf’);

GO

Rerun query from step 1 to validate modified location:

— To get current location and names of tempdb files

SELECT name, physical_name AS CurrentLocation

FROM sys.master_files

WHERE database_id = DB_ID(N’tempdb’);

GO

3. Stopping and restarting MSSQLServer will recreate the TempDB files in their new locations.

4. To set initial size of TempDB from 8MB to the estimated size of tempdb, from prior step. From SSMS, expand System Databases node, right click TempDB database and select Properties

clip_image042

Files Option: Set TEMPDB initial size (based on estimated size – this should be approximately 25-30% of the size of the estimated ConfigMgr database) and autogrowth file setting. Autogrowth should be set to either 1024KB, or a minimum of 512KB.

TempDB Data files: optionally, create a minimum of 4 to a maximum of 8 data files (see references)

Reference: https://stevethompsonmvp.wordpress.com/2014/12/09/why-default-database-autogrowth-settings-are-bad/

https://stevethompsonmvp.wordpress.com/2016/02/05/proper-tempdb-creation-for-configuration-manager/

Pre-create the ConfigMgr 2012 R2 database

Based on the previous size estimation, create a database to the total estimated size.

From SSMS, right click databases > New Database…

New Database properties

clip_image044

Database name: CM_<Site Code>

Owner: sa

Database Files: For both Data & Log files – Enter initial size, autogrowth, Path (as appropriate) & file name

New Database Properties

clip_image046

Select Options (on left)

Change recovery model to ‘Simple’

Then, click OK

Firewall ports

Windows server – if you are using a firewall, be aware that the following ports are required:

  • Intersite communications use:

    • SQL Server Service Broker, which defaults to port TCP 4022.

    • SQL Server Service, which defaults to port TCP 1433

  • Intrasite communication between the SQL Server database engine and various Configuration Manager site system roles default to port TCP 1433.

  • https://technet.microsoft.com/en-us/library/hh427328.aspx

    Install Configuration Manager

    Now that all of the preliminary work is in place, now install Configuration Manager.

    When installing Configuration Manager, use the option of installing to an existing SQL Server instance and database. Choose the pre-created database during the installation phase.

    Post ConfigMgr 2012 R2 Installation Tasks

    1. Setup database backup plan – backs up to <drive>:\BACKUP with a 1 week retention

    2. Database Optimization and Tuning

    Configuration Manager SQL Server Backup guidelines

     

    As previously documented, using native SQL Server backup for ConfigMgr recovery has been supported since CM 2012 Sp1 and later.

    SQL backups are the preferred technique for backing up the ConfigMgr for a number of reasons.

    Here is my list of why native SQL Server backups are superior to the built-in ConfigMgr maintenance task:

              Backup databases other than ConfigMgr (ReportServer, ReportServerTempdb and SUSDB)

              Use database compression (the space savings can be huge)

              ConfigMgr services continue to run while backup is in motion

              Can send email notifications on completion (or failure)

              More granular control over scheduling of backup time

    Since ConfigMgr (Current Branch) build 1511 was introduced, in addition to SQL databases, the CD.Latest folder (located in the installation path for Configuration Manager), also needs to be backed up.

    Why do I care about the CD.Latest folder?

    As updates are delivered to ConfigMgr, updates to the ConfigMgr binaries are updated in the CD.Latest folder as well.

    The source files for CD.Latest are used as follows:

    ·         Backup and Recovery – if you need to re-install a site and want it to be the same build number, the files contained will be an exact version match. If you need to recover a site and do not have the contents of CD.Latest folder – you cannot recover the site!

    ·         Child primary site – if you need to re-install a child primary site in a hierarchy you must use the CD.Latest folder that matches the CAS version

    ·         Expand an existing site – if you need to turn your primary site into a multi-site hierarch, you will need to use CD.Latest to install the CAS. (Hopefully you do not have to do this!)

    TIP: If you need to perform any of the above steps, you will need to copy the contents of CD.Latest to a path outside the Configuration Manager installation to get ALL of the available options.

    I’ve assembled the following step by step guide to create a backup task for SQL Server to back up the core items you need for a successful recovery.

    Caveat(s): This guide will not cover other files that you will need for a complete recovery; OSD images, source for packages/applications, drivers, etc. (please see the references section for more information). Always test the restore process in a lab prior to actual use! Keep fingers out of the cage and do not feed the animals. J

    How to backup ConfigMgr using SQL Server Agent tasks

     

    You will start by creating an SQL Server Agent task to back up the cd.latest folder.

    1.      Using SQL Server Management Studio, expand the SQL Server Agent node. Right click Jobs, then New Job…

    image

    Perform the following steps:

    a.      After Name: type Backup CDLatest

    b.      On left, click Steps

    c.       Click New…

    2.      For the Job Step Properties, perform the following steps:

    a.      After Step Name: type Backup

    b.      For Type: select Operating system (CmdExec)

    c.       Copy the following PowerShell command into the clipboard (Note: modify paths as needed to match your environment, should be one line)

    powershell.exe -command "Get-ChildItem -Path 'E:\SQLBCK\CDLatest\*.zip' | Where-Object {$_.CreationTime -lt (Get-Date).AddDays(-7)} | Remove-Item | Add-Type -Assembly 'System.IO.Compression.FileSystem' -PassThru | Select -First 1 | % { [IO.Compression.ZIPFile]::CreateFromDirectory('e:\program files\microsoft configuration manager\cd.latest', 'e:\SQLBCK\CDLatest\CDLatestArchive' + (Get-Date -format 'yyyyMMddHHmm') + '.zip') }"

     

     

    d.      Paste into the Command Window, click OK.

    image

    Now, you will start by creating an SQL Server Agent task to back up the cd.latest folder.

    1.      Using SQL Server Management Studio, expand the Management node > click Maintenance Plans, right click Maintenance Plan Wizard.

     image

    2.      Click Next. At the Select Plan Properties, after Name: type: BackupCM, then click Next.

    image

    3.      At the Select Maintenance Tasks, select the following, then click Next.

    a.      Clean up History

    b.      Execute SQL Server Agent Job

    c.       Back Up Database (Full)

    d.      Maintenance Cleanup Task

     

    image

    4.      At the Select Maintenance Task Order, select the Execute SQL Server Agent Job, click Move Down until this task is last.

    image

    5.      Click Next. At the Define Back Up Database (Full) Task > General tab, Select Databases, then enable All user databases. Click OK.

    image

    6.      At the Define Back Up Database (Full) Task > Destination tab, perform the following:

    a.      Enable Create a backup for every database

    b.      Enable Create a sub-directory for each database

    c.       Type the folder name (or UNC) location for your SQL backups (sample: E:\SQLBCK)

    d.      After Backup file extension: type bak

    e.      Enable All user databases. Click OK.

    image

    7.      At the Define Back Up Database (Full) Task > Options tab, perform the following:

    a.      After Set backup compression: select Compress backup 

    b.      Click Next.

     

    image

    8.      For the Define Maintenance Cleanup Task, perform the following:

    a.      Type the folder name (or UNC) location for your SQL backups (sample: E:\SQLBCK)

    b.      After File Extension – type bak

    c.       Enable Include first-level folders

    d.      In the File age section, change delete files older than to 1 week

    e.      Click Next.

    image

    9.      For the Define Execute SQL Server Agent Job Task, perform the following:

    a.      Locate and select the Backup CDLatest SQL Agent task (created in the first task)

    b.      Click Next

    image

    10.  At the Select Report Options, Click Next.

    11.  At the Complete the Wizard, click Finish.

    image

     

    Schedule this SQL Server maintenance task

     

    1.      Using SQL Server Management Studio, expand the SQL Server Agent node. Right click Jobs, select the BackupCM task, right click Properties.

    image

     

    2.      From Job Properties, choose Schedules, then New…

    image

     

    3.      From New Job Schedule, perform the following:

    a.      After Name – type Backup

    b.      After Frequency – select Daily

    c.       Note change time of backup (if needed)

    d.      Click OK

    e.      Click OK

    image

     

    image

     

    Notes:

    ·        Before running the first time, be sure to manually create a folder in the back up path for CDLatest. In this example, browse to E:\SQLBCK and create a folder: E:\SQLBCK\CDLatest

    ·         To test this agent tast prior to the scheduled backup time, right click newly created SQL Server agent task BackupCM, right click Start Job at Step…

    ·         It is usually a good idea to create a separate maintenance plan to back up your System Databases. Schedule this task to run weekly with a 4 week retention.

     

    References:

    Backup and Recovery in Configuration Manager

    The CD.Latest folder for System Center Configuration Manager

    Configuring backup in ConfigMgr Current Branch

    ConfigMgr 2012 Site Backup and Recovery Overview

    SQL Server Database Migration Checklist

    This guide is meant to provide general recommendations for migrating SQL Server databases from one server to another.

    Always test database migrations (and subsequent application connectivity) in a lab environment prior to the actual migration.

    Note: Server name ‘SOURCE’ is the original SQL Server, ‘TARGET’ is the new SQL Server.

    1.1 Database migration checklist

    The migration should be conducted in three phases:

    1) Pre-migration inventory / checks

    2) Database migration

    3) Post-migration configuration / checks

    1.1.1 Pre-migration phase

    For the pre-migration phase, review and document the following tasks on the SOURCE server.

    1) Database sizes

    2) Data and Log file location

    3) Server and Database properties (Collation, Auto Stats, DB Owner, Recovery Model, Compatibility level, etc.)

    4) Collect the information of dependent applications, make sure application services will be stopped during the database migration

    5) Database logins, users and their permissions

    6) Dependent objects (SQL Agent Jobs and Linked Servers)

    7) Document maintenance plans

    On the TARGET server, conduct the following tasks:

    1) Verify adequate disk space exists

    2) Appropriate folders have been created

    3) SQL Server is correctly installed and configured with latest Service Pack and Cumulative Update

    4) Set SQL Server properties; memory, tempdb size, autogrowth and drive location.

    5) Appropriate connectivity exists with application servers, web servers, etc.

    6) Create databases consistent with planned database migrations

    1.1.2 Database migration phase

    1) From SOURCE server, get recent list of DB and file location list

    2) On SOURCE SQL Server, Script migrate user LOGIN information between servers, save output for subsequent step.

    3) Use native SQL Server to back up databases from SOURCE, use backup compression to reduce size of backup (optionally, use detach database, copy db files, then reattach database)

    4) On SOURCE server, place databases in Read Only mode

    5) Restore databases to TARGET

    6) On TARGET SQL, apply script to migrate users/group security (Created in Step 3)

    7) Update any custom SSRS DSNs to point to TARGET server.

    8) On SOURCE server, script any required SQL Server Agent Jobs (select Job, right click, Script Job as > CREATE to > File ) to create

    9) On TARGET, using the file create, then transfer/execute job creation on TARGET. Create schedules as needed.

    1.1.3 Post-migration phase

    1) Point the application to the new DB server address, or Availability Group LISTENER for AlwaysOn Availability Groups (Connection strings, etc. to altered by the application support team)

    2) Restart / review Network connections between all stake holding servers (Network Team)

    3) Check the SQL Server Error Log and Windows Error logs for any failures

    a. Confirm application functionality with end users

    1.2 Transfer logins

    If you ever have a need to migrate SQL Server databases between servers; particularly a backup/restore scenario, you need to be aware that the database security login ids are not automatically migrated. Here are some notes on this task.

    Once databases are backed up from one instance and restored to another instance, database security needs to be transferred as well.

    This task can be a challenge, fortunately Microsoft has created a SQL script that can be used for this purpose. This can be found here:

    How to transfer logins and passwords between instances of SQL Server

    The process is well documented. The steps are as follows:

    1. From the transfer logins and passwords web page, copy/paste the SQL script to SQL Server Management Studio (SSMS) running on the SOURCE server.

    2. Execute the script – it will create a new stored procedures on the Master database.

    3. From SSMS, run the following query:

    4. EXEC sp_help_revlogin

    5. The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

    6. On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.

    7. Important Before you go to step 5, review the information in the "Remarks" section. (Note: see web page mentioned earlier)

    8. Open a new Query Editor window on TARGET server, and then run the output script that is generated in step 3.

    Reference: http://support.microsoft.com/kb/918992

    Downloading updates fails zero percent complete

    Examined a server recently that refused to download deployed security updates. This server was also a Distribution Point in a remote location. Further, this site is running CM Current Branch.

    Software Center presented the following:

    image

    DataTransfer.log showed something similar to the following:

    Successfully sent location services HTTP failure message.
    
    Error sending DAV request. HTTP code 500, status 'Internal Server Error'
    
    GetDirectoryList_HTTP('http://<server>:80/SMS_DP_SMSPKG$/<guid>') failed with code 0x87d0027e.    DataTransferService  4/29/2016 8:51:36 PM  3028 (0x0BD4)
    
    Error retrieving manifest (0x87d0027e).  

    After review, content boundaries were correct, DP was installed correctly. Enabling HTTP anonymous authentication did not help.

    Found a similar problem on the web, with a tip to enable ASP.NET 4.5:

    Adding the ASP.NET 4.5 Role (and proposed Features) under Roles and Features > "Web Server (IIS)" > "Web Server" > "Application Development" solved our issue.

    For more details see http://go.microsoft.com/fwlink/?LinkID=216771

    Took these steps, rebooted the server and all is well.

    New Power BI dashboard for CM and Intune Hybrid

    This is one of the nicest offerings that I have seen for a while… using PowerBI and access to the CM database.

    Highly recommend!

    Some sample screen shots:

     

    Details on the offering, including the download link:

    Exploring your System Center Configuration Manager and Microsoft Intune (Hybrid) data on Power BI Dashboard

    https://blogs.technet.microsoft.com/configmgrteam/2016/04/01/exploring-your-system-center-configuration-manager-and-microsoft-intune-hybrid-data-on-power-bi-dashboard/

    The SQL Server 2014 Upgrade Advisor

     

    1.1 SQL Server Upgrade Readiness

    One of the best ways to proceed with SQL Server upgrades is by running the Upgrade Advisor. The SQL Server 2014 Upgrade Advisor analyzes the configuration of installed SQL Server components, and then generates a report of issues that you must address to assure a successful upgrade.

    1.1.1 To install the Upgrade Advisor

    Either use the Upgrade Advisor installation file (sqlua.msi) in the redist folder of the installation media or download the *msi as part of the Microsoft SQL Server 2014 Feature Pack.

    The best to way to get the items you need, use this download link:

    Microsoft SQL Server 2014 Feature Pack

    https://www.microsoft.com/en-us/download/details.aspx?id=42295

    Select SQLDom.msi and SQLUA.msi (x64)

    clip_image002

    SQL Server 2014 Feature Pack Download

    Install the downloaded files in the following order:

    1. Install SQLDom.msi

    2. Install SqlUA.msi

    1.1.2 To Run the Upgrade Advisor

    From the Start menu, lauch the SQL Server 2014 Upgrade Advisor.

    clip_image004

    SQL Server 2014 Upgrade Advisor – Home page

    Start the Launch Upgrade Advisor Analysis Wizard. Click Next.

    clip_image006

    SQL Server 2014 Upgrade Advisor Analysis Wizard

    Enter the SQL Server name, select components that you need to evaluate and click Next.

    clip_image008

    SQL 2014 Upgrade Advisor – Select Components

    Select the Instance name, and appropriate Authentication method, click Next.

    clip_image010

    SQL 2014 Upgrade Advisor – Connection parameters

    Select the appropriate databases to analyze, optionally select Analyze trace files and/or SQL batch files, click Next.

    clip_image012

    SQL 2014 Upgrade Advisor – Select Databases

    Review Confirmation settings, then click Run.

    clip_image014

    SQL 2014 Upgrade Advisor – Confirm Selections

    Upgrade wizard result dialog. The Launch Report button will display more detailed results.

    clip_image016

    SQL 2014 Upgrade Advisor –Results

    1.1.3 View Upgrade Advisor Report

    The Upgrade Advisor report can be run from the SQL Server 2014 Upgrade Advisor, shown at the begging of the last section, or from the completion dialog. Review and resolve any issues prior to the upgrade.

    An example of a successful upgrade analysis report is shown next.

    clip_image018

    SQL 2014 Upgrade Advisor – Reporting