July 20, 2017 at 8:57 am
With so many database servers (prod, test, dev ) it is hard to keep track of them all. What are some ways to keep track of all database servers that you have implemented?
July 20, 2017 at 9:22 am
What I've done in the past is setup a small bit of monitoring/tracking on each one. This might be separate from an enterprise monitoring suite (like SQL Monitor) since I don't necessarily monitor all dev/qa instances. I'll have a database and some jobs that track space, backups, config, etc. This is standard for all instances, but this let's each instance keep track of itself.
Then I have a job (push or pull) that moves this data to a central system, with a daily report to me. I have left joins so that I get a complete list of instances, but then the daily data if it's there. When it isn't, I go checking.
This let's me have a decent idea of what the estate is for planning, upgrades, costs, etc. If we find dev instances, we send them our setup script so they can put the base monitoring on it and we can keep track of the instance.
This isn't a replacement for a monitoring tool, and I'd likely buy something like SQL Monitor for those instances that need daily watching. However, a small inventory system as part of monitoring is helpful.
July 20, 2017 at 10:12 am
I have a database table containing one row for each MSSQL instance, and this table contains columns for physical environment, settings, capacity, etc. in addition to a description and the names of it's related DEV, QA, PROD environments. I also have a PowerShell scripts that queries this table and can ad-hoc execute a sql script across all servers, outputting the result-sets into a single Excel sheet. With this, I have a basic framework for performing almost any small or medium sized task that may come up.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 20, 2017 at 10:30 am
I actually keep a spreadsheet with all the database servers in it. I use a free third party tool from Idera to discover instances on the network and track their SQL Server version:
https://www.idera.com/productssolutions/freetools/sql-instance-check
I track the following in the spreadsheet for each instance:
- instance name
- description
- SQL Server version
- SQL Server service pack, CU
- SQL Server edition
- OS version
- CPU cores
- is hyperthreaded?
- physical RAM
- UNC of folder for database backups
- log backups and frequency
- components used (SSIS, SSRS, SSAS)
- IP address
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply