Why you should not use remote SQL Server with ConfigMgr 2012

 

As a senior consultant, long time administrator of SMS/ConfigMgr and former SQL Server MVP, I have some strong opinions on this subject. These opinions are based on years of actually designing, implementing and using the product. And, observing when things go wrong.

I’ve read different guidelines for SQL Server installed locally vs. remote. One guideline is the number of clients that are supported in your site. However, with proper server planning, memory, processor and storage configuration, these “limitations” do not exist!

My friend, Kent Agerlund, addresses this topic: System Center 2012 Configuration Manager SQL recommendations

The major advantages/benefits of a local SQL Server install:

  • Less complex
  • Easier to implement
  • Fewer Setup / Connection / Communication issues
  • - Setup fails: example here and here
  • - SQL SPN (Service Principle Names) missing or invalid: example here
  • - Invalid or missing Firewall rules/ports
  • Fewer Authentication issues
  • Higher performance – network connection is not relevant for ConfigMgr 2012 communication with SQL Server.
  • WSUS – if you are using WSUS for patch management, install WSUS using a local copy of SUSDB on the same SQL Server instance. Do not use the Windows Internal database for SUSDB.
  • Reporting Services – unless you have an extremely large site, install Reporting Services on the local SQL Server as well.

Suggestion:

Become your OWN DBA and manage your own ConfigMgr database(s) – many DBA’s overly complicate the setup and security access to the database. I’ve read and heard many, many stories of DBA’s doing their work so effectively, ConfigMgr 2012 no longer works!!

Additionally, for ConfigMgr 2012, I do not recommend installing or configuring SQL Server on a clustered instance. The reasons for this recommendation compliment the ones above; in that remote SQL Server overly complicate an already complex product and High Availability is not really needed for ConfigMgr 2012. Updates are far more complex in this environment as well.

If you really need higher availability, consider using remote Management Points with Management Point SQL Server replicas!

One final note, when you install SQL Server locally for ConfigMgr 2012, be sure to properly configure the amount of memory SQL Server uses.

As always, comments and questions welcome!

Posted in ConfigMgr, SQL, SQL Server, SQL Server 2012 | Leave a comment

Microsoft Senior Systems Engineer Needed – Dallas, TX

Microsoft Senior Systems Engineer Needed

Softchoice – Dallas, TX

Anyone with System Center skills in the Dallas, TX area looking for work?

Softchoice has a great opportunity: https://www.linkedin.com/jobs2/view/13441840

Contact me for more information:

steve (dot) thompson (at) softchoice (dot) com

Posted in Softchoice, Uncategorized | Leave a comment

Why default database Autogrowth settings are bad

File AutogrowthNever, never use the default database Autogrowth settings. As in the next screen shot, the tempdb default for the primary file is 1MB and the Log is 10%. Allowing these default settings will lead to excessive file fragmentation and impact performance. Better settings for Autogrowth are a minimum growth size of 512MB or 1024MB (data and log settings).

Default File Size and Autogrowth setting for tempdb

clip_image001

Size the database data and log portions to the projected, estimated size.  Sizing your files in advance, will reduce the fragmentation caused by autogrowth. This advice applies to databases other than tempdb as well.

Further, allowing default autogrowth settings for transaction log files will cause VLFs (next).

VLF (Virtual Log Files) – can have a negative effect on database performance. Too many VLFs will slow down the startup, shutdown and read/write performance of any database.

VLFs are created when a transaction log file is allowed to auto-grow. While auto-growth will likely be enabled per database, the transaction log file should already be sized appropriately so it will not need to auto grow.

A general rule of thumb, there should be fewer than 100 VLFs.

For more information on VLFs, reference: http://stevethompsonmvp.wordpress.com/2013/05/14/virtual-log-files-and-impact-on-configmgr-performance/

Posted in ConfigMgr, SQL, SQL Server, SQL Server 2012 | 2 Comments

T-SQL Tuesday giving back to the community

 

Read an interesting post here:

http://www.sqlservercentral.com/blogs/discussionofsqlserver/2014/12/02/t-sql-tuesday-61-giving-back/

and thought, why not? :)

TSQL Tuesday

 

Allow me to reflect a little; in 2014, I’ve had the opportunity to present SQL Server related content at:

  • TechEd 2014 (Houston)
  • IT/Dev Connections 2014 (Las Vegas)
  • MMS Minnesota 2014 (Minneapolis) http://mms.mnscug.org/

Have created a number of blog entries focused on SQL Server and its interaction with System Center ConfigMgr 2012.

In October 2014, was fortunate enough to he re-awarded MVP status for the 20th consecutive year. By the way, found out there are only 11 remaining MVPs that have had this award 20 or more years.

Started a Twitter account @Steve_TSQL to be more active with social media.

Finally, I have a series of planned blog articles for 2015 to assist ConfigMgr administrators get the most out of SQL Server.

I’m looking for ideas to develop these articles, any gaps, or more information you’d like to see on a particular topic, let me know?

Posted in ConfigMgr, MVP, SQL Server, System Center 2012 R2 | Leave a comment

What is PSR?

Not sure if I’m the only one that had not heard of this tool.

PSR is the Problem Steps Recorder, included with Windows 7 & Windows 8.  This will be really useful for recording interactive sessions, particularly for documentation

From a CMD window, or from Start Run enter:

PSR

image

On the right hand side, click the dropdown, then settings. Change the Number of screen captures to something larger than the default of 25. Optionally, input an Outout location.

image

Once you save the settings, click record, all mouse clicks will save screen shots. Once you stop recording, the screen shots are saved as a *.mht file.

Posted in Uncategorized | 2 Comments

How to monitor Windows Server memory usage

In the last blog entry:

Configuring SQL Server memory for ConfigMgr 2012

I’ve covered how to configure SQL Server to not monopolize Server memory. Thought it would be useful to further define how to monitor the memory usage and provide a real life example.

For monitoring, here are a few options:

- System Operations Manager 2012

- Performance Monitor – either dynamic, or log activity

- Task manager

For a quick check on how well a server is managing its memory stack, I’ll usually just open Task Manager and select the Performance tab.

In the following real world example; this dedicated SQL Server had a total of 98GB of memory installed, and SQL Server had been allowed to use 96GB of memory. If you check the Physical Memory (MB) section, you can see that this server is only showing 33 MB! of Free memory. Finally, check the highlighted “Physical Memory” (bottom), showing Physical Memory: 99%. Page file is being used in this scenario.

image

Recommendation was to add additional memory, then adjust the SQL Server max memory setting.

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

Configuring SQL Server memory for ConfigMgr 2012

A common SQL Server configuration error is allowing SQL Server to use all available memory.

Te default “Maximum server memory (in MB)” setting (highlighted) allows SQL Server use ALL available memory. For the most part, SQL does a good job of managing the available memory, however, best practice is setting a maximum available memory.

image

For a Windows Server dedicated for use by SQL Server, general rule of thumb is to allocate approximately 80-90% of available server memory to SQL Server. Monitor the Windows Server memory usage.

If you are running ConfigMgr 2012 on the same server with SQL Server; I’d recommend allowing about 8GB of memory for ConfigMgr and the Windows Server OS, then allocate the remainder of memory to SQL Server by using the Maximum Server memory setting shown earlier. If possible, allocate in even 1024MB chunks.

For example:

Windows Server has 32GB total memory, and is running ConfigMgr 2012 and SQL Server. Subtract 8GB memory for the Server OS and ConfigMgr 2012. The remainder should be used for SQL.

32GB – 8GB = 26GB (SQL Server)

26GB * 1024 = 28672 (MB – enter in SQL property sheet)

image

Ideally, the server should not be using the server page file, as this will lead to performance degradation. Monitor Windows Server memory usage and make adjustments if needed.

Posted in ConfigMgr, SQL Server, SQL Server 2012 | 1 Comment