Server Organization

  • 😎 I just secured a job where the company has about 98 SQL Servers installed. I only know this by polling the network for them. I think I have linke 187 instances of SQL Dbs floating around but they are scattered. They are in no rhyme or reason. Test and dev are usually on the same server and there is no real backup process in place.

    I am in the process of finding these servers and instances and identifyling them to an application. The instances are unpached and on inadequate servers. It has taken me 3 months to find out this much.

    In the intrim they are deploying 4 major projects with new server taht were built by infrastructure but no one knows the names of them.

    Also they want me to constuct the policies and procedures for the databases, job accesses and vendor accesses (since on vendor logged in without knowledge and restarted the server and applied 48 windows patches to the server which crashed the database :w00t:)

    I feel like im spinning my wheels. In not a new DBA (10years exp) however I have not ever found myself in this much disarray. Has anyone any suggestions how I can organize this mess besides quitting?????:crazy:


    The DBADiva

  • First, get backups running on production servers in an organized fashion. Just built a policy for now, then start getting things changed based on criticality.

    Second, as you set things up, SCRIPT EVERYTHING. Make sure that if you setup a backup, you do so with a script so that you can deploy the same thing on other servers.

    Third, get a place to test restores. Last thing you need is an explosion on production without being ready. Get this ready, practice a few restores, do log restores, point in time, get some familiarity with what's needed on these systems.

    From there, you can start to look for others test/dev servers, set up a reasonable backup strategy. If you're not sure, post back here with specific questions in a new thread.

    I would also not worry too much about all those instances. Some may be MSDE, so you might get a domain admin to query all of them and get back the results from

    select @@version

    If you see Express or MSDE, it's a local instance. Not worth worrying about for you.

    Now, take a deep breath. This is a great chance for you to learn. Every time you think about doing something think about how to apply it to multiple servers. SQL Server has some good tools, and as you try to handle something like jobs, backups, whatever, come up with a plan. Test something on one server, make sure you have a script, and then post a new thread here, asking what others think. Then you can deploy things.

  • I like MSSQL Central Management Server especially for deploying Ola's Backup Solution across a large amount of SQL Servers.
    If you have to store backups in different locations depending on the Environment or other factors, make different CMS Groups and deploy your scripts by these groups.
    Generally I like handling things where possible through CMS, need to find out what Patchlevel all your Instances have? SELECT @@VERSION against them all, same goes for NUMA Configurations and other things which might be of importance.

    If you want to be able to deploy new SQL Servers to certain standards, have a look at Desired State Configuration. Basically you need a Webserver running and can deploy Web-, App- and SQL Servers to preconfigured DSCs.

    I think once you've gotten that far, you'll figure out many more things you can change.

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

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