This will be a work-in-progress article, so it’ll be updated. If you’re interested; I suggest you bookmark or subscribe 🙂
What I’ve noticed in my travels, is that SQL Server is one of those things that alot of people install, but few people configure properly. I have been one of those people, though I have tried my best.
And it’s understandable. Often times, you’re looking to set something up for a single purpose maybe, and SQL Server does work as a next-next-finish job. But it’s REALLY bad practice, and your performance will be hit hard. So don’t do it!
So, I am not an SQL guru by any stretch of the imagination, so this is not meant to be “the ultimate guide”. What I aim for here, is to condense the principles of SQL setup down to understandable settings. If you’ve googled SQL setup; chances are you’ll have gotten a bit of a headache from the technical stuff.
It’s complex, and there is a reason that there are dedicated people doing SQL usually. But many people, and companies don’t have that luxury. So consider this a dummy’s guide to SQL, made by a dummy (so I can relate).
If you’re a SQL wiz, don’t shoot me; but feel free to make constructive comments 🙂
Disks
Now, when you’re setting up you server, first rule of business is to design your disk-layout. You’ll obviously have your OS-drive, but we’ll aim not to have anything SQL Server-related there. If you must, for some reason, you can do the SQL Server install there. But for this design, we’ll separate that as well.
My layout would be like this:
- C-drive (40-60 GB)
- D-drive (SQL Server Install; 20 GBs)
- E-drive (TempDB; 10-20 GBs)
- F-drive (Databases; size as required)
- E-drive (Logs; size as required)
- F-drive (Backups, if needed, not everyone requires SQL-backups if it’s handled by other software).
Once the disks are configured; you might be tempted to initialize them all with default settings. Wrong! All SQL data disks should be formatted with Allocation Unite Size of 64K (default is 4K). and I format all my disks with GPT, though performance-wise it’s not a difference there.
In complex environments that might not be 100% accurate, but if you’re reading this, you’re not there. The OS drive is automatically formatted with 4K; but we’re not using that for SQL, remember?
The difference in performance is on average probably something around 15-20% (I’ve not done extensive testing on this, so I’m relying on numbers I’ve found). So you want to do that! In fact, if you’ve already set up your drives, I’d consider redoing them with 64K.
So next segment will be…
SQL Install / Setup
Stay tuned 🙂