[vc_row][vc_column width=”2/3″][vc_column_text]DOMAIN AND HOSTING:
This week I bought a domain and hosting. This is for a separate project but worth mentioning because of its implications for SQL. The hosting I bought was specifically chosen for the ability to have SQL Server hosted. I have three databases hosted on GoDaddy’s servers for my site. In reality they provide me more for a centralized development database so a disjointed team, without a collocation, can work together.
I started (pre-purchasing) by doing the database design. I laid out all of the tables, columns, data types, and relations similar to how we did it in the database class. All tables are related to each other in some way, using foreign keys and primary keys where it was applicable. One of the new things was SQL server’s way of doing incremented integers. It’s not an autonum feature, but rather you declare it an int, and declare that it increments, making it just different enough to be a short-term problem . I then specifically scripted out the database creation and table creation. This took a few iterations since I would run it and it would complete part way, and then error out at some part. I specifically wanted this script to be runnable multiple times, on multiple servers and give me the same database. I went through a very similar process with a MYSQL database, in fact, I copied my SQL Server script and converted it to MYSQL manually. It wasn’t until this point when I realized how similar but vastly different the two query languages are.
After buying the hosting I am happy to report that my script ran in one attempt, creating everything I needed it to be for development to start. Scripting in this fashion I believe will prove useful as time goes on, a quote from today was “A DBA’s motto is if you have to do it more than once, you may as well script it out”. They also pointed out that scripting is a great way to deal with change management in companies, since you can paste it directly into the change control document.
I haven’t had the opportunity to try my MYSQL script out on a second server yet, I’m waiting for one of my colleagues to set up the MYSQL hosting on his personal site.
BOOT CAMP:
On to the boot camp. I’ve spent a day here, and it’s safe to say I’m not at all disappointed (in the class or the weather). I’ve taken extensive notes from today that I’ll annotate below. The blog I was discussing in previous weeks were laying ground for this class, so some of it will be review from there. The detailed subjects covered though were mind-boggling. I’m converting this all from handwritten notes, so please excuse the bulleted structure.
We started out with Hardware:
Processors
Intel over AMD
The clock speed is hands down better
AMD has more cores, but with new versions of SQL Server more cores means heavier licensing.
Geek Bench is a good benchmarking tool
Licensing hits each socket for at least 4 cores, no reason to have less than a quad core processor
L2 and L3 cache sizes are an important thing to look into
Processors with Non unit memory Architecture (NUMA) are the way to go
Memory
Memory can be used as a band aid for bad IOPS
Max out memory for the licensing
If the licensing allows 64 get 64 gbs, after 64gbs the gains become less.
IOPS (Storage)
Insufficient RAM or CPU speed will cause IOPS issues
A good configuration would be servers with all SSDs, with Fusion PCIE cards, since you will actually bottleneck at SATA
SAN storage isn’t bad.
More small drives is better than fewer large drives.
When planning storage, you have to consider transfer size, max latency, high availability requirements, and size, not just size.
Recommended equalogic ps 6100x which is a hybrid array.
RAID
Most databases use RAID 5 for cases where reads exceed writes.
Raid 10 should be used for transaction logs, since these are the logs used when restoring in a disaster recovery (DR) situation.
Virtualization
You become limited by the hyper-visor you use.
64 virtual CPUS for Hyper-V and ESXi 5.1
1 tb memory for Hyper-V and ESXi 5.1
Virtual Environments work well for DR
Sans with ID virtualization can use direct fiber pass through which brings speed up considerably
Avoid overcommitting RAM
Overcommitting CPU is safe up to double, CPU schedulers should be able to handle the load
If you have a mixture of application and database machines, it’s better to group them up,
SQL servers have a better time scheduling with SQL servers, and app servers have a similar outcome.
Enable hyperthreading
Enable virtual NUMA
Disable hotadd memory and hotadd cpu.
Install/Configure
Before installation
install and fully patch windows
disable windows updates after patching
update BIOS, firmware, and drivers
Set powerplan to high performance in BIOS and windows
Use cupid.com to check cpu performance
Create separate domain accounts for each service
give accounts the right to perform volume maintenance, and lock pages in memory.
Enable install file initialization over Zero initialization
Final Checks
Give it a static IP
Make sure antivirus has the correct exclusions for SQL server (there is a KB article about it)
Manual optimize the C drive (or defrag prior to windows 2012)
Make sure there is no pending reboots
Decide what features SQL needs and only install
During Install
Decide Standalone or Fail Over Cluster
Decide if it’s a default or named Databse
Use service accounts
Decide Authentication Mode
Windows is preferred to mixed
Decide where data directories go.
Best Practices
Enable remote DAC
Remove sysadmin from builtinadministrators
Set max server memory in settings
+1 gb for OS
+1 gb for each 4 gb < 16 gb
+1 gb for each 8 gb > 16 gb
Set Max Degree of Parallelism
Sets to ma number of threads per query
Non numa processors should be set to the number of physical cores
Numa processors should be set to number of cores on a single cpu
Set cost threshold
25 is a safe number
Optimize AD hoc workload
Check error logs
Configure firewall rules
Make multiple temp db files, start with 4 4gb files
Enable autogrowth.
Enable auto update stats asynchronously
Enable parameterization
Leave page verify as checksum
Database Mail
Must use a separate smtp server
At least one sql operator
Check for SQL errors severity 19-24
Check for error 825
Database File layouts
This is heavily a design portion
Create at least one additional file group
More filegroups make for a more flexible database
Autogrowth should be fixed not a percent
DISABLE AUTO SHRINK (this is very bad)
We also touched on security, but we didn’t get very far into it before we ran out of time, I’ll leave that for next week. We had class from 8:30 to 5:45 today, and have the same for the next two days. So far it’s been an intense but great experience![/vc_column_text][/vc_column][vc_column width=”1/3″][/vc_column][/vc_row]