Buffer Pool

The buffer pool contains and manages SQL Server’s data cache. Information on its contents can be found in the sys.dm_os_buffer_descriptors DMV.

For example, the following query will return the amount of data cache usage in MB per database:

SELECT count(*)*8/1024 AS 'Cached Size (MB)'
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS 'Database'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 'Cached Size (MB)' DESC



Monitoring SQL Server’s buffer pool is a great way to look out for memory pressure


MSSQL$:Memory Manager\Total Server Memory (KB): This indicates the current
size of the buffer pool.
MSSQL$:Memory Manager\Target Server Memory (KB): This indicates the ideal
size for the buffer pool.
MSSQL$:Buffer Manager\Page Life Expectancy: This is the amount of time, in
seconds, that SQL Server expects a page that has been loaded into the buffer pool to remain in cache.

open the firewall port for SQL Server on Windows Server 2008/R2

How to: Configure a Windows Firewall for Integration Services

http://msdn.microsoft.com/en-us/library/ms141198.aspx

How do I open the firewall port for SQL Server on Windows Server 2008?

http://support.microsoft.com/kb/968872

SharePoint 2010 All-in-One For Dummies

SharePoint 2010 All-in-One For Dummies

Seven tiers of disaster recovery

1 Tier 0: No off-site data – Possibly no recovery
2 Tier 1: Data backup with no hot site
3 Tier 2: Data backup with a hot site
4 Tier 3: Electronic vaulting
5 Tier 4: Point-in-time copies
6 Tier 5: Transaction integrity
7 Tier 6: Zero or near-Zero data loss
8 Tier 7: Highly automated, business integrated solution

http://en.wikipedia.org/wiki/Seven_tiers_of_disaster_recovery

user's permissions in SQL Server 2005

Example - list current login user's permissions

select object_name(major_id) as object_name,permission_name,state_desc
from sys.database_permissions
where major_id >0 and grantee_principal_id !=0

Example - SQL Server Instance Rights

USE AdventureWorks;
SELECT *
FROM fn_my_permissions(NULL, 'SERVER');
GO

Example - Database Rights

USE AdventureWorks;
SELECT *
FROM fn_my_permissions('AdventureWorks', 'DATABASE');
GO

Example - Table Rights

USE AdventureWorks;
SELECT *
FROM fn_my_permissions('HumanResources.Employee', 'OBJECT')
ORDER BY subentity_name, permission_name ;
GO

Reporting Services Scripter for SQL Server 2000/2005/2008

http://www.sqldbatips.com/showarticle.asp?ID=62

Limit Concurrent Database Connections by SQL Server User Account

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [connection_limit_trigger] ON ALL SERVER
WITH EXECUTE AS 'tfs4dba' FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'tfs4dba' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'tfs4dba') > 3
ROLLBACK; END;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [connection_limit_trigger] ON ALL SERVER