Planning an installation on SQL Server 2012 is under the Install and Configure SQL Server objective which is 19% of the total examination for 70-457.
I will be writing all the important things to consider planning an installation for SQL Server 2012 in a nugget form to serve as a handy review notes for the certification exam. This topic can also be used as part of your review if you are going to take 70-462, Administering SQL Server 2012 Databases. As I have explained before 70-457 is an upgrade exam for all DBA with MCITP certification for SQL Server 2008.
Topics under Planning an Installation
- Evaluate installation requirements
- Designing an installation
- Scale up versus scale out
- Capacity planning
- Designing new database
- Identify standby database for reporting
- Windows & service level security
- Core mode installation
My nuggets on each topic:
On evaluating your installation requirements
- Identify the necessary components needed. Database Engine only? SSIS? SSRS? SSAS?
- Which SQL Edition is needed to be installed
- Standard Edition
- Business Intelligence Edition
- Enterprise Edition
- Web/Development/Express Editions
- Minimum hardware requirements. How much CPU? How much RAM? Disk requirements?
- CPU requirement : 1.4Ghz or higher
- Memory: 1G (512 for express edition)
- Storage: atleast 1G for the system and data files
- Consider virtualization requirements
- Choosing which design? Instance components or shared components?
- Operating system prerequisites;
- .NET 3.5 SP1
- .NET 4.0
- Windows Powershell
- IE7 or higher
Scale up vs Scale out
- Scale up means increasing the current resources of your server.
- Scale out means adding more servers to your current one.
- In scale up, has larger impact in event of failure. In time of failure or downtime all services are down.
- In scale out, there is lesser impact on failure or downtime.
- Small database that grow on demand can increase fragmentation
- Fixed database size can reduce fragmentation
- Auto-grow can cause file fragmentation
- Auto-shrink can cause index fragmentation (I really think they should get rid of this.)
Designing new databases
- Use RAID 1+0/5 for better performance and fault tolerance benefits.
- Isolate the location of the log file to another physical disk. It is best to use RAID 10 for better write performance
- Use filegroups. Place tables with most heavy writes on a RAID 10. Place tables with most heavy reads on RAID 5. The primary filegroup can be placed on RAID10 or RAID 5.
- Place your tempDB on a RAID10 disk for better write performance.
- Consider creating multiple tempDB files proportional to each logical CPU on your server to enable the SQL Server scheduler workers to loosely align to a file.
Identify standby database for reporting
- Log shipping – this is just a warm standby, no automatic failover
- Mirroring – this is considered a hot standby, will be deprecated in futures editions and will be replaced by the AlwaysOn feature
Server Core Mode Installation
- Can be performed only on Windows Server 2008 R2 SP 1
- All editions of SQL Server 2012 are supported
- No GUI, only command line
- Most secure way of installing SQL Server 2012
- Isolate services with separate service account. Advantage is that if a service account is compromised, only that specific service is compromised.
- You can also use virtual accounts or managed service
- Use SQLIO to determine your hardware I/O capabilities. This is a separate tool and can be downloaded here.
- Use SQLIOSim utility to validate disk integrity. To download this utility and a deep dive, click here.