June 30, 2020 at 3:16 pm
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.
Thanks
June 30, 2020 at 6:10 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 1, 2020 at 7:41 am
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
BEGIN
INSERT INTO ##enterprise_features
SELECT dbname=DB_NAME(),feature_name,feature_id
FROM sys.dm_db_persisted_sku_features
END '
SELECT *
FROM ##enterprise_featuresfor any Enterprise Instance that isn't > 128 GB RAM and is not using Enterprise features I'd question why an EE License was used
July 19, 2020 at 1:00 am
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?
You have said that your company is in this position due to lack of expertise and good management strategies. Have you thought about reaching out to a consultant company like BrentOzar.com or StraightPathSQL to get assistance with getting a handle on your environment? These types of companies can provide hands on assistance and guidance for helping you understand what is in your environment and what should be done with it. In the case of BrentOzar.com, he also has a lot of free scripts bundled into the "FirstResponder Kit" that can help you understand a lot about your environment. He also as something called SQLConstantCare that can assist with learning what is in your environment and figuring out how to manage it. The free scripts and SQLConstantCare provide guidance and links for the things that they find.
One issue you're going to have with such a wide variety of SQL Server in play is that you will need people who have been DBA's for a long time in order to have specific knowledge across that many versions of SQL Server. I like the previous recommendation to upgrade as many of those as you can to get most of your SQL Servers on as few versions as possible. This will help you minimize the differences in management needed between versions.
Beyond that, my very first suggestion is for you to figure out what the backup/restore strategy is for the servers. Can you confirm you have backups and have you tested that you can restore from those backups?
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?
As a previous responder suggested, you could set up a Central Management Server and register all the servers you know about into that Central Management Server.
Purchase a monitoring tool that will cover as many of those installed instances as you can. A good monitoring tool will give you, essentially, a single place to go for reviewing the performance of your SQL Servers.
Another thing you can do is identify any SQLExpress you have in your environment and migrate and consolidate those onto as few servers as you can, decommissioning/uninstalling the SQLExpress as you go. That will reduce the number of SQL Servers you have to manage.
Whether you use Failover Cluster instances versus replication, log Shipping or Availability Groups depends on many factors. What is the RPO and RTO for the data? How long can a server be down? How much data can you stand to lose? The answers to these determine which HA/DR tech you need to implement. The other constraint here is, do you have the people in place with the requisite knowledge about HA/DR in SQL Server to know what the options are and how they work? If you don't have anyone with experience with failover cluster instances or Availability Groups, then that narrows down your options, or tells you that you need to hire someone with the knowledge.
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.
Here is where consolidating SQL instances is going to help you. Also, if these are virtual machines, you might be at a tipping point where it makes sense to license a couple of VMWare hosts and put your SQL Servers on those hosts versus paying for licensing by the machine/VM.
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?
I'm not entirely sure I understand what the question is getting at.
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.
This sounds like data dictionaries. You might look at SentryOne's Doc xPress to help you with object definitions over time and notation software so you can make notes about the meaning of your data.
July 19, 2020 at 12:08 pm
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
BEGIN
INSERT INTO ##enterprise_features
SELECT dbname=DB_NAME(),feature_name,feature_id
FROM sys.dm_db_persisted_sku_features
END '
SELECT *
FROM ##enterprise_featuresfor any Enterprise Instance that isn't > 128 GB RAM and is not using Enterprise features I'd question why an EE License was used
No need to use any enterprise feature to be the best/only option - with virtualization it is almost a requirement for big shops to use it so they can license the vmware cluster and allow for VM's to be moved around without breaking the licensing terms.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply