Plan Installation

Microsoft Skill Measured: This objective may include but is not limited to: evaluate installation requirements; design the installation of SQL Server and its components (including drives and service accounts); plan scale up vs. scale out basics; plan for capacity, including if/when to shrink, grow, autogrow, and monitor growth; manage the technologies that influence SQL architecture (including service broker, full text, and scale out); design the storage for new databases (drives, filegroups, partitioning); design database infrastructure; configure a SQL Server standby database for reporting purposes; Windows-level security and service level security; Core mode installation; benchmark a server before using it in a production environment (SQLIO, Tests on SQL Instance); choose the right hardware

What I see

–          Installation requirements

–          Service accounts

–          Scale up vs. scale out

–          Database shrinking, growing, autogrowth, growth monitoring

–          RAID

Installation Requirements

                For a complete listing of installation requirements, please see MSDN’s Hardware and Software Requirements for Installing SQL Server 2012.

Service Accounts

                For a complete reference on all service account considerations regarding Virtual Accounts, domain accounts, permissions granted during setup, etc. please see the BOL reference on Configure Windows Service Accounts and Permissions documentation.

Scale Up vs. Scale Out

                To learn the differences between the two different scalability models, see this Wikipedia reference.

Database Shrinking, Growing, Autogrowth, and Growth Monitoring

                As a general rule of thumb, routine database shrinking is not recommended.  Shrinking the log is sometimes necessary if, for instance, you are in full recovery mode and transaction log backups haven’t been taken frequently enough and the log has grown out of control.  As per Paul Randal’s blog article on the topic, it can cause index fragmentation.  Please reference Paul’s article for reasoning and proof against data file shrinking.

                Conversely, database file growth is a necessary action during the lifetime of a database.  There are two ways to control this:  Manually (with ALTER DATABASE… MODIFY FILE…), and automatically (setting autogrowth).  The manual process is simple specifying a new (larger) file size:

use master;

go

alter database AdventureWorks2012

modify file

(

       name = ‘AdventureWorks2012_Data’,

       size = 200MB

);

go

The automatic way is by setting autogrowth with the FILEGROWTH file specification.  When this is set, the respective file grows by a certain byte increment, or percentage increment:

alter database AdventureWorks2012

modify file

(

       name = ‘AdventureWorks2012_Data’,

       filegrowth = 20MB

);

go

With the above setting, when the database file with the logical name of ‘AdventureWorks2012_Data’ becomes full, it will automatically grow that file by 20 MB (provided it hasn’t reached MAXSIZE).  This can be specified as KB, MB, GB, TB, or a percentage (%).  In order to monitor file growth, there are a couple of avenues.  You can use a SQL Server Agent Alert to notify when a log file has grown.  Also, you can utilize Extended Events to capture log file growth, and perfmon exposes a counter for this.  As for data file growth, you can monitor data file size and used space to get a view on growth and, if manually done, when it needs to happen.

RAID

                See this BOL reference on different RAID configurations.

References/Documentation

–          MSDN reference on Hardware and Software Requirements for Installing SQL Server 2012

–          BOL reference on Configure Windows Service Accounts and Permissions

–          Wikipedia reference on Scalability

–          BOL reference on RAID Levels

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s