Deploying SQL Server 2005 with SAN #1

Microsoft  recommendation is to use RAID 10 both for logs and data/index files. When cost is an issue you could allocate data and index files on RAID 5 or equivalent; however, keep logs on RAID 10.


RAID 10 offers better availability than RAID 5 and better performance especially for write-intensive applications. Some of our customers have reported up to 50% adverse impact on write intensive work load when moving to RAID 5. The impact on performance of RAID 5 is dependent on the hardware vendor’s implementation of RAID 5 and the characteristics of your particular work load. You should not assume that you will get a 50% adverse impact – it could be higher or lower.

http://blogs.msdn.com/b/sqlcat/archive/2005/10/11/479887.aspx

SQL Server Error Logs

View SQL Server Error Logs in Query
EXEC xp_readerrorlog   -- (current error log file)
EXEC xp_readerrorlog 3 -- (error log file number #3)

You can re-cycle the error log by executing the DBCC ERRORLOG command (or) the sp_cycle_errorlog system procedure.

How to Enable the Lock Pages in Memory Option on Windows Server

The Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE). This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Locking pages in memory is not normally required on 64-bit operating systems. You will need to enable this right on 64-bit operating systems only when using Large Page Memory support or to configure SQL Server such that the Buffer Pool memory does not get paged out. Use the Windows Group Policy tool (gpedit.msc) to enable this policy for the account used by SQL Server 2005 Database Engine. You must be a system administrator to change this policy.


To enable the lock pages in memory option

--------------------------------------------------------------------------------
1.On the Start menu, click Run. In the Open box, type gpedit.msc.
The Group Policy dialog box opens.
2.On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
3.Expand Security Settings, and then expand Local Policies.
4.Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
5.In the pane, double-click Lock pages in memory.
6.In the Local Security Policy Setting dialog box, click Add.
7.In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.

Login failed for user 'DomainName\SQLAdmin'. (.Net SqlClient Data Provider)

Login failed for user 'DomainName\SQLAdmin'. (.Net SqlClient Data Provider)


Go to Start, Programs, SQL Management Studio -- Right Click on that and say Run as Administrator. Now pass your credentials.
This will allow to you open SSMS for your login(SQLAdmin).

But if you need to fix permanently The user SQLAdmin must be part of Local administrator group of the server and should have full rights to access SQL server installation path like C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER

Find the Database is set for snapshot_isolation

SELECT name,snapshot_isolation_state,
snapshot_isolation_state_desc AS Descriptions FROM sys.databases

Check the Merge Replication Generation by date wise

select convert(varchar(12),coldate)as Dates , count(*) as generation
from MSmerge_genhistory (nolock)
where year(convert(varchar(12),coldate))= '2010'
group by convert(varchar(12),coldate)
order by convert(varchar(12),coldate)

Monitor Replication in SQL 2005

The following list of sps really useful to get more information from distribution database to monitor replication.

use distribution

go
select * from MSPublisher_databases
Sp_browsereplcmds
sp_MSenum_snapshot
sp_MSenum_logreader
sp_MSenum_distribution
sp_MSenum_qreader

Check Merge Replication Confilicts 2005

CREATE PROCEDURE [dbo].[DBA_MonitorMergeConflicts]
AS
/****************************************************
Purpose : Check Merge Replication Confilicts and send
an email notification to DBA
*****************************************************/
SET NOCOUNT ON
Declare @iCnt int
Select @iCnt = count(*) from NorthWind.dbo.MSmerge_conflicts_info
--Print @iCnt


If @iCnt > 0
Begin
 EXEC msdb.dbo.sp_notify_operator
 @profile_name = N'DBAMail',
 @name = N'dba',
 @subject = N'Merge Conflicts Notification',
 @body = N'There are Merge Replication Conflicts Please check the  View Conflicts to Resolve' ;
End

Find SQL Server Information

sp_server_info

Find Linked Server Details

sp_helpserver

Transfer Backup File to another server do not use xp_cmdshell

By default in SQL Server 2005 and 2008 xp_cmdshell is disabled for security reason.

Instead of using xp_cmdshell you can create batch file to transfer bak file copy to another server

The TransferBackupFile contains the following line of command




Copy E:\mssql\backup\mydb.bak \\ServerName\E$\MSSQL\Backup  /y

The merge process was unable to deliver the snapshot to the Subscriber

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData

Solution : Change default snapshot folder settings to full qualified network name
\\SRVSQL001\C$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData

Remove Distribution Database

-- In SQL 2005
exec sp_dropdistributor @no_checks =1, @ignore_distributor = 1;

go

Find Replicated Database

--SQL 2005/2008
sp_helpreplicationdboption

--SQL 2000
select name, databasepropertyex (name,'IsMergePublished') from
master..sysdatabases
select name, databasepropertyex (name,'IsPublished') from
master..sysdatabases