SQL Server Express and You – Planning a Smooth Transition to Full SQL

Recently, our Support team received a number of calls relating to an issue with Bp Premier’s SQL Server Express component, which can result in a very large, unexpected expense for Practices. As such, we felt it was important to flag this scenario with our customers.

Experiencing either performance issues, warning messages about low diskspace or a notification from Microsoft about the need to upgrade their SQL Server Express licence, several Practices contacted our Support team to seek an explanation and guidance on how to resolve the issue. Upon further investigation, we found that some Practices have outgrown their free SQL Server Express licence that comes packaged with your Bp Premier installation, requiring an upgrade to Microsoft’s SQL Server Standard Edition or even their Enterprise edition. Depending on the Practice’s size and licensing requirements, this can be anywhere from $4,000 to $14,000.

To find out how you can reduce the risk of being stuck with a large, unexpected and unwelcome bill – read on.

SQL Server Express edition, included with your Bp Premier installation, has a limit of 10GB of storage for a single database. For Practices in this situation, an upgrade to SQL Standard edition will allow for your patient database to exceed the 10GB limit, and allow for the the use of 4 sockets / 24 cores from 1 socket / 4 cores and an increased Buffer Pool Memory from 1410MB to 128GB.  In simple terms, SQL Standard edition will allow you to utilise far more of your server’s processing power.
 

However, upgrading from SQL Server Express is not as simple as flicking a switch, there are considerations you will need to make to ensure you are prepared for a smooth transition.

Given the costs involved with the move to SQL Server Standard Edition (or “Full SQL), a proper succession plan is vital to the smooth operation and transition of your systems.  The key part of this is to know your current limitations and track your Practice’s growth against these to identify your target time frame.

The key metrics to be aware of that effect Bp Premier when used with SQL Express are:

  • 1GB maximum memory used by the SQL Server Database Engine
  • The maximum size of each relational database is 10GB (this refers to the BPSPatients database)
  • The limit on the buffer cache for a single SQL instance is 1MB of RAM
  • The relational database engine is restricted to the lesser of 1 socket or 4 cores

Please note these limitations are governed by Microsoft for this free edition of SQL Server.  As this version suits the needs of a vast majority of sites Best Practice chooses to ship and deploy this for your Practice to assist in the reduction of costs.

The following actions will help you determine if you have a need to upgrade to “Full SQL”:

Monitoring and tracking of performance:

You may want to speak with your IT regarding monitoring your Express instance during your busy periods, to track your performance.

Database size:

By monitoring your back-up sizes for the “BPSPatients” file, you can plot out your Practice’s growth over a period of time and work out an average growth rate, as well as when you would expect to hit your limitations.

When planning for an upgrade to “Full SQL” due to file size growth, you should ensure that you allow enough time before you reach the file size restriction to avoid any downtime for your Practice. As a rule of thumb, you should aim to upgrade with at least 20% of your file size restriction remaining.  It would be recommended to seek advice on costs for “Full SQL” and allow for this in your budget so that funds may be available should you reach this limitation.

Should you need to upgrade your SQL Server version or edition, you can refer to the relevant online knowledge base article titled Upgrade SQL Server. For more information on Microsoft’s SQL Server pricing, visit their product page.

Authored by:

sql server express article scott everist avatar

Scott Everist
Support Leader at Best Practice Software