One Hour Server

3

May 25, 2012 by Mike Hillwig

When I presented by 1 Hour Server session at SQL Saturday 142 in Waltham last weekend, one of the things I stressed to my audience is that this is insanely easy and that anybody can do this.

There are really two steps to my process. First, I automate the setup process. The next step is to take my configuration checklist and automate it.

When I run through the installer, I emphasize that it’s a lot of non-value added time. You basically spend a lot of time helping the installer build a response file. Once you have that file, you can recycle it over and over, saving you hours of time.

myWPEdit Image

The key is walking through the installer and grabbing that file at the end. After that, I do a quick edit of that file by commenting out the UIMODE line. I then throw that into my batch file to have it run the setup silently.

“C:\Software\SQL2012\setup.exe” /QUIETSIMPLE /IACCEPTSQLSERVERLICENSETERMS /configurationfile=“C:\1HourServer\ConfigurationFile2.txt” /SAPWD=“SA<em>Password</em>goes<em>here” /SQLSVCPASSWORD=“service</em>account<em>password</em>goes<em>here” /AGTSVCPASSWORD=“agent</em>account<em>password</em>goes_here” 

After that, I walk through my scripts and put them into the same batch file. I have everything that I do in reusable scripts, such as creating my backup SQL Agent jobs to calling Michelle Ufford’s index maintenance scripts.

One of the little nuggets here is that when you do something with SQL Management Studio, click the little “Script” button and have it generate the SQL for you. Once you have that, you can recycle that over and over.

myWPEdit Image

The other thing I do is right-click on a SQL Agent job and generate the SQL to recreate the job. That too becomes a script.

myWPEdit Image

Then I take those scripts and call them with SQLCMD in my batch file.

Ultimately, I have a batch file that looks like this. The last line just reboots the server for good measure.

"C:\Software\SQL2012\setup.exe" /QUIETSIMPLE /IACCEPTSQLSERVERLICENSETERMS /configurationfile="C:\1HourServer\ConfigurationFile2.txt" /SAPWD="SA<em>Password</em>goes<em>here" /SQLSVCPASSWORD="service</em>account<em>password</em>goes<em>here" /AGTSVCPASSWORD="agent</em>account<em>password</em>goes_here"
"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd" -S localhost -E -i "c:\1HourServer\mailprofile.sql"
"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd" -S localhost -E -i "c:\1HourServer\cdbaoperator.sql"
"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd" -S localhost -E -i "c:\1HourServer\enable<em>dbmail</em>alerts.sql"
"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd" -S localhost -E -i "c:\1HourServer\system_alerts.sql"
"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd" -S localhost -E -i "c:\1HourServer\audit.sql"
"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd" -S localhost -E -i "c:\1HourServer\backups.sql"
"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd" -S localhost -E -i "c:\1HourServer\tlogbackups.sql"
"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd" -S localhost -E -i "c:\1HourServer\revlogin.sql"
"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd" -S localhost -E -i "c:\1HourServer\purge_backup_and_agent.sql"
"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd" -S localhost -E -i "c:\1HourServer\failed_agents.sql"
"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd" -S localhost -E -i "c:\1HourServer\checkdb_full.sql"
"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd" -S localhost -E -i "c:\1HourServer\checkdb_physical.sql"
shutdown /r /t 0 /d p:4:2

Really, it’s that easy.

Here are a few resources I mentioned in the session.

Script library at sqlservercentral.com

Ola Halengren’s Maintenance Scripts

Michelle Ufford’s Index Maintenance Scripts

Paul Randal’s Blog (or any other author at sqlskills.com)

  • Qun Lily

    Thank you for the post. It was a great presentation on Saturday.

    A couple of more questions:
    – During installation, do you recommend use the same account for all SQL Server services?
    – What account do you usually use? Do you have to create the account before installation?

  • Qun Lily

    Could you please also provide an example of your configuration file?

    The configuration file I generated did run without any error in summary file. However, no installation was done. Any idea or suggestion?

    THANK YOU!

  • Здравствуйте Олег!В Вашем случае надо правильно отредактировать файл palo.ini и перегрузить palo-сервер (можно перегрузить компьютер).Пример правильного файла:splash-limit 1000 500 100goalseek-limit 1000goalseek-timeout 10000user-loginverboseinfotemplate-directory ../Apilog C:\Program Files\Jedox\Palo\Log\PaloServer.log http 192.168.2.64 7777template-directory C:\Program Files\Jedox\Palo\Api В данном случае указан порт 7777. К нему и connect.