:: infotechGuyz.com ::
> For the IT community by the IT community

• BlackBerry Server (BES)
• Excel Macro/VBA
• Exchange 2007
• Exchange 2010
• Lync 2010
• Scripting/Command-line
• SQL Server 2008
• SQL Server 2012
• Team Foundation Server
• VMware
• Windows 7
• Windows 8
• Windows Server 2008
• Windows Server 2012

Configuring SQL Server 2012 Database Server

 

SQL 2012 is a highly scalable database engine and allows for customizations to fit your needs. Each named instance can be configured to meet your application requirements including trigger recursion, auditing, and resource usage. Customizing DB instance in SQL Server 2012 allows DBAs to meet SLA requirement set by an organization. It is recommended that you configure the database instance prior to placing it production. Below are the special configurations, such as network connection and others that can affect database performance. Two main tools we will be using are SQL Server Management Studio and SQL server configuration manager.

 

• General server properties
  This can be viewed by choosing “properties” of the server from SQL management studio.

• Configure server connection properties
  SQL Management studio > rightclick on server > properties. Click on Connections in “SQL server properties ” dialog box. On Connections page, set desire remote server connections. You must restart the server for this to take effect.

• Affinity Mask Configuration
  OS such as Server 2003 move worker threads between two or more processors for multitask jobs. To allow use additional processors, SQL Management studio > rightclick on server > properties. Choose Processors. In “Enable Processors” , clear the “automatically set processor affinity mask for all processors” check box. This will allow you to configure CPU affinity.

• C2 Audit Tracing
  This mode allows the server to audit success and fail attempts to objects which can be used to investigate security breaches. To set the c2 audit mode, SQL Management studio > rightclick on server > properties. Check “enable c2 audit tracing” under security page.

• Enable or Disable Backup Compression
  To configure this option, SQL Management studio > rightclick on server > properties. Under Database settings > Backup and restore, check “compress backup”

• Cursor Threshold Option configuration
  Use this setting to state the number of row in the cursor set. The query optimizer guess that number of returned rows is greater than this threshold.  To configure this option, SQL Management studio > rightclick on server > properties. Click Advanced, change the Cursor threshold option under Miscellaneous section.

• Default Full-Text Language setting
  This dictate the default language for fulltext indexed columns. You may need to change this since this option is set to default language of the server. To change this vaule, To configure this option,  SQL Management studio > rightclick on server > properties. Click Advanced, configure “default full text language”

• Default Language configuration
  To configure this option, SQL Management studio > rightclick on server > properties. Click on Misc server settings. Choose desired language.

• Fixed Fill Factor Option
  This option allows admins to control how SQL 2012 make each page when creating a new index using current data. To configure this setting, go to SQL Management studio > Right-click on server > Properties. Choose Database settings, then set the default index fill factor.

• Locks option configuration
  Use this to specify the max number of available locks by limiting the amount of memory SQL 2012 uses. To configure this setting, go to SQL Management studio > Right-click on server > Properties. Click Advanced. Then configure locks option under parallelism.

• Minimum Query Memory Configuration
  This setting specifies the minimum amount of memory that will be set for execution of a query. To configure this option, go to SQL Management studio > Right-click on server > Properties. Click Memory > Minimum memory per query.

• Set Backup media Retention
  This function provides a default retention period for backups preventing backups from being overwritten. The length of time to retain backups does not need to be specified when configuring this setting. By default this is set to 0 days. To configure this setting, go to SQL Management studio > Right-click on server > Properties. Choose Database settings. In “backup/restore” section, configure the default backup media retention box.

• Allow Triggers to Fire Others Option
  This option controls nested triggers, whether if a trigger can launch another trigger to be initiated. Nested triggers can flow down up to 32 leaves. To configure this setting, go to SQL Management studio > Right-click on server > Properties. Click Advanced. Configure “Allow Triggers to Fire Others”.

• Boost SQL Server priority option
  This option allows you to control the process priority on Server 2008 and R2. Setting this option to True will change the priority base to 13. . To configure this setting, go to SQL Management studio > Right-click on server > properties. Choose “Processors.”.Under Threads section, check “Boost SQL Server priority.”

• Remote Server Access option
  This options allows stored procedures to be executed remotely from remote clients. To configure this setting, go to SQL Management studio > Right-click on server > Properties. Choose Connections, under “remote server connections” section, configure “allow remote connections to this server.”

• Remote Login Timeout option
  This option allows you to set the wait time before failed log in (due to network connectivity) attempts.  To configure this setting, go to SQL Management studio > Right-click on server > properties. Click Advanced. Configure “remote login timeout” under Network section.

• Network Packet Size option
  By default, SQL Server 2012 packet size is 4KB. This can be changed if needed or recommended by your network administrator. To configure this setting, go to SQL Management studio > Right-click on server > Properties. Click Advanced. Configure “Network Packet Size” under Network section.

• Max number of concurrent connections option
  By default, there is no limit on user connections. Often times, this can be a security risk depending on your policies. This option allows you to set the max number of simultaneous user connections to the SQL Server 2012. To configure this setting, go to SQL Management studio > Right-click on server > Properties. Click connections, set the “Max number of concurrent connections.”

• Remote Query time out option
  This allows admins to specify the time out of remote sql queries. The default is set to 10 minutes and this can be changed to fit your needs. To configure this setting, go to SQL Management studio > Right-click on server > Properties. Click connections, set the “Remote Query time out” option under remote server connections.