Instance Level Settings [SQL SERVER 2012]

Instance Level Settings

Instance-level settings is also known as SQL Server options include memory use, fill factor, processor, I/O Affinity etc.SQL Server  settings apply only for one instance without affecting other instances on the same host computer.

                   Instance-level settings can be configured through the Server Properties dialog box. The Server Properties dialog box contains the following pages:

General

View general information about the instance, including the host operating system, root directory, and server collation. You can’t modify settings on this page.

Memory 

Configure instance memory options, including minimum, maximum, index creation memory, and minimum memory per query.

Processors

Configure processor affinity and I/O affinity, limit worker threads, boost SQL Server priority, and use Windows fibers (lightweight pooling).

Security 

Configure server authentication, log on auditing, server proxy account, Common Criteria compliance, C2 audit tracing, and cross-database ownership Chain

Connections

Configure maximum connections; query governor and default connection options, whether to allow remote connections, and whether to require distributed transactions for server-to-server communication.

Database Settings

Configure default index fill factor, default backup media retention, recovery filter and database default locations.

Advanced 

Configure FILESTREAM options; enable contained databases; and trigger firing, two-digit year cutoff, network packet size, remote logon timeout, and parallelism options.

Permissions  
Configure instance-level permissions.

Configuring Memory Allocation

         By default, a SQL Server 2012 instance uses memory dynamically, querying the host operating system on a periodic basis to determine how much free memory is available and releasing memory back to the host as needed to avoid paging.

       
Instance-Level Settings of Memory:

Min Server Memory  
Max Server Memory 
 

Lowest Min Memory for SQL Server 2012
32-bit = 64 MB
64-bit = 128 MB

Default Settings

Min memory  =  0 MB

Max memory =  2,147,483,647 MB

Use sp_configure when  advanced options are enabled to configure maximum

and minimum server memory. 


Configuring Instance to use Min = 1024 MB , Max = 8,096 MB

EXEC sys.sp_configure 'show advanced options', 1;

GO

RECONFIGURE; GO

EXEC sys.sp_configure 'min server memory', 1024;

GO

EXEC sys.sp_configure 'max server memory', 4096;

GO

RECONFIGURE;

GO

REFERENCES:

Training Kit(Exam 70-462): Administering Microsoft SQL Server 2012 Databases

Memory Allocation on MSDN

 

Configuring Additional SQL Server Components

Configuring Additional SQL Server Components

Deploying and Configuring Analysis Services

When installing Analysis  Services, you can choose whether to install it in

         multidimensional mode and data mining mode

                          OR

              tabular mode

 Mode is specific to an instance, and if you want to use more than one mode, it is necessary to install more than one Analysis Services instance.

DIFFERENCES BETWEEN THESE MODES

 The difference between these modes is as follows:

 Multidimensional and data mining mode

 The default Analysis Services modeSupports online analytical processing (OLAP) databases and data mining models.

Tabular mode

                  Supports new tabular modeling features. When installed using this mode, Analysis Services can host solutions built in the tabular model designer.

                           Analysis Services in tabular mode is necessary when you want tabular model data access over a network.

                          You can install Analysis Services from the command line by using the

 /FEATURES=AS option.  

      The /ASSERVERMODE can be set to MULTIDIMENSIONAL, TABULAR, or POWERPIVOT.

      For example, to create an instance named ASMulti with Analysis Services installed in multidimensional

and data mining mode and configuring the Analysis Services service account as

       contoso\asaccount, and with contoso\kim_akers as the Analysis Services Administrator

account, use the following command:

Setup.exe /q /IAcceptSQLServerLicenseTerms /Action=install /Features=AS

/ASSERVERMODE=MULTIDIMENSIONAL /INSTANCENAME=ASMulti /ASSVCACCOUNT=NetworkService

/ASSYSADMINACCOUNTS=contoso\kim_akers

                 To create an instance named ASTabular with Analysis Services installed in tabular mode,

with the Analysis Services service account as NetworkService, and with contoso\kim_akers as

the Analysis Services Administrator account, use the following command:

Setup.exe /q /IAcceptSQLServerLicenseTerms /Action=install /Features=AS

/ASSERVERMODE=TABULAR /INSTANCENAME=ASTabular /ASSVCACCOUNT=NetworkService

/ASSYSADMINACCOUNTS=contoso\kim_akersYSADMINACCOUNTS=contoso\kim_aker

Analysis Services uses a managed service account when installed by default. You can also

configure Analysis Services to use a domain or local user account:

1. If Analysis Services will connect to network resources in the security context of the

login account, create a specific domain user account for use with Analysis Services.

You can also use the Network Service account. When you use this account, the account

presents the local computer’s credentials to remote servers. To grant access to this

account, use the Computer account of the Analysis Server host.

2.If Analysis Services will not connect to external network resources, Analysis Services

can be run using a local user account, a domain user account, a virtual account, or a

managed service account.

  

Deploying and Configuring Reporting Services

          To install a SQL Server Reporting Services (SSRS) Native Mode Report Server–only instance by

using SQL Server Installation Center, perform the following general steps:

1. Open SQL Server Installation Center from the Configuration Tools folder.

2. Click Installation and then choose New SQL Server Stand-Alone Installation Or Add

Features To An Existing Installation. Specify the location of the SQL Server 2012 installation

files.

3. Click OK after the Setup Support Rules check runs.

4. Click Next on the Product Updates page.

5. Click Next on the Setup Support Rule page.

6. On the Installation Type page, choose Perform A New Installation Of SQL Server 2012.

7. On the Product Key page, enter the product key.

8. On the License Terms page, choose I Accept The License Terms.

9. On the Setup Role page, choose SQL Server Feature Installation.

10. On the Feature Selection page, choose Reporting Services - Native And Database

Engine Services  and then click .

11.On the Installation Rules page, click Next.

12. On the Instance Configuration page, provide a name for the Reporting Services

instance.

13. On the Disk Space Requirements page, click Next.

14. On the Service Accounts page, review the Service Account configuration and then click

Next.

15. On the Database Engine Configuration page, add the users who will hold the SQL

Server Administrative role and then click Next.

16. On the Reporting Services Configuration page, shown in Figure 3-3, choose Install And

Configure. You’ll have this option only if you have already installed the necessary Web

Server components.

17. On the Error Reporting page, click Next twice and then choose Install. Click Close to

dismiss the Setup Wizard.