How Do You Setup Your Instances?

Question

How Do You Setup Your Instances?

Steve Jones - SSC Editor

SSC Guru

Points: 736183
More actions
September 13, 2018 at 9:17 pm

#363639

Comments posted to this topic are about the item How Do You Setup Your Instances?

Viewing 8 posts - 1 through 7 (of 7 total)

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

DinoRS SSCrazy Points: 2683 More actions · Answer 1

Nowadays I hardly do any SQL Server Setup anymore, customers usually provide a SQL Box as they wish to have by own policy. I mostly do some fine tuning around CTFP, check max Memory, maxdop etc. for feasibility. I spend much more time analyzing existing DB access patterns during times of slow running Jobs / Queries and have a look at a few Design things I can modify like FileGroups and Number of Files in each.

But I did set up MSSQL Servers by DVD, ISO, System Center Service Manager Templates, Powershell and I still am looking for a potential future customer which might see use of centralized DSC for deployments at scale. I've seen some demos with IIS providing such configuration.

call.copse SSCoach Points: 18050 More actions · Answer 2

I've done it manually plenty of times, I'm inclined to leave it to those whose speciality it is these days, plenty of other things to be done - that's kind of a good part of being in a bigger company. Mind you I'm never quite sure if it's been done right but am just happy to have it working.

hjp Default port Points: 1437 More actions · Answer 3

Small company = I have had to live with two different approaches.
1. Doing everything myself.
2. Learning to deal with a (mentally remote) "hosting" company's lack of knowledge and unprofessional attitude towards databases they are not using themselves.

While living in scenario 1, I gradually became better at tuning. Of course, it was manual setup since it was "learning as you go along". Every change of a default setting was triggered by things happening or not happening, so: a very reactive process. After a while, I have collected a larger list of changes, and eventually I build a script to run after initial deployment, and a "how to" one-pager to remind myself of the necessary steps. I used it mainly for every so often as IT department decided to fry something - including changing my computer, because senior management wanted a different label to sit on the front...

While living in scenario 2, I have to press hard just to be informed about the settings they have chosen. It is impossible to get told why they choose as they do! And it is equally impossible to change their minds. We are just too small to carry any weight in their decisions, but we surely didn't signed the contract based in their hosting capabilities. Other matters were far more important to senior management.

jasona.work SSC Guru Points: 50117 More actions · Answer 4

My last round of deployments, I set up a command line install script. Tweaked the locations for the databases, logs, etc, set the service accounts and the first SA account, then ran with it. Remove any services not required on a particular server before the install, and the nice part is, all my servers have the same basic setup from the git-go.

Then tweak the various settings like Max Mem after the install, and start adding logins. We've got a fair number of SQL Logins, sometime back I found a script from someone (don't recall who) that is able to pull the login and password and create a "create login" script, that also duplicates the SID for those accounts (so I don't have a mass of orphan logins to fix on the new server.) The various Windows logins are easier to recreate / script out on the old server.

I suspect when we do our next migration sometime in the future, I'll be able to just tweak my current command line install and use it again.

andrew gothard SSChampion Points: 12314 More actions · Answer 5

All scripted. Backups, overnight checks, alerts, DBAUtilities database, tools (sp_blitzX, sp_WhoIsActive), monitoring jobs, MaxDOP and other non-standard settings, backup locations. There are some minor hand tweaks - IFI, TempDB and Model growths and sizes and startup trace flags, adding to Server Group for SSMS - but pretty much everything is standardized, predictable and reliable - as well as being quick and easy.

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 6

We have a PowerShell script that installs SQL Server using all of our internal best practices.
It will download and run the mssql setup executable for appropriate version and latest cumulative update
, and then once done, it will:

- create all the data and log file folders
- split tempdb into appropriate number of files depending on the machine's CPU count
- create logins, alerts, server settings, trace flags
- setup dbmail, xe audit traces, backup schedule, maintenance plan (Ola Hallengren)
- create SA account with random password and then make web service call to add that server name + password to Secret Server key vault
... and 100+ other things

Still, it doesn't guarantee that the install process will complete without issue. The script is a constant work in progress as environments, platforms, and prevailing best practices change.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

mfagan12 Default port Points: 1432 More actions · Answer 7

I work in a small company where one guy and I share DBA duties, even though he's the one who has DBA in his title. I'm the one who has the most influence and pushes for upgrading/setting up new instances and which version to go to as I do the database development.

We do things manually for the most part. I take care of installing on the test server and usually note the differences between versions and pass the info along to my coworker. He installs on production. I know he utilizes Powershell scripts for logins.