Brief about Protocols

TCP/IP - Widly accepted. You can connect from Unix machine also.
Shared Memory - When only Shared Memory is enabled, you can connect only from local machine. You cannot connect from remote machines.
Names Pipes - Microsoft Windows only.
VIA - Used for special hardware

To add more on this TCP/IP is a widely used protocol for Internet and is often faster then Named Pipes.Named pipe could work only in LAN (Local N/W) and often performs poorly in slow N/Ws.

Moreover, Kerberos Authentication Protocol could be integrated with TCP/IP only so it is better for security perspective also.

Write-Ahead Transaction Log

This topic describes the role of the write-ahead transaction log in recording data modifications to disk. For basic information about transaction logs, see Introduction to Transaction Logs.

SQL Server 2005 uses a write-ahead log (WAL), which guarantees that no data modifications are written to disk before the associated log record is written to disk. This maintains the ACID properties for a transaction. For more information about transactions and ACID properties, see Transactions (Database Engine).

To understand how the write-ahead log works, it is important for you to know how modified data is written to disk. SQL Server maintains a buffer cache into which it reads data pages when data must be retrieved. Data modifications are not made directly to disk, but are made to the copy of the page in the buffer cache. The modification is not written to disk until a checkpoint occurs in the database, or the modification must be written to disk so the buffer can be used to hold a new page. Writing a modified data page from the buffer cache to disk is called flushing the page. A page modified in the cache, but not yet written to disk, is called a dirty page.
At the time a modification is made to a page in the buffer, a log record is built in the log cache that records the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. If the dirty page is flushed before the log record is written, the dirty page creates a modification on the disk that cannot be rolled back if the server fails before the log record is written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written. Log records are written to disk when the transactions are committed.

Script To generate the script of Database Users, roles and object level permissions in SQL Server 2005

-- Script To generate the script of Database Users, roles and object level permissions in SQL Server 2005.

SET NOCOUNT ON

DECLARE     @OldUser varchar(100), @NewUser sysname

DECLARE a_cur CURSOR FOR

select name,name from sys.sysusers where uid>3 AND uid < 16384 ORDER BY

      1

Open a_cur

FETCH NEXT from a_cur into @OldUser,@NewUser

--SET @NewUser = @OldUser

WHILE (@@FETCH_STATUS = 0)

BEGIN

SELECT      'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'

SELECT      '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) AS '--Comment'

SELECT      'EXEC sp_addrolemember @rolename ='

      + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'

FROM  sys.database_role_members AS rm

WHERE USER_NAME(rm.member_principal_id) = @OldUser

ORDER BY rm.role_principal_id ASC

SELECT      CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

      + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)

      + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

      + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default

      + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'

FROM  sys.database_permissions AS perm

      INNER JOIN

      sys.objects AS obj

      ON perm.major_id = obj.[object_id]

      INNER JOIN

      sys.database_principals AS usr

      ON perm.grantee_principal_id = usr.principal_id

      LEFT JOIN

      sys.columns AS cl

      ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

WHERE usr.name = @OldUser

ORDER BY perm.permission_name ASC, perm.state_desc ASC

SELECT      CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

      + SPACE(1) + perm.permission_name + SPACE(1)

      + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default

      + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'

FROM  sys.database_permissions AS perm

      INNER JOIN

      sys.database_principals AS usr

      ON perm.grantee_principal_id = usr.principal_id

WHERE usr.name = @OldUser

AND   perm.major_id = 0

ORDER BY perm.permission_name ASC, perm.state_desc ASC

FETCH NEXT from a_cur into @OldUser,@NewUser

END

CLOSE a_cur

DEALLOCATE a_cur

List of All tables and Foreign Keys

SELECT         (CASE

         WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'

         ELSE 'DISABLED'

        END) AS STATUS,

         OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,

         OBJECT_NAME(FKEYID) AS TABLE_NAME,

        COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,

         OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,

         COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME

FROM SYSFOREIGNKEYS

ORDER BY  TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME,  KEYNO

GO

ATTACH DB &&& ATTACH SINGLEFILE TO DB

ATTACH DB:

Use master

Go

Sp_attach_db ‘pubs’,’E: \Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'’

EXEC sp_attach_db @dbname = N'pubs',

   @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',

   @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

ATTACH SINGLEFILE TO DB:

EXEC sp_detach_db @dbname = 'pubs'

EXEC sp_attach_single_file_db @dbname = 'pubs',

@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'

Fixing Orphaned Users

When ever you refresh a database to new location you need to fix the orphaned users on that database.

Below is the script for fixing orphaned users. If the login exists in that instance, below script will automatically fixes the orphaned users, otherwise will provide you the list of orphaned users information:

Below script need to be run on the refreshed database:

USE [Refreshed database name]

SET NOCOUNT ON

DECLARE @User sysname

DECLARE @NameFound bit

DECLARE orphan_cursor CURSOR FOR

-- The follow select statement was lifted from sp_change_users_login

-- under the area where the REPORT parameter is passed in and then

-- modified.  It's purpose is to identify users in the database that

-- do not match to a corresponding login by sid and then identify

-- whether or not there is a orresponding login by name

        select

                        name as UserName,

                        CASE WHEN suser_sid(name) is not null

                                    THEN 1

                                    ELSE 0

                       END as NameFound

            from sysusers

        where issqluser = 1

            and (sid is not null and sid <> 0x0)

            and suser_sname(sid) is null

        order by name

OPEN orphan_cursor

FETCH NEXT FROM orphan_cursor into @User, @NameFound

WHILE @@FETCH_STATUS = 0

BEGIN

            if @NameFound = 1

            begin

                        select 'Synchronizing user '+@User+' with login '+@User

                        exec sp_change_users_login 'UPDATE_ONE', @User, @User

            end

            else

            begin

                        select 'No matching login found for user '+@User

            end

            FETCH NEXT FROM orphan_cursor into @User, @NameFound

END

CLOSE orphan_cursor

DEALLOCATE orphan_cursor

Customized Update Stats on all active databases

Below is the script for Update Stats, which will extract the database information(Only active databases) and run the Update Stats one by one.

You can create a Stored Procedure in MSDB and schedule a job or dirctely you can run it.

declare @dbname sysname  --holds the name of the database
declare @stmt varchar(64)  --SQL Statement to be run
DECLARE @SQLVer int -- SQL Server primary version


set @dbname=@DB

IF @dbname is not NULL  
begin 
set @stmt='use '+'['+@dbname+']'+'exec sp_updatestats'
exec(@stmt)
return
end

SELECT @SQLVer = SUBSTRING(@@VERSION, CHARINDEX('-',@@VERSION)+1,2)
IF @SQLVer <> 9                      --Condition to check the version of SQL Server
begin
declare dbs cursor for 
select name from master..sysdatabases where 
status & (32 + 64 + 128 + 512 + 1024 + 2048 + 4096 + 32768) = 0  and name NOT IN ('master', 'msdb', 'model','tempdb')/*  To skip the databases which are
                                                                        32 = loading
                                                                        64 = pre recovery
                                                                        128 = recovering
                                                                        256 = not recovered
                                                                        512 = offline
                                                                        1024 = read only
                                                                        2048 = dbo use only
                                                                        4096 = single user
                                                                        32768 = emergency mode   */
end
else
begin
declare dbs cursor for 
select name from sys.databases where 
state  & (1 + 2 + 3 + 4 + 5  + 6 ) = 0 and is_read_only <>1 and   name NOT IN ('master', 'msdb', 'model','tempdb')  /*making use of the catalog view in SQL Server2005
                                                            and the state values indicate as below
                                                            Database state:
                                                            0 = ONLINE
                                                            1 = RESTORING
                                                            2 = RECOVERING
                                                            3 = RECOVERY_PENDING
                                                            4 = SUSPECT
                                                            5 = EMERGENCY
                                                            6 = OFFLINE            */

end

open dbs
fetch next from dbs into @dbname

while @@fetch_status=0
begin
print 'updatestats started on'+ '['+@dbname+']'
set @stmt='use '+'['+@dbname+']'+'exec sp_updatestats'
exec(@stmt)

fetch next from dbs into @dbname
end
close dbs
deallocate dbs

Customised CheckDB script for all databases.

Below is the script for Checkdb, which will extract the database information(Only active databases) and run the checkdb one by one.

You can create a Stored Procedure in MSDB and schedule a job or dirctely you can run it.

declare @dbname sysname  -- this variable holds the name of the database 

DECLARE @SQLVer int -- SQL Server primary version 

 

set @dbname=@DB   

   

IF @dbname is not NULL    

begin   

dbcc checkdb (@dbname)   

return   

end   

 

SELECT @SQLVer = SUBSTRING(@@VERSION, CHARINDEX('-',@@VERSION)+1,2) 

IF @SQLVer <> 9                --Condition to check the version of SQL Server  

begin 

declare dbs cursor for   

select name from master..sysdatabases where   

status & (32 + 64 + 128 + 512 + 1024 + 2048 + 4096 + 32768) = 0  /*  To skip the databases which are  

      32 = loading 

      64 = pre recovery 

      128 = recovering 

      256 = not recovered 

      512 = offline 

      1024 = read only 

      2048 = dbo use only 

      4096 = single user 

      32768 = emergency mode   */ 

 

end  

else 

begin  

declare dbs cursor for   

select name from sys.databases where   

state  & (1 + 2 + 3 + 4 + 5  + 6 ) = 0 and is_read_only <>1    /*making use of the catalog view in SQL Server2005 

     and the state values indicate as below 

     Database state: 

     0 = ONLINE  

     1 = RESTORING 

     2 = RECOVERING 

     3 = RECOVERY_PENDING 

     4 = SUSPECT 

     5 = EMERGENCY 

     6 = OFFLINE            */ 

  

end 

 

declare @str varchar(64) 

open dbs   

fetch next from dbs into @dbname   

   

while @@fetch_status=0   

begin   

set @str='dbcc checkdb (['+@dbname+'])' 

exec(@str) 

--dbcc checkdb (@dbname)   

fetch next from dbs into @dbname   

end   

close dbs   

deallocate dbs