Category: SQL Server

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!

SCCM 2012 R2 (Configuration Manager) – Setup is unable to connect to SQL Server

Chances are you have a named instance for your SCCM SQL install, which is definitely the way to go. However, when installing SCCM 2012 (R2) you are presented with the following error.

Setup is unable to connect to SQL Server with the connection information provided. Verify the following:

  • The SQL Server and instance names are entered correctly
  • The specified SQL Server instance is not configured to use dynamic ports
  • If a firewall is enabled on the SQL Server, inbound rules exist to allow connections to the correct ports
  • The account used to run Setup has permissions to connect to the specified SQL server instance

1

 

To resolve this is pretty pain-less.

In my scenario, I implemented the following two solutions:

  1. Enable Named Pipes for your SQL Server Network Configuration
  2. Delete all Dynamic (TCP/IP) Ports within the Protocols for your SQL Named Instance

First, to Enable Named Pipes, Launch SQL Server Configuration Manager, expand the SQL Server Network Configuration. Locate your named instance, right-click on TCP/IP and enable.

2

Second, within the same console view, double-click and open the TCP/IP properties.

  • Here you need to delete any 0‘s (Zero’s) assigned to the TCP Dynamic Ports (Yes, remove for all IPv4, IPv6, IPAll, etc.).
  • Also within the IPAll there will be a random port assigned here (TCP Dynamic Ports), go ahead and delete this too.
  • Lastly, now you need to assign some port (ensure this port is open between your SCCM server and SCCM SQL server, if you are making use of the Windows or any Firewall(s)). In my case, I decided to assign port 1433. Within each interface, IPv4, IPv6, etc. apply your port here within the TCP Port. (See below)

2b

 

Once you have implemented the two solutions above, now go ahead and restart the SQL Server (instance name) service.

3

Now proceed with your SCCM 2012 R2 Install.

If you want to learn more on Configuring SQL Server and TCP Port(s), please see the following Microsoft article, HERE.

Cheers!

SCOM Servers not “Remotely Manageable”? – Automation

Few posts ago, I blogged on how you can change your manually installed SCOM agents to actually appear as console-deployed. Although this solution is essentially a one time work-around, the solution below is intended for on-going manual installs. The solution below using the same SQL query and creating an automated SQL tasks that runs on a user-defined interval. Following the steps below, you can set this to run every month (or week, or quarter, etc.) and any manually installed will back their “Change Primary Management Server” enabled again.

In my solution below, I was working with SQL Server 2012SP1. This should work for previous iterations of SQL Server as well, 2012, 2008R2, etc.

Following the steps below, and using the SQL query used in a previous POST, you can automate this as well!

 

image001

image002

image003

 

image004

SCOM Servers not “Remotely Manageable”?

Odds are you probably will have some machines where you can’t deploy the SCOM agent via SCOM console, or PowerShell, or some automated way, and you must install and configure the agent manually. Days/weeks/years go by and now you need to decommission that troublesome, manually installed agents Primary Managed Server. You go to the console and right click, and notice you cannot change the machines primary management server.  Well that is because any manually installed agents SCOM/SQL disables this feature. Well, there is a workaround!

Launch SQL and run the following query against the OperationsManager database to get a list of all manually installed servers:

select bme.DisplayName from MT_HealthService mths
INNER JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mths.BaseManagedEntityId
where IsManuallyInstalled = 1

Now that you have determined which servers were manually installed, to re-enable the remotely manageable feature, run the following SQL query (against the OpsMgr DB).

UPDATE MT_HealthService
SET IsManuallyInstalled=0
WHERE IsManuallyInstalled=1

You should note, this will re-enable this feature for all servers.

Now you should be able to change your machine or any manually installed machines primary management server!

 

Happy SCOM’ing =)

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…)

Step-by-Step – SCOM 2012 R2 Update Rollup 7 (UR7) Install Procedure

My personal notes,  UR7 has a lot of security fixes, and it is highly recommended to upgrade your lab/Dev environments first before upgrading your Production environment(s). The step by step procedures below are the steps I took and in no way shape or form do I accept responsibility for any data loss, and/or issues within your environment. It is advised to always take a backup of your SQL databases and/or snapshots of your SCOM environment(s). Please take these notes as suggestions. Always refer to Microsoft’s KB (posted above) for full documentation steps.

Here are the key updates for UR7 (source Microsoft):

Issues that are fixed in this update rollup can be found here, https://support.microsoft.com/kb/3064919

Once you are ready to begin your upgrade, it is recommend you do the following server/roles in the order below:

  1. Install the update rollup package on the following server infrastructure:
  • Management server or servers
  • Gateway servers
  • Web console server role computers
  • Operations console role computers
  1. Apply SQL scripts.
  2. Manually import the management packs.
  3. Apply the agent update to manually installed agents, or push the installation from the Pending view in the Operations console.

Once you have downloaded the rollup files, I like to extract and only keep the language I need, in this case, ENU (English). You will need to install these with Administrative rights, I like to use PowerShell as Local Administrator. It really does frustrate me, as there is no indication that the rollup installed correctly, (other than looking at the file version number change via File Explorer).

1 (2)

2 (2)

Once the rollups are installed, you will now need to apply the SQL scripts. First update the Data Warehouse, then followed by the OpsMgr DB.

The scripts can be found here, “C:\Program Files\Microsoft System Center 2012 R2\Operations Manager\Server\SQL Script for Update Rollups\”

Please note, the user executing these scripts needs to have read and write permissions to the database(s).

3 (2)

5 (2)

4 (2)

Once you have successfully executed the SQL scripts, you will now need to import the updated Management Packs. These MPs can be found here, “C:\Program Files\Microsoft System Center 2012 R2\Operations Manager\Server\Management Packs for Update Rollups\”.

You will need to import the following MPs, please see below:

6 (1)

Once the MPs have been imported, you should now go back to your Pending Management view, under the Administrations pane, and update all servers.

7 (1)

And that is that! You are now on the latest System Center release for SCOM 2012 R2.

(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'