Strategies for better SQL server Management and Cost Saving


    SSC Rookie

    Points: 32

    Our company has nearly 50 SQL server engines (more than 2/3 are version 2014 or newer) and 250 database instances running, the business applications data in the databases vary greatly in their subjects. Due to lack of expertise and good management strategies, we do not have good knowledge about our SQL server environments, from licensing, data security, disaster recovery plan, basic database management(backup, tuning, account management), applications usage and BI reporting,  to data directory and contents. We are looking for grand strategies aimed at better management and cost saving with the following goals in mind:

    1. Our SQL servers have multiple versions from 2005 version to 2017 versions with different editions,  what are the good strategies to manage such diverse server environments?

    2. We are exploring possibilities to have centralized architectures to management our SQL server databases, would  SQL clustering or SQL farm, or SQL cloud reduce, at least partially, our database management burden?  what are the criteria to decide whether some applications can be based on clustered SQL servers vs dedicated SQL server?  any recommendations?

    3. We are looking for better licensing management strategies given our diverse and complex SQL server environments to reduce overhead on duplicate licensing costs, upgrade costs, and maintenance costs.

    4. What are the considerations we should give to the better SQL server management from applications and reporting perspective? any recommendation to our complex business applications and data usage?

    5. We are looking for efficient and possible 'smart" approaches to categorize, index or build directories of our business data in our SQL server databases, so that we have better understanding our business data for better management purposes.

    We know each subject above covers wide range of topics and details, we are looking for help to start with a roadmap to achieve our goals. Any suggestions and recommendations are greatly appreciated.


  • Mr. Brian Gale


    Points: 22932

    I am in a similar boat and have created my own "wishlist" as I've started calling  it as it is hard to get buy-in from non-technical people.  But I'll try to address each question.

    1 - My first bit of advice is going to be upgrading the SQL instances to the same or similar version as far as the applications will allow.  For us, that is mostly 2016, but it may be different for you.  The instances that cannot be upgraded thankfully are managed outside of  my hands so those ones (a 2000 and a 2005) are both isolated on their own machines tied heavily to the application and are both marked as EOL systems.  So step 1 was to get a list of all instances, their versions and if they can be upgraded.  If they cannot, then I needed to figure out if I actually owned and managed the systems and if not, they are outside the scope of the upgrade plan.  Once they are all at the same version, maintenance becomes a lot more simple and if you decide to consolidate your instances, it is much safer if they are all on the same version.

    2 - For software to manage the SQL Instances, SSMS has a built in "Registered Servers" which allows you to make a central management server for the SQL instances.  That is what I use.  Outside of SSMS, I use DxEnterprise for a failover tool which also (by design) shows me all of the instances and where they are hosted.  DxEnterprise comes at a high price tag, but it is a very good failover tool (similar to HP Polyserve before it went EOL).

    3 - For licensing, I would reach out to your licensing reseller as they will offer you the best advice based on the licenses you currently have. It may be different if you are a fully virtual environment, fully physical, or mixed too.

    4\5 - For points 4 and 5, those are a lot bigger questions and I don't have good answers for that.  It really depends on your system, how big  your team is, what your timeline is, and the applications.  For managing application data and report data, it depends on your systems and needs.  If users are doing analytical reporting, then I'd recommend a data warehouse for that and load data in (ETL) as needed (may be nightly, may be weekly, may be yearly... I don't know your systems).  For non-analytical reporting (ie real-time reporting), you likely will need to pull from the live systems.  One big thing I'd advise against with reporting is don't mix the 2.  If a report NEEDS real-time data (serial numbers in use) and CAN use some analytical data (internal part numbers), do it all from real-time.

    For building the data dictionaries, that is a large undertaking itself.

    If you have the budget for it, there are consultants you can hire in that will offer a lot better advice based on your specific system.  In my case, I have 60-ish SQL instances with about 150 databases total.  Some of the INSTANCES can be consolidated, SOME of the databases can be too.  My approach is going to be to try to reduce the 60 instances down to about 5-10.  Some of the instances will trivial to do this with as they are 3rd party applications and are all running the same SQL version.  Downside is needing to be more fussy about permissions.  I don't want 3rd party tool A having access to 3rd party tool B's data in any way. Things that will be tricky is our internal databases that we created which SHOULD be easier, but due to old, unmaintained, legacy code (VB6) that has little to no business benefit to update, changes to the databases needs to remain transparent to the software.

    That is my advice anyways.  A lot of those are tricky questions as it really depends on your specific situation and what your databases look like and how they are used.  In your case, it may make more sense to install more instances and break the 250 databases out further.

  • DinoRS


    Points: 2676

    Run this against your Instances


    IF OBJECT_ID('tempdb.dbo.##enterprise_features') IS NOT NULL
    DROP TABLE ##enterprise_features

    CREATE TABLE ##enterprise_features
    dbname SYSNAME,
    feature_name VARCHAR(100),
    feature_id INT

    EXEC sp_msforeachdb
    N' USE [?]
    IF (SELECT COUNT(*) FROM sys.dm_db_persisted_sku_features) >0
    INSERT INTO ##enterprise_features
    SELECT dbname=DB_NAME(),feature_name,feature_id
    FROM sys.dm_db_persisted_sku_features
    END '
    SELECT *
    FROM ##enterprise_features

    for any Enterprise Instance that isn't  > 128 GB RAM and is not using Enterprise features I'd question why an EE License was used

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply