August 30, 2018 at 2:22 am
Hi, not 2016 specific but cant see where else to ask it and we have a lot of 2016, and this is one of the busier forums.
We have a large SQL estate and need to keep track of it.
Was wondering what tools people use to keep track of their SQL Estates, or is it typically Spreadsheets ?
The problem with spreadsheets is that as the team and estate gets bigger they dont work in terms of concurrency and the information can easily become stale if people do not update it.
In the past I have had a bespoke app developed, which worked fairly well but management were not keen on it as it was non standard, we migrated to using "Lansweeper" for the SQL data, a very good tool that collects server info and has a decent awareness of SQL, i.e. it gathered instance and database information on a scheduled basis so it didnt generally get out of date. It allowed data entry via a set of user definable fields which we used to record info about the applications, owners, licenses and also to import VMWare host/guest info into the fields, only issue was that though it recorded database details, you could not record information at this level.
No corporate CMDB I have seen goes to the holy grail level of allowing linking of data with applications and their owners, data entry to record a narrative about the db, server or instance whilst polling the instances and keeping the information about databases and config current.
In my current job, we are still using a spreadsheet but am looking for suggestions as I need to define the strategy going forward of how we manage the estate, I know what I want and need to do but need the right tools in place, a focal point for the team where we can record details and thoughts, add a plan and review dates.
August 30, 2018 at 5:15 am
Could something like DBAReports be of use for you?
September 3, 2018 at 3:08 am
Most people I know who have large estates purchase a third party tool to help with the management. I know that when I was managing a large SQL Server estate, I used a 3rd party tool.
Now, I work for a software vendor that makes that kind of software. It's called SQL Monitor. It has some specific tooling and reports around estate management. You might want to check it out.
There are other 3rd party tools that do this as well. You really should find and use one that works well for you and your business. You should not be managing large numbers of SQL Server manually. Automation is the key to getting this work done. So, even if you don't use a 3rd party tool, you need to automate everything you do yourself.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 3, 2018 at 4:24 am
Grant Fritchey - Monday, September 3, 2018 3:08 AMNow, I work for a software vendor that makes that kind of software. It's called SQL Monitor. It has some specific tooling and reports around estate management. You might want to check it out.
I've worked at a place that used Idera's monitoring tool and where I work now we are using Redgate's Monitor software. Both work in a similair manner - I prefer the Redgate tool because of the other range of useful tools that you can get from them.
Redgate has an interactive demo against their own systems here.This is worth a look, to get an idea of how it manages a multiple instances.
September 3, 2018 at 7:56 pm
My personal feeling is that if you actually need a 3rd party tool to discover server instances, applications, and licenses, then you have too many unqualified cooks in the kitchen building things on a whim instead of a plan.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2018 at 1:54 am
Jeff Moden - Monday, September 3, 2018 7:56 PMMy personal feeling is that if you actually need a 3rd party tool to discover server instances, applications, and licenses, then you have too many unqualified cooks in the kitchen building things on a whim instead of a plan.
Or, you have inherited a large estate currently managed in a spreadsheet which it has outgrown and want to provide something better that allows multiple DBA's to access the same set of information ?
One DBA left last year and the remaining one is leaving at the end of the year, I joined four months ago and two more DBA's are being recruited.
Spreadsheets dont auto update, it is manual and with 160 instances quite onerous, we used Lansweeper at the last place which is not a SQL tool but it does a daily scan on each SQL Server and is "SQL Aware" so captures the config info and database list and allows custom fields and us to augment the collected data with our insights. This allowed us to build automation to detect new instances, databases, changes, incorrect editions etc and manage the estate, and estate that had grown through acquisition.
All very well when you have nice, neat little walled garden, but when you are in SQL chaos, through no fault of your own, like when your company acquires another company as big as yours but not as joined up, in several countries, with at least as much SQL again (450 instances total), but not managed that you then have to assimilate with limited resources, you need tools you can rely on.
Will have a look at the Redgate tool, funnily enough was listening to old Brent Ozar podcasts on the way to work today and someone asked the same question and he mentioned Idera had just released this tool.
I did speak to Idera about the lack of such a tool at SQLBits in Nottingham in 2013, they took it on board and contacted me and asked for a meeting which we duly had, I showed them the in house tool we had developed in .net, then they went away and didnt hear anything further until having been made redundant and got a new job I was on the lookout for a new tool, a Idera now have something very similar to what we talked about, might be coincidence but it does seem like what we discussed.
September 4, 2018 at 5:56 am
mark 52160 - Tuesday, September 4, 2018 1:54 AMJeff Moden - Monday, September 3, 2018 7:56 PMMy personal feeling is that if you actually need a 3rd party tool to discover server instances, applications, and licenses, then you have too many unqualified cooks in the kitchen building things on a whim instead of a plan.Or, you have inherited a large estate currently managed in a spreadsheet which it has outgrown and want to provide something better that allows multiple DBA's to access the same set of information ?
One DBA left last year and the remaining one is leaving at the end of the year, I joined four months ago and two more DBA's are being recruited.
Spreadsheets dont auto update, it is manual and with 160 instances quite onerous, we used Lansweeper at the last place which is not a SQL tool but it does a daily scan on each SQL Server and is "SQL Aware" so captures the config info and database list and allows custom fields and us to augment the collected data with our insights. This allowed us to build automation to detect new instances, databases, changes, incorrect editions etc and manage the estate, and estate that had grown through acquisition.
All very well when you have nice, neat little walled garden, but when you are in SQL chaos, through no fault of your own, like when your company acquires another company as big as yours but not as joined up, in several countries, with at least as much SQL again (450 instances total), but not managed that you then have to assimilate with limited resources, you need tools you can rely on.
Will have a look at the Redgate tool, funnily enough was listening to old Brent Ozar podcasts on the way to work today and someone asked the same question and he mentioned Idera had just released this tool.
I did speak to Idera about the lack of such a tool at SQLBits in Nottingham in 2013, they took it on board and contacted me and asked for a meeting which we duly had, I showed them the in house tool we had developed in .net, then they went away and didnt hear anything further until having been made redundant and got a new job I was on the lookout for a new tool, a Idera now have something very similar to what we talked about, might be coincidence but it does seem like what we discussed.
To be honest, if you've done it correctly, no such auto search should actually work. Imagine someone nasty getting into your box and being able to auto-discover the keys to the city by using a similar tool. I understand that it IS a tradeoff, especially for a new DBA. In such a case, I'd use such a tool once to verify the spreadsheet and then lock the domain down to prevent the use of such tools or at least limit the damage that someone nasty using such a tool could pull off.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2018 at 6:53 am
Jeff Moden - Tuesday, September 4, 2018 5:56 AMmark 52160 - Tuesday, September 4, 2018 1:54 AMJeff Moden - Monday, September 3, 2018 7:56 PMMy personal feeling is that if you actually need a 3rd party tool to discover server instances, applications, and licenses, then you have too many unqualified cooks in the kitchen building things on a whim instead of a plan.Or, you have inherited a large estate currently managed in a spreadsheet which it has outgrown and want to provide something better that allows multiple DBA's to access the same set of information ?
One DBA left last year and the remaining one is leaving at the end of the year, I joined four months ago and two more DBA's are being recruited.
Spreadsheets dont auto update, it is manual and with 160 instances quite onerous, we used Lansweeper at the last place which is not a SQL tool but it does a daily scan on each SQL Server and is "SQL Aware" so captures the config info and database list and allows custom fields and us to augment the collected data with our insights. This allowed us to build automation to detect new instances, databases, changes, incorrect editions etc and manage the estate, and estate that had grown through acquisition.
All very well when you have nice, neat little walled garden, but when you are in SQL chaos, through no fault of your own, like when your company acquires another company as big as yours but not as joined up, in several countries, with at least as much SQL again (450 instances total), but not managed that you then have to assimilate with limited resources, you need tools you can rely on.
Will have a look at the Redgate tool, funnily enough was listening to old Brent Ozar podcasts on the way to work today and someone asked the same question and he mentioned Idera had just released this tool.
I did speak to Idera about the lack of such a tool at SQLBits in Nottingham in 2013, they took it on board and contacted me and asked for a meeting which we duly had, I showed them the in house tool we had developed in .net, then they went away and didnt hear anything further until having been made redundant and got a new job I was on the lookout for a new tool, a Idera now have something very similar to what we talked about, might be coincidence but it does seem like what we discussed.
To be honest, if you've done it correctly, no such auto search should actually work. Imagine someone nasty getting into your box and being able to auto-discover the keys to the city by using a similar tool. I understand that it IS a tradeoff, especially for a new DBA. In such a case, I'd use such a tool once to verify the spreadsheet and then lock the domain down to prevent the use of such tools or at least limit the damage that someone nasty using such a tool could pull off.
It doesnt modify anything, it simply collects information like every other scan tool, every network has various discovery and monitoring applications and its a case of setting it up correctly using appropriate credentials with low privilege to do only what they need to do, and using firewalls, no keys to the city. How many have spreadsheets with lists of passwords in ?
What does scare me is managing stuff with SQL CMS and big groups of registered servers.
Spreadsheet data drifts, with the best will in the world, even if its just one person, it doesnt scale past one, "must update that memory allocation and patch level, oh its locked, will do it later....", the team will be growing soon and be in separate locations so I need a tool that scales, and after all, DB guys, using a bloody spreadsheet, that's like an F1 driver using a small hatchback !
You need to data to update itself as much as is possible to avoid re-keying, not got time for that, then you can concentrate on adding intelligence to it, a narrative.
September 4, 2018 at 11:05 am
I would suggest creating a centralized dedicated monitoring database servers which will talk to all the environments and write the information back in to tables.then execute the reports on your monitoring database.
September 4, 2018 at 12:52 pm
mark 52160 - Tuesday, September 4, 2018 6:53 AMJeff Moden - Tuesday, September 4, 2018 5:56 AMmark 52160 - Tuesday, September 4, 2018 1:54 AMJeff Moden - Monday, September 3, 2018 7:56 PMMy personal feeling is that if you actually need a 3rd party tool to discover server instances, applications, and licenses, then you have too many unqualified cooks in the kitchen building things on a whim instead of a plan.Or, you have inherited a large estate currently managed in a spreadsheet which it has outgrown and want to provide something better that allows multiple DBA's to access the same set of information ?
One DBA left last year and the remaining one is leaving at the end of the year, I joined four months ago and two more DBA's are being recruited.
Spreadsheets dont auto update, it is manual and with 160 instances quite onerous, we used Lansweeper at the last place which is not a SQL tool but it does a daily scan on each SQL Server and is "SQL Aware" so captures the config info and database list and allows custom fields and us to augment the collected data with our insights. This allowed us to build automation to detect new instances, databases, changes, incorrect editions etc and manage the estate, and estate that had grown through acquisition.
All very well when you have nice, neat little walled garden, but when you are in SQL chaos, through no fault of your own, like when your company acquires another company as big as yours but not as joined up, in several countries, with at least as much SQL again (450 instances total), but not managed that you then have to assimilate with limited resources, you need tools you can rely on.
Will have a look at the Redgate tool, funnily enough was listening to old Brent Ozar podcasts on the way to work today and someone asked the same question and he mentioned Idera had just released this tool.
I did speak to Idera about the lack of such a tool at SQLBits in Nottingham in 2013, they took it on board and contacted me and asked for a meeting which we duly had, I showed them the in house tool we had developed in .net, then they went away and didnt hear anything further until having been made redundant and got a new job I was on the lookout for a new tool, a Idera now have something very similar to what we talked about, might be coincidence but it does seem like what we discussed.
To be honest, if you've done it correctly, no such auto search should actually work. Imagine someone nasty getting into your box and being able to auto-discover the keys to the city by using a similar tool. I understand that it IS a tradeoff, especially for a new DBA. In such a case, I'd use such a tool once to verify the spreadsheet and then lock the domain down to prevent the use of such tools or at least limit the damage that someone nasty using such a tool could pull off.
It doesnt modify anything, it simply collects information like every other scan tool, every network has various discovery and monitoring applications and its a case of setting it up correctly using appropriate credentials with low privilege to do only what they need to do, and using firewalls, no keys to the city. How many have spreadsheets with lists of passwords in ?
What does scare me is managing stuff with SQL CMS and big groups of registered servers.
Spreadsheet data drifts, with the best will in the world, even if its just one person, it doesnt scale past one, "must update that memory allocation and patch level, oh its locked, will do it later....", the team will be growing soon and be in separate locations so I need a tool that scales, and after all, DB guys, using a bloody spreadsheet, that's like an F1 driver using a small hatchback !
You need to data to update itself as much as is possible to avoid re-keying, not got time for that, then you can concentrate on adding intelligence to it, a narrative.
I know that you're not modifying anything with it. I'm more concerned about someone nasty using something similar to get the lay of the land for your domain so that they can mount a very silent and deadly attack to walk away with a full and regular payload of data.
I agree that spreadsheets aren't the way but auto-discovery shouldn't be used for anything except to verify that nothing has changed. If there's not a process where changes (moves, adds, deletes, updates, etc) to servers aren't being documented by some sort of ticketing and tracking system, you might have your hinny sticking pretty far out when it comes to audits.
Just saying because I'm an old dude that has seen some folks get tattooed by such audits.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2018 at 12:41 pm
Grant Fritchey - Monday, September 3, 2018 3:08 AMMost people I know who have large estates purchase a third party tool to help with the management. I know that when I was managing a large SQL Server estate, I used a 3rd party tool.Now, I work for a software vendor that makes that kind of software. It's called SQL Monitor. It has some specific tooling and reports around estate management. You might want to check it out.
There are other 3rd party tools that do this as well. You really should find and use one that works well for you and your business. You should not be managing large numbers of SQL Server manually. Automation is the key to getting this work done. So, even if you don't use a 3rd party tool, you need to automate everything you do yourself.
I'm using redgate SQL Monitor for our (small) group of SQL Servers and find it very helpful. The new Estate tab that was added recently is great for seeing versions and notes about anything being out of support as well as links to the latest SPs / CUs. There is a ton of good diagnostic information to help you troubleshoot issues and a good alerting system. I second taking a look at it.
September 5, 2018 at 2:20 pm
I built an in-house monitoring system based on Rodney Landrum/David Bird's SSIS packages 10 years ago and it is still going strong today. I have expanded it quite a bit over the years to include more alerting and functions:
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/61774/
The package does a full run first thing in the morning which includes auto populating database and server info tables then does regular checks throughout the day looking for issues.
My SQL servers are slowly migrating to the cloud or managed services now but I was looking after just over 100 SQL servers and 1,500 database on my own up until a couple of months ago.
September 5, 2018 at 3:49 pm
CC-597066 - Wednesday, September 5, 2018 2:20 PMI built an in-house monitoring system based on Rodney Landrum/David Bird's SSIS packages 10 years ago and it is still going strong today. I have expanded it quite a bit over the years to include more alerting and functions:http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/61774/
The package does a full run first thing in the morning which includes auto populating database and server info tables then does regular checks throughout the day looking for issues.
My SQL servers are slowly migrating to the cloud or managed services now but I was looking after just over 100 SQL servers and 1,500 database on my own up until a couple of months ago.
Just curious about this. I notice in the article that it grabs job history from the last 7 days. Not sure what it does with all of that for reporting purposes especially when you have jobs (more jobs than I care to admit to) that run once per minute, every minute, 24/7.
How often do you clean out legacy data from the tables this uses?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2018 at 5:08 pm
When the package does its hourly run, it generally moves data in to an Archive table so it is only reporting or alerting on the current set of data. I have added more archiving over the years so don't need to pull back so much data during each run but it sounds like some of your archive tables would get quite large!
The amount of data held depends on what it is. I keep job history for 6 months but I have database info going back to 2009 which is really handy for reporting on long term database growth. There is also a daily report that uses the same data to alert on any databases that have grown by more than 10% in the last 24 hours plus another one that does the same thing but for growth over the last 7 days. They are useful for spotting sudden, unusual growth or growth over time that you might not spot otherwise.
Other alerts include things like missing database and log backups, instances recently started (although I get an alert on restart anyway), filtered errors from the errorlog, databases close to growth limit, databases with small next extent, long running jobs, long running transactions, blocked sessions, databases with log files that are bigger than the data files, new SQL servers that are on the network that aren't being monitored or ones that I have temporarily disabled monitoring for.
I also store WhoIsActive data and Perfmon data every minute on each server. That has been a game changer for me because it allows me to retrospectively trouble shoot performance issues.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply