Tag: SQL

Installing SQL 2016 for System Center Operations Manager (SCOM) 2016 – Step-by-Step

The following is a guide on how to install SQL 2016 for your System Center Operations Manager (SCOM) 2016 environment. I will be installing SQL 2016 on a brand-new server with Windows Server 2016 installed.


To begin, I am going to set the following accounts as a Local Administrator on the server. Also, I am going to be creating two SQL instances, one for the Operations database, and the other for the Data Warehouse. Since this is for my personal lab, I am not dedicated storage/drives for the databases.

 

Domain\Account Description
domain\SCOM_AA SCOM Action Account
domain\SCOM_DA SCOM Data Access/SDK Account
domain\SCOM_SQL_READ SCOM SQL Reader
domain\SCOM_SQL_WRITE SCOM SQL Writer
domain\SQL_SA SQL Service Account

1

Next, let’s run the setup wizard as the SQL_SA account to make life easier down the road…

2

First thing I noticed, between SQL 2012/2014 and SQL 2016, a few changes/features have been removed/added. One that stands out is, the SQL Server Management Studio (SSMS) console is no longer here. Hmm.. I guess we can always connect to the databases from a console on another server/PC.

3

As mentioned, I am dedicated an instance for the Operations DB, and one for the Date Warehouse DB.

4

Setting the  SQL Server Agent to Automatic, and specifying the service accounts for the two services.

5

Keeping the database engine collation as default, “SQL_Latin1_General_CP1_CI_AS“.

6

Here, I am adding all the SCOM/SQL service accounts and SQL service accounts as SQL server administrators.

7

Nice! This is new for SQL 2016 — being able to create TempDB‘s. Since my VM has 8 vCPU’s, looks like SQL 2016 picked up on that, and has decided to create a one-to-one relationship. Great, let’s get started within the installation…

8

Perfect! No errors. Keep in mind, we will need to repeat these steps to create the Data Warehouse instance.

15

16

Great! Now we can go ahead with the SCOM 2016 installation! See HERE, for that post.

If you need to install the SQL Server Management Studio (SSMS), continue reading…

(more…)

Advertisements

Step-by-Step – Upgrading to SCOM 2016 from SCOM 2012 R2

So you’ve decided to take the jump and upgrade to System Center Operations Manager 2016? Assuming you are running a SCOM 2012R2 (UR11) environment, this post will be exactly what you need to have a successful SCOM 2012 R2 to SCOM 2016 upgrade. **Although the recommended path is to upgrade from UR9**

In my environment, here is the quick breakdown:

  • SCOM 2012 R2 environment has two Management Servers.
    • Handful of clients (Windows 2012R2) being monitored.
  • Both Management Servers live on a Windows 2012 R2 operating system.
    • Both Management Servers are running with UR 11 (Update Rollup) **Microsoft recommends upgrading the environment at latest version number minus one (latest – 1), so in this case, UR9 (UR10 was never issued for SCOM 2012R2)**.
  • The SQL environment is a SQL Server 2014 SP2 also running on Windows 2012 R2.
    • The Operations and Data Warehouse live on dedicated SQL instances, however reside on the same server.

Pre-Upgrade Tasks

Let’s get started!

First, we need to do some “Pre-Upgrade” Tasks. Follow these in order.

  1. Back up the Operations Manager Databases
  2. Review the Operations Manager Event Logs
  3. Cleanup the Database (ETL Table)
  4. Remove Agents from Pending Management
  5. Disable the Notification Subscriptions
  6. Stop the Services or Disable any Connectors
  7. Verify that the Operational Database Has More Than 50 Percent Free Space
  8. Back up the Operations Manager Databases

To Cleanup the ETL Tables, you will need to run the following script:

  • The following script will determine the number of rows that will (need) be deleted:

DECLARE @SubscriptionWatermark bigint = 0;

SELECT @SubscriptionWatermark = dbo.fn_GetEntityChangeLogGroomingWatermark();

Select COUNT (*)
FROM EntityTransactionLog ETL with(nolock)
WHERE NOT EXISTS (SELECT 1 FROM EntityChangeLog ECL with(nolock) WHERE ECL.EntityTransactionLogId = ETL.EntityTransactionLogId)
AND NOT EXISTS (SELECT 1 FROM RelatedEntityChangeLog RECL with(nolock) WHERE RECL.EntityTransactionLogId = ETL.EntityTransactionLogId)
AND EntityTransactionLogId < @SubscriptionWatermark;

etl-cleanup-1

  • Now we can go ahead and clean up the ETL table running the script below:

DECLARE @RowCount int = 1;
DECLARE @BatchSize int = 100000;
DECLARE @SubscriptionWatermark bigint = 0;
DECLARE @LastErr int;

SELECT @SubscriptionWatermark = dbo.fn_GetEntityChangeLogGroomingWatermark();
WHILE(@RowCount > 0)
BEGIN
DELETE TOP (@BatchSize) ETL
FROM EntityTransactionLog ETL
WHERE NOT EXISTS (SELECT 1 FROM EntityChangeLog ECL WHERE ECL.EntityTransactionLogId = ETL.EntityTransactionLogId)
AND NOT EXISTS (SELECT 1 FROM RelatedEntityChangeLog RECL WHERE RECL.EntityTransactionLogId = ETL.EntityTransactionLogId)
AND ETL.EntityTransactionLogId < @SubscriptionWatermark;

SELECT @LastErr = @@ERROR, @RowCount = @@ROWCOUNT;

END

etl-cleanup-2

Great! Let this run, which may take a few minutes, or hours depending on your environment..

Now we are ready to get started with the upgrade. (Don’t get to backup your databases (Report Server, Operations, Data Warehouse!!)

1


Upgrading to SCOM 2016

After you have downloaded the RTM image file, as you may have noticed, it is not an ISO file. So let’s extract the file contents locally…

2

Before moving on with the install, I stopped the following SCOM services on all of the Management Servers:

  1. Microsoft Monitoring Agent (healthservice)
  2. System Center Data Access Service (OMSDK)
  3. System Center Management Configuration (cshost)

Now we can run the installer (Run As Administrator or, SCOM Data Access/SDK account)

3

4

5

6

7

Sweet! Although this was expected, since no new changes were required for Windows Server 2012 R2.

8

I cannot stress how useful it is to use dedicated service accounts. Here input your Data Access/SDK account.

9

Once quick review before we begin the Upgrade..

10

Let this run… For me, the upgrade took around 50 minutes for the first Management Server..

11

Sweet! All good. Remember to install the license key before the 120 days are up.

Let’s launch the console just to make sure we are in all working order.

12

Great! Now we will need to repeat the process for the second/other Management servers…

Once complete, let’s upgrade our client agents to SCOM 2016 (v 8.0.10918.0)

13

At this time we can make use of the SCOM 2016 features, and update our out-dated Management Packs. Remember this feature really only works for Microsoft based Management Packs, ie. SQL, Windows Server, Client OS, etc.

14

 

I hope this helped! For additional information, and or upgrading other items such as Gateways, ACS (Audit Collection Services), etc. Please visit Microsoft’s guides HERE.

 

Lastly, it is highly recommended to upgrade to SCOM 2016 Update Rollup 1 (UR1). For that guide, please visit this LINK.

 

Happy SCOM’ing 2016!

(more…)

Step-by-Step – Installing System Center Operations Manager (SCOM) 2016 on Windows Server 2016 with SQL 2016

This post I will be installing System Center Operations Manager 2016 (SCOM) RTM, Build Number 7.2.11719.0.

Here is some of the background information. As this post will concentrate on the installation of SCOM 2016, I am going to omit the setup and configuration of the Domain Controller, Windows Server 2016 for both SCOM Management Server and SQL Server (Please note, I am using SQL Server 2016, both servers on Windows 2016).

If you need help setting up SQL 2016 for SCOM 2016, please visit HERE.

Environment:  Virtual; ESX 6.0 Hypervisor

SCOM Management Server:

  • Windows Server 2016
  • 4 vCPU (2.00GHz)
  • 12 GB memory
  • 100GB Diskspace
  • 1GB vNIC

SQL Server:

  • Windows Server 2016
  • SQL Server 2016
  • 4 vCPU (2.00GHz)
  • 24 GB memory
  • 300GB Diskspace
  • 1GB vNIC

Service Accounts and Local Administrator:

Domain\Account Description Local Admin on…
domain\SCOM_AA SCOM Action Account SCOM & SQL
domain\SCOM_DA SCOM Data Access/SDK Account SCOM & SQL
domain\SCOM_SQL_READ SCOM SQL Reader SQL
domain\SCOM_SQL_WRITE SCOM SQL Writer SQL
domain\SCOM_Admins SCOM Administrators Group SCOM
domain\SQL_SA SQL Service Account SQL
domain\SQL_SSRS SQL Service Reporting Services Account SCOM

 

Now, if you’re lazy like me, or are tired of doing this setup for environments, I have scripted the automation of these accounts. You can find that link here, Microsoft TechNet Gallery.


Let’s Begin:

2

3

For completeness, let’s install all the features of SCOM 2016. (I am hosting a default SQL 2016 instance on the SCOM Management Server for SSRS)

4

5

Well, that’s not new… Errors. Since this is a clean, vanilla Windows 2016 server, we will need to install all the necessary Web Console components, along with Report Viewer Controls (probably SQL CLR Types too..).

  • For the Report Viewer Prerequisites, go HERE.

Note, oddly I was unable to install with CLR SQL 2016, Reports Viewer still complained and required CLR SQL 2014.

  • Here is the PowerShell command I ran to install the necessary IIS features/roles:
Import-Module ServerManager
Add-WindowsFeature Web-Server, Web-WebServer, Web-Common-Http, Web-Default-Doc, Web-Dir-Browsing, Web-Http-Errors, Web-Static-Content, Web-Health, Web-Http-Logging, Web-Log-Libraries, Web-Request-Monitor, Web-Performance, Web-Stat-Compression, Web-Security, Web-Filtering, Web-Windows-Auth, Web-App-Dev, Web-Net-Ext45, Web-Asp-Net45, Web-ISAPI-Ext, Web-ISAPI-Filter, Web-Mgmt-Tools, Web-Mgmt-Console, Web-Mgmt-Compat, Web-Metabase, NET-Framework-45-Features, NET-Framework-45-Core, NET-Framework-45-ASPNET, NET-WCF-Services45, NET-WCF-HTTP-Activation45, NET-WCF-TCP-PortSharing45, WAS, WAS-Process-Model, WAS-Config-APIs -restart

 

Once the server is back online, you will need to register ASP.Net.

6

You will need to apply the following using Command Prompt (as Administrator)).

  1. cd %WINDIR%\Microsoft.NET\Framework64\v4.0.30319\
  2. aspnet_regiis.exe -r
  3. IISRESET
  4. Reboot your server…

Once the server is back online, let’s try that Prerequisites check again….

7

Great! Now all of Prerequisites have been met!

8

Provide a meaningful Management Group Name (there’s no going back after this…)

9

SQL Server will be where your SCOM SQL instance(s) were installed. For me, I have built two instances on my SQL2016 server (SCOM_OPSMGR & SCOM_DW).

10

11

12

13

14

15

16

17

I recommend always keeping this off, and manually updating your SCOM infrastructure.

18

One quick review. Looks good. Hit Install, and get some fresh air!

19

A few minutes later….

20

Sweet! All good. I hope this helps. If you have any questions or issues, please drop me a line.

Please note, it is STRONGLY ADVISED to install the Update Rollup 1 once you have deployed SCOM 2016. For that walk-through, please visit the following post, HERE.

Happy 2016 SCOM’ing!

(more…)

Configuring Memory Consumption for SQL Server 2012 R2

Naturally (by default) SQL will consume all the memory available within its environment. There are times some applications require a minimum of memory allocated to the SQL instance before you can proceed with the application install. Below is how you can configure the minimum and maximum memory allocation your SQL instance can consume.

For starters, I am going to assume you understand how much memory (RAM) is available to your SQL server, and you have done the math behind any other SQL instances that may be within your SQL server…

In my case, I am going to ensure my application will always have a minimum of 12GB of memory, and a maximum of 14GB. The server itself has 16GB, and I will leave 2GB for the OS.

Launch your SQL Server Manager > right-click on the named instance > select Properties > select the Memory tab > define your settings here.

1

2

 

That is it!

OMS SQL Assessment Solution

First things, first, what is the SQL Assessment Solution? OMS SQL Assessment Solution does exactly what it sounds like, it assesses a given SQL environment, providing a health check and risk assessment. The solution executes on a fixed (for now) interval monitoring and evaluates your SQL environment.

With the solution, it provides six focus areas, where it allows you and your SQL team(s) to understand where your environment may need attention either soon, or immediately. The focus areas provide recommendations based on Microsoft’s KB and Microsoft’s engineers across multiple environments, industries and scenarios. These recommendations are suggested in order to get your environment back in good standing.

Six Focus Areas:

  1. Security and Compliance
  2. Availability and Business Continuity
  3. Performance and Scalability
  4. Upgrade, Migration and Deployment
  5. Operations and Monitoring
  6. Change and Configuration Management

Each focus area will break down its recommendations based on a weighted system. The weighted system is based on three metrics: Impact, Probability and Effort.

Each metric can be broken down as follows:

  • The Impact of the issue on your organization if it does cause a problem. The higher the impact equates to a larger overall score for the recommendation.
  • The Probability that an issue identified will cause problems within the environment. The higher the probability equates to a larger overall score for the recommendation.
  • The Effort required to implement the suggested recommendation. A higher effort equates to a smaller overall score for the recommendation.

For example, if the “Schedule full database backups at least weekly,” is weighted with 4.0, this means after implementing the recommendations and satisfying the assessment, this will improve our SQL assessment score from 88% to 92%, an overall increase of 4%.

temp

Implementing the OMS Solution

To get the SQL Assessment Solution implemented, you will obviously need a SQL environment to monitor, and its Microsoft Monitoring Agent (MMA) either configured to OMS, or the agent/server a member of the OMS server group with SCOM.

Here are the steps you will need to follow to configure the SQL Run As account in the SCOM console:

Note, the Run As account you will be using, needs to be a member of the Local Administrators group on all of the Windows Servers hosting the SQL Server Instances.

  1. In SCOM, go to the Administrations tab
  2. Under the Run As Configuration, click Accounts
  3. Create the Run As Account, following through the Wizard, creating a Windows account
    1. Under Distribution Security, select More secure
  4. Go back to the Run As Configuration and click Profiles
  5. Search for the SQL Assessment Profile
  6. Assuming you are using SCOM 2012 R2 UR7, the profile name should be, “Micorsoft System Center Advisor SQL Assessment Run As Profile
  7. Right click and update its properties, and add the recently created Run As Account we just created in step 3
  8. Now you need to add the Run As account to the SQL database, and grant it the permissions it will need. Use the SQL code below, this will need to be executed on all SQL instances you are interested in incorporating into the SQL Assessment Solution. (I used this from the OMS documentation site; link can be found at the bottom of this blog)

---
    -- Replace "DOMAIN\UserName" with the actual user name being used as Run As Account (removing the quotes).
    USE master

    -- Create login for the user, comment this line if login is already created.
    CREATE LOGIN ["DOMAIN\UserName"] FROM WINDOWS

    -- Grant permissions to user.
    GRANT VIEW SERVER STATE TO ["DOMAIN\UserName"]
    GRANT VIEW ANY DEFINITION TO ["DOMAIN\UserName"]
    GRANT VIEW ANY DATABASE TO ["DOMAIN\UserName"]

    -- Add database user for all the databases on SQL Server Instance, this is required for connecting to individual databases.
    -- NOTE: This command must be run anytime new databases are added to SQL Server instances.
    EXEC sp_msforeachdb N'USE [?]; CREATE USER ["DOMAIN\UserName"] FOR LOGIN ["DOMAIN\UserName"];'

Once you have implemented the steps above, and assuming everything went successfully, soon, with OMS, you will see your SQL environment under the SQL Assessment Solution.

Hopefully there isn’t too much to fix. =)

SQL Assessment OMS

(more…)

Maintenance Mode History with SQL

Unfortunately SCOM 2012R2 does not have a native report and/or view that allows you quickly view the maintenance history on a specific server or servers. This handy SQL query I have used many times over to get the history of a given server or servers to find out when the machine entered MM (Maintenance Mode). Using the query below, run against the OperationsManager (or Data Warehouse) DB, and specify the server(s) you are interested with the date range:

---
USE OperationsManagerDW
SELECT ManagedEntity.DisplayName, MaintenanceModeHistory.*
FROM ManagedEntity WITH (NOLOCK)
INNER JOIN
MaintenanceMode ON ManagedEntity.ManagedEntityRowId = MaintenanceMode.ManagedEntityRowId
INNER JOIN
MaintenanceModeHistory ON MaintenanceMode.MaintenanceModeRowId = MaintenanceModeHistory.MaintenanceModeRowId

WHERE DisplayName Like 'server%.domain.net' AND ScheduledEndDateTime BETWEEN 'fromDateRange' AND 'toDateRange'