Within the incredibly talented ranks of Miles Technologies exists an elite team known as “A.S.E.” (“Ace”) The individuals that make up this group posses an expert level of knowledge in multiple areas within Information Technology. They are the superheroes of IT, using their powers to increase the reliability, scalability, and efficiency of every IT system they touch. These are the IT professionals for other IT professionals.
Here, they share some of their extensive knowledge with you. This is: An ASE Up Your Sleeve
Small Business Server Performance and the Hidden SQL Problem
What SBS is and why does it have some issues?
A Small Business Server, or SBS, was a former go-to, affordable server solution for small to mid-sized businesses. Most people are probably familiar with SBS’ successor, Windows Server Essentials. While some businesses still utilize SBS, it can have its fair share of issues and not everyone knows why or how to fix them. I’ve run into several small businesses using SBS that “used to run fine,” but are now slow, ineffective, and all but unusable. These businesses claim no noticeable changes happened that would have affected their SBS. So where are these problems coming from?
A little background information can help explain why a legacy SBS might be over-encumbered. Small Business Server is a now discontinued product developed by Microsoft that acted as a customized version of server 2000, 2003, 2008, and 2008 R2(SBS 2011). SBS came bundled with Exchange, SQL, Sharepoint, and Active Directory. The streamlined administration and management console had a hard user limit of 50, and restriction settings that prevented users from joining other Active Directory servers to the domain.
The major advantages of the SBS product were price and a substantial feature set. Its cost was much less expensive than full versions of Windows Server + Microsoft Exchange + Microsoft SQL + Sharepoint. That’s enough of a history lesson for now, though! Let’s move on to one of the main reasons you might find your SBS server running slow.
Why your SBS is out of memory, and how to fix it:
The Native SQL server that comes with Small Business Server is configured with a Windows internal database, as well as databases for performance counters and Sharepoint. Over time these databases can become very large, eating up space and wrecking the performance of your server as its memory consumption balloons out of control.
By default, no upper limit of memory usage is placed on this database and, left unchecked, it can cause your server to grind to a halt. Uninstalling this feature is not for the faint of heart, and it will leave you with an event log full of errors that will block out any legitimate issues. Even though you can remove it, it’s not recommended to do so. Your best option is configuring the SQL instance to have a memory limit of a gig or two, depending on your hardware specs.
First, you’ll need to be logged in as the Domain Administrator. Install if needed, and open SQL Server Management Studio.
There are 3 databases you’ll need to connect to. The first is \SBSMONITORING. Enter that into the server name field in SQL Server Management Studio, and leave Windows Authentication selected.
Once you’re in SQL Server Management Studio, Select File -> Connect Object Explorer, and the same window will pop up, this time type in \SHAREPOINT. Click Connect.
Repeat one more time, and type in \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query or np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query depending on your version of SQL. Versions 2005 and older require the np:
This will give you 3 nodes on the left showing the databases.
Repeat the following procedure for all 3 databases.
Right click on the database, and select properties.
On the page that comes up, select Memory, and set the maximum server memory to a reasonable amount for the server you’re working on. If the server has 8 gigs of ram, 1 gig would be a good place to start (1024MB). If you’ve got 16 or more, then 2 would work (2048MB).
Once this has been done for all 3 databases, exit SQL Server Management Studio, and then restart the following services:
- SQL Server (SBS Monitoring)
- SQL Server (Sharepoint)
- Windows Internal Database(MICROSOFT##SSEE)
The names of these services may vary slightly for different versions of SBS, but should generally be easily identifiable.
Once complete, you should see substantially reduced RAM utilization and a much more performant server.
Additional Information for Managing SBS
Though this article applies specifically to Small Business Server 2011 and older, the technique described here for limiting memory usage is the same for any Microsoft SQL Database. In some situations, it can be used to improve the performance of normal SQL servers if it appears databases are misbehaving. SQL will grab as much RAM as it possibly can when unconfigured, and this can lead to undesirable results.
Need help getting your server running smoothly for your business? We’re here to help you do more.