Monday, November 7, 2011

Moving tempdb to a new location

The following example moves tempdb from its current location on the disk to another disk location. Because tempdb is re-created each time the MSSQLSERVER service is started, you do not have to physically move the data and log files. The files are created when the service is restarted in step 3. Until the service is restarted, tempdb continues to function in its existing location.

  1. Determine the logical file names of the tempdb database and their current location on disk.

  1: SELECT name, physical_name
  2: FROM sys.master_files
  3: WHERE database_id = DB_ID('tempdb');
  4: GO
  5: 
       2.   Change the location of each file by using ALTER DATABASE.



  1: USE master;
  2: GO
  3: ALTER DATABASE tempdb 
  4: MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
  5: GO
  6: ALTER DATABASE  tempdb 
  7: MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
  8: GO
  9: 


      3.   Stop and restart the instance of SQL Server.



      4.  Verify the file change.



  1: SELECT name, physical_name
  2: FROM sys.master_files
  3: WHERE database_id = DB_ID('tempdb');
 


     5.  Delete the tempdb.mdf and templog.ldf files from their original location

Tuesday, November 1, 2011

SQL Server Service Accounts

Service account for

Files and folders

Access

MSSQLServer

Instid\MSSQL\backup

D:\backups

Full control

 

Instid\MSSQL\binn

Read, Execute

 

Instid\MSSQL\data +

G:\MSSQL10.MSSQLSERVER\MSSQL\DATA + index folders

H:\MSSQL10.MSSQLSERVER\MSSQL\LOG\

(include Tempdb directory)

Full control

 

Instid\MSSQL\FTData

Full control

 

Instid\MSSQL\Install

Read, Execute

 

Instid\MSSQL\Log

Full control

 

Instid\MSSQL\Repldata

Full control

 

100\shared

Read, Execute

SQLServerAgent

Instid\MSSQL\binn

Full control

 

Instid\MSSQL\Log

Read, Write, Delete, Execute

 

100\com

Read, Execute

 

100\shared

Read, Execute

 

100\shared\Errordumps

Read, Write

 

ServerName\EventLog

Full control

FTS

Instid\MSSQL\FTData

Full control

 

Instid\MSSQL\FTRef

Read, Execute

 

100\shared

Read, Execute

 

100\shared\Errordumps

Read, Write

 

Instid\MSSQL\Install

Read, Execute

 

Instid\MSSQL\jobs

Read, Write

SQLServerReportServerUser

Instid\Reporting Services\Log Files

Read, Write, Delete

 

Instid\Reporting Services\ReportServer

Read, Execute

 

Instid\Reportingservices\Reportserver\global.asax

Full control

 

Instid\Reportingservices\Reportserver\Reportserver.config

Read

 

Instid\Reporting Services\reportManager

Read, Execute

 

Instid\Reporting Services\RSTempfiles

Read, Write, Execute, Delete

 

100\shared

Read, Execute

 

100\shared\Errordumps

Read, Write

SQL Server Browser

100\shared\ASConfig

Read

 

100\shared

Read, Execute

 

100\shared\Errordumps

Read, Write

SQLWriter

N/A (Runs as local system)

 

User

Instid\MSSQL\binn

Read, Execute

 

Instid\Reporting Services\ReportServer

Read, Execute, List Folder Contents

 

Instid\Reportingservices\Reportserver\global.asax

Read

 

Instid\Reporting Services\ReportManager

Read, Execute

 

Instid\Reporting Services\ReportManager\pages

Read

 

Instid\Reporting Services\ReportManager\Styles

Read

 

100\tools

Read, Execute

 

90\tools

Read, Execute

 

80\tools

Read, Execute

 

100\sdk

Read

 

Microsoft SQL Server\100\Setup Bootstrap

Read, Execute

 

+any physical folders the application access

 

 

** this needs the service restart

1. Need separate domain accounts with least priority for each service. Create one each for the following services

a. SQL Server

b. SQL Server agent

c. Reporting services

2. To change the service accounts, password, service startup type, or other properties of any SQL Server–related service, use SQL Server Configuration Manager. For reporting services, use the Reporting Services Configuration Tool.

 

** must grant SQL Service Account account the windows permission "Perform Volume maintenance Tasks"

What is Database Mirroring

In the proposed database mirroring solution there will be two groups of active-passive windows clusters deployed at each location (PRIMARY SITE and Secondary Site). The Primary Site will be the primary site and the Secondary Site will be the disaster recovery (DR) site.
The different failure scenarios are as follows:
- In case of a failure at the Primary Site, Node-A would automatically fail-over to Node-B on the Primary Site.
-In case of a Node-B failure, Node-C at Disaster Recovery site will need to be manually, brought online.
-In case of a Node-C failure, Node-C will automatically fail-over to Node-D.
Asynchronous database mirroring is to be implemented to replicate the data across these sites. Fail-over and fail-back is to be performed manually from PRIMARY SITE to Secondary Site.
Database Mirroring Overview
Database mirroring is a software technology built into Microsoft SQL Server providing high availability of application databases. Mirroring is implemented on a per database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring.
Database mirroring offers a substantive increase in availability over the level previously possible using Microsoft SQL Server and provides an easy-to-manage alternative or supplement to failover clustering or log shipping. Database mirroring works by maintaining a hot standby server. During a typical mirroring session, after a production server fails, client applications can recover quickly by reconnecting to the standby server.
Pros
- Database mirroring architecture is more robust and efficient than Database Log Shipping. It can be configured to replicate the changes synchronously to minimize data loss.
-Database mirroring has automatic server failover and client failover capabilities.
-Configuration is simpler than log shipping and replication, and has built-in network encryption support (AES algorithm).
-Because propagation can be done asynchronously it requires less bandwidth than synchronous method (e.g. host-based replication, clustering). Replication is not limited by geographical distance with current technology.
-Database mirroring supports full-text catalogs.
-Database mirroring does not require special hardware (such as shared storage, heart-beat connection) and cluster hardware, thus potentially has lower infrastructure cost.
Cons
-As the mirroring database is in a constantly restoring state a database snapshot is required to use the database for reporting.
-There is a risk in using automatic fail-over on a SQL Server cluster set-up, as in case of hardware failure on primary node there is a possibility of fail-over occurring to the mirrored database instead of secondary node.
-Additional SQL Server instances may be required for witnessing.