Tag: SQL Server

Best Practices for SQL Server in SharePoint

  • Use a dedicated server for SQL Server
  • Do not enable auto-create statistics on SharePoint content databases
  • Set max degree of parallelism (MAXDOP) to 1
  • Create DNS aliases that point to the IP address for all instances of SQL Server
  • NTFS Allocation Unit Size = 64 KB
    • format d: /FS:NTFS /Q /A:64K /Y
  • Collation = Latin1_General_CI_AS_KS_WS
  • Maximum Server Memory = 16 GB / 16,384 MB

Database Sizing

tempdb Initial Size Autogrowth
Rows Data 500 MB 500 MB
Log 125 MB 70 MB
  • Recovery model : Simple

 

model Initial Size Autogrowth
Rows Data 500 MB 500 MB
Log 125 MB 70 MB
  • Recovery model : Full
  • Auto Create Statistics : False
  • Auto Update Statistics : False

See Also

 

Create SQL Server Login

1. Local / Domain User (Windows Authentication)

USE master

EXEC sp_grantlogin '{Server Name}\{User Name}'

USE {Database Name}
GO

EXEC sp_grantdbaccess '{Server Name}\{User Name}'
GO

ALTER ROLE db_datareader ADD MEMBER [{Server Name}\{User Name}]
GO

ALTER ROLE db_datawriter ADD MEMBER [{Server Name}\{User Name}]
GO

2. IIS Application Pool (Windows Authentication)

USE master

EXEC sp_grantlogin 'IIS APPPOOL\{Application Pool Name}'

USE {Database Name}
GO

EXEC sp_grantdbaccess 'IIS APPPOOL\{Application Pool Name}'
GO

ALTER ROLE db_datareader ADD MEMBER [IIS APPPOOL\{Application Pool Name}]
GO

ALTER ROLE db_datawriter ADD MEMBER [IIS APPPOOL\{Application Pool Name}]
GO

3. SQL Server Login (SQL Authentication)

USE master

CREATE LOGIN {Login Name} WITH PASSWORD = 'Pass1234';

USE {Database Name}
GO

CREATE USER {Login Name} FOR LOGIN {Login Name};

ALTER ROLE db_datareader ADD MEMBER [{Login Name}]
GO

ALTER ROLE db_datawriter ADD MEMBER [{Login Name}]
GO

Install SQL Server 2008 R2 Standard

1. Double-click the SQL Server 2008 R2 installer

2. Click Installation > New Installation or add features to an existing installation
SQL Server Installation Center

3. Setup support rules , click OK
SQL Server 2008 R2 Setup - Setup Support Rules

4. Enter the product key, click Next
SQL Server 2008 R2 Setup - Product Key

5. Accept the license terms, click Next
SQL Server 2008 R2 Setup - License Terms

6. Install setup support files, click Install
SQL Server 2008 R2 Setup - Setup Support Files

7. Setup support rules, click Next
SQL Server 2008 R2 Setup - Setup Support Rules

8. Select feature role, click Next
SQL Server 2008 R2 Setup - Setup Role

9. Select “Database Engine Services” & “Management Tools – Complete” feature, click Next
SQL Server 2008 R2 Setup - Feature Selection

10. Setup installation rules, click Next
SQL Server 2008 R2 Setup - Installation Rules

11. Specify the name & instance ID, click Next
SQL Server 2008 R2 Setup - Instance Configuration

12. Review disk space requirements, click Next
SQL Server 2008 R2 Setup - Disk Space Requirements

13. Specify the service accounts, click Next
SQL Server 2008 R2 Setup - Server Configuration

14. Specify the authentication mode, switch to Data Directories tab
SQL Server 2008 R2 Setup - Database Engine Configuration - Account Provisioning

15. Specify the data directories, click Next
SQL Server 2008 R2 Setup - Database Engine Configuration - Data Directories

16. Click Next
SQL Server 2008 R2 Setup - Error Reporting

17. Installation configuration rules, click Next
SQL Server 2008 R2 Setup - Installation Configuration Rules

18. Ready to install, click Next
SQL Server 2008 R2 Setup - Ready to Install

19. Complete, click Close
SQL Server 2008 R2 Setup - Complete

Schedule Database Backup in SQL Server

1. In Microsoft SQL Server Management Studio, right-click SQL Server Agent, then click New, then click Job

2. Input Name, “JOB_BACKUP_DATABASE”

3. Click Steps, then click New

4. Input Step Name, “BACKUP DATABASE TO DISK”

5. Paste the following Command

DECLARE @todayText char(8)
SET @todayText = CONVERT(char(8), GETDATE(), 112)

DECLARE @path nvarchar(256)
SET @path = N'D:\DatabaseBackup\DB_NAME.{DATESTAMP}.bak'
SET @path = REPLACE(@path, '{DATESTAMP}', @todayText);

BACKUP DATABASE [DB_NAME]
    TO  DISK = @path
    WITH NOFORMAT, NOINIT,  NAME = N'DB_NAME - Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

6. Click OK

7. Click Schedules, then click New

8. Input Name, “Daily 18:00″

9. Setup the appropriate schedule

10. Click OK

11. Click OK again

Backup SQL Server Database using Script

DECLARE @todayText char(8)
SET @todayText = CONVERT(char(8), GETDATE(), 112)

DECLARE @path nvarchar(256)
SET @path = N'D:\DatabaseBackup\DB_NAME.{DATESTAMP}.bak'
SET @path = REPLACE(@path, '{DATESTAMP}', @todayText);

BACKUP DATABASE [DB_NAME] 
	TO  DISK = @path
	WITH NOFORMAT, NOINIT,  NAME = N'DB_NAME - Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Restore SQL Server Database

1. In SQL Server Management Studio, connect to the server as sa

2. Expand Databases

Microsoft SQL Server Management Studio
Microsoft SQL Server Management Studio

3. Right-click the database, point to Tasks, point to Restore, and then click Database…

Restore Database - General Page
Restore Database – General Page

4. In Restore Database dialog, verify the destination database name

5. On the General page, specify the source and location of backup sets to restore

Restore Database - Options Page
Restore Database – Options Page

6. On the Options page, select Overwrite the existing database (WITH REPLACE)

7. Click OK to start restore

Restore Completed
Restore Completed

References