You work for a manufacturing company that relies heavily on

You work for a manufacturing company that relies heavily on SQL Server. There is a new application that is about to be deployed that uses SQL Server. Discuss the performance and redundancy options you will need to consider for the physical file architecture, and list the key database configurations settings.

Solution

Solution:

SQL SERVER is a Microsoft product used to manage and store information. Technically, SQL Server is a “relational database management system” (RDMS).Broken apart, this term means two things. First, that data stored inside SQL Server will be housed in a “relational database”, and second, that SQL Server is an entire “management system”, not just a database. SQL itself stands for Structured Query Language. This is the language used to manage and administer the database server.

Relational Database:

RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.

RDBMSs have been a common choice for the storage of information in new databases used for financial records, manufacturing and logistical information, personnel data, and other applications . Relational databases have often replaced legacyhierarchical databases and network databases because they are easier to understand and use.

plan to configure SQL Server for SharePoint Server 2013

The following is general guidance for when to deploy an additional server that will run a SQL Server instance:

Separate and prioritize your data among disks

Add an additional database server when you have more than four web servers that are running at capacity.

Add an additional database server when your current server has reached its effective resource limits of RAM, CPU, disk IO throughput, disk capacity, or network throughput.

Ideally, you should place the tempdb database, content databases, Usage database, search databases, and SQL Server 2008 R2 with SP1 and SQL Server 2012 transaction logs on separate physical hard disks.

The following list provides some best practices and recommendations for prioritizing data:

When you prioritize data among faster disks, use the following ranking:

Testing and customer data show that SharePoint Server 2013 farm performance can be significantly impeded by insufficient disk I/O for tempdb. To avoid this issue, allocate dedicated disks for tempdb. If a high workload is projected or monitored — that is, the average read action or the average write action requires more than 20 ms — you might have to ease the bottleneck by either separating the files across disks or by replacing the disks with faster disks.

For best performance, place the tempdb on a RAID 10 array. The number of tempdb data files should equal the number of core CPUs, and the tempdb data files should be set at an equal size. Count dual core processors as two CPUs for this purpose. Count each processor that supports hyper-threading as a single CPU.

Separate database data and transaction log files across different disks. If files must share disks because the files are too small to warrant a whole disk or stripe, or you have a shortage of disk space, put files that have different usage patterns on the same disk to minimize concurrent access requests.

Tempdb data files and transaction logs

Database transaction log files

Search databases, except for the Search administration database

Database data files

Validate and monitor storage and SQL Server performance:

Test that your performance and backup solution on your hardware enables you to meet your service level agreements (SLAs). In particular, test the I/O subsystem of the computer that is running SQL Server to make sure that performance is satisfactory

Test the backup solution that you are using to make sure that it can back up the system within the available maintenance window. If the backup solution can\'t meet the SLAs your business requires, consider using an incremental backup solution such as System Center 2012 - Data Protection Manager (DPM) with Service Pack 1 (SP1).

It is important to track the following resource components of a server that is running SQL Server: CPU, memory, cache/hit ratio, and I/O subsystem. When one or more of the components seems slow or overburdened, analyze the appropriate strategy based on the current and projected workload. For more information, see Monitoring and Tuning for Performance for SQL Server 2008 R2 with SP1 and Monitor and Tune for Performance for SQL Server 2012.

Databases   This object provides counters to monitor bulk copy operations, backup and restore throughput, and transaction log activities. Monitor transactions and the transaction log to determine how much user activity is occurring in the database and how full the transaction log is becoming. The amount of user activity can determine the performance of the database and affect log size, locking, and replication. Monitoring low-level log activity to gauge user activity and resource usage can help you identify performance bottlenecks. Consider monitoring the following counter:

Transactions/sec   This counter shows the number of transactions on a given database or on the entire server per second. This number is more for your baseline and to help you troubleshoot issues.

Locks   This object provides information about SQL Server locks on individual resource types. Consider monitoring the following counters:

content databases   RAM recommended computerSQL Server

Average Wait Time (ms)   This counter shows the average amount of wait time for each lock request that resulted in a wait.

Lock Wait Time (ms)   This counter shows the wait time for locks in the last second.

Lock waits/sec   This counter shows the number of locks per second that couldn\'t be satisfied immediately and had to wait for resources.

Number of deadlocks/sec   This counter shows the number of deadlocks on the computer that is running SQL Server per second. This should not increase above 0.

Minimum for small production deployments 8 GB

Minimum for medium production deployments 16 GB

Recommendation for up to 2 terabytes 32 GB

Recommendation for the range of 2 terabytes to 5 terabytes 64 GB

Recommendation for more than 5 terabytes Additional RAM over 64 GB can improve SQL Server caching speed