Voice of the DBA

Tag: configuration

  • How Do You Setup Your Instances?

    I’ve set up a lot of SQL Server instances in my career. I’ve gone from manual only setup in SQL Server 4.2 to more automated means in the latest versions. The easiest was actually in Azure where I only need to specify a few parameters for a PoSh cmdlet. However, unattended setup is pretty easy as well for local SQL Server instances. If you’ve never done it, you’re missing out. There are plenty of other ways to do this with tools like Chef and Puppet, AMIs in AWS, and more.

    Erik Darling wrote a post recently called Setting Up SQL Server: People Still Need Help. Erik’s point in the piece is not that installing SQL Server is hard, but that many people stick with the defaults once they’ve installed the instance, never changing anything. There are some basic things that you’ll want installed all the time, so having a repeatable process is important.

    This week I’m wondering how you install instances in your job. If you need to add a new SQL Server for production or development, what do you do? Let us know your process and procedure.

    I don’t set up too many instances, and in fact, I mostly add them as a lab instance on one of my machines. For the initial install at times, I’ll just run through the manual install, but I then have a quick config script that I use to change a few items, but very few. In most cases, I don’t do much more than add a few logins, limit memory, and enable the DAC. For the cases where I want to add a few different instances for testing, say for looking at patches or using mutli-server features, I’ll use an unattended install script.

    There are some amazing ways people have created for repeatable installs, such as the Finebuild project. If you’ve got a way that works for you, let us know. Just be sure that whatever repeatable process you use changes some of the defaults and ensures your SQL Server is better prepared for any workload to come.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3..0MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • SQL Server Thumbnail Metrics – OS Memory

    I’m gathering a few metrics around the Internet for SQL Server from people that I think really know how to run a SQL Server. This is a series of posts that I’m making to gather these metrics up for easy access later.

    Host Memory

    The short metric is:

    Leave 4GB for the OS, or 10% of physical (or guest) memory, whichever is greater.

    Examples: If I have the following amounts of RAM, I leave this for the OS.

    • 16GB of RAM – leave 4GB since 4 > (16 * 10%)
    • 32GB – leave 4GB since 4 > (32 * 10%)
    • 64GB – leave 6.4GB since (64 * 10%) > 4

    Essentially leave 4GB until you get to 48GB of RAM, then go to 10%.

    References: Mr. Brent Ozar of Brent Ozar, PLF who’s presented and written this a few times, but the reference I’m using is: How to Set SQL Server Max Memory for VMware

    Explanation: In the older x86 days there were all sorts of rules for setting memory for the OS and SQL Server. I won’t go into those, and for the time being we’re working in the x64 world, not x128. For that, you need to be sure the OS isn’t starved by SQL Server and unable to manage the host processes. These days 32GB isn’t expensive, and 4GB gives you a nice cushion at lower levels. At higher ones, you need