SysComments v/s sys.sql_modules - Returns different result for same query

In SQL 2000 we used to query system table SysComments to get the SQL Definition of Objects like SP,Views,Trigger,Functions etc. It seems that the counterpart in SQL 2005 is sys.sql_modules. But when i ran same query for SysComments and sys.sql_modules, the rows affected was different(No. of Rows). Out of curiosity, i thought it need a postmortem. When i ran the following query in my R&D database , the SysComments returned 138 rows and sys.sql_modules returned 84 rows.

SELECT obj.name , mod.definition,obj.object_id,len(mod.definition) FROM sys.sql_modules mod INNER JOIN sys.objects obj ON mod.object_id = obj.object_id WHERE obj.type = 'p'

SELECT o.name, m.Text ,o.Object_ID, m.id,len(m.Text) FROM syscomments m INNER JOIN sys.objects o ON m.id = o.object_id WHERE o.type = 'p'

You know why this different result for same query; it because in SQL Server we have new Data Type called Nchar/nvarchar (Max) etc. So in SQL 2005 the SQL Definition is stored in nvarchar(Max) data type where as in SQL Server 2000 it is nvarchar(4000). So you will have different result when you have a large stored procedure definition.

Maximum Number of Instances by Edition in a Clustered and Nonclustered Configuration

SQL Server Editions Maximum Instances Allowed


Enterprise, Developer, -50
and Evaluation Editions

Enterprise, Developer, - 25
Evaluation Editions in a clustered configuration

Standard, Workgroup, - 16
and Express Editions

Standard, Workgroup, - 16
and Express Editions in a clustered configuration

SQL Server Instance-Aware Components SQL Server Component

SQL Server Component - Instance-aware?
--------------------------------------------------------------------------

SQL Server Database Services -Yes
(includes SQL Server Agent, Full-Text Search, and Replication)

SQL Server Analysis Services -Yes

SQL Server Reporting Services - Yes

SQL Server Integration Services - No

Notification Services -Yes
(instances are created
after installation; not during setup. )

Workstation components and tools - No

Export/Import Surface area Configuration

In larger environment you may need to have same Surface Area Configuration on many instance of SQL Server. In SQL Server 2005 , SAC command line utility make it very easier for you. To change the SAC setting using GUI is always time consuming and tedious. SAC Command line utility allow you to export and import surface area configuration to and from a file.
Here you go….

Suppose you have a master server and in that master you have configured the surface area as per the company security policy. Now you have another 50 instances to which you want to apply same configuration.

(a) Go to command prompt of Master server and change directory to “C:\Program Files\Microsoft SQL Server\90\Shared”
(b) Now you will have C:\Program Files\Microsoft SQL Server\90\Shared> prompt
(c) Give the following command
sac out C:\sac_MasterSetting.xml S MasterComputerName

Now you will have a xml file in C:\ with all you master server surface area configuration setting. Next step is to apply this to other instances

From the same directory, you can give the following command

sac in C:\sac_ MasterSetting.xml S Computer_1
sac in C:\sac_ MasterSetting.xml S Computer_2
sac in C:\sac_ MasterSetting.xml S Computer_3
……
sac in sac_ MasterSetting.xml S Computer_50

or what you can do is create a batch file and keep it in VSS for better management

Connect to Remote SQL Server Configuration Manager

From Start --> SQL Server 2005 --> SQL Server Configuration Manager you can only connect to Local SQL Server instances. To connect to Remote instance’s SQL Server Configuration Manager there are two methods :-
(a) From SQL Server Management Studio

1. Open SQL Server Management Studio.
2. Click on Cancel if the Connect to Server dialog pops up.
3. In Management Studio, click on the View menu and select Registered Servers (if it is not visible)
4. In the Registered Servers window, right-click on Database Engine, select New and then Server Registration. This will launch the New Server Registration dialog.
5. In the New Server Registration dialog, enter the name of the remote computer in the Server name field, click on Test and then Save. If the Database Engine on the remote computer requires SQL Server authentication, you have to change Authentication to SQL Server Authentication to enter the username and password.
6. Back in the Registered Servers window in SQL Server Management Studio, right-click on the remote computer and select SQL Server Configuration Manager to configure the remote computer


(b) From MyComputer

1. Right button on “My computer” and select Manage and you will get Computer Management Window
2. Right click on Computer Management and select Connect to another computer.
3. You will have a "Select Computer Window"
4. Give the Remote Computer Name and press OK
5. Now, you will have the remote computer's Computer Management Window
6. Click on Services and Applications7. Select SQL Server Configuration Manager

Difference in SQL Server Profiler of 2005 and 2000

The main enhancement in SQL 2005 is integration with Analysis Server. SQL Profiler 2005 allows users to look at current activity happening inside Analysis Server.

Performance Counter Value from System Table

To see the current performance counter value
Select *from sys.sysperfinfo

Error Severity Level Ranges in SQL Server

Severity level Description

0-10 Indicate informational messages and non-severe errors
11-16 Indicate errors that can be corrected by the user
17-19 Indicate software errors that cannot be corrected by the user
20-25 Indicate system problems and fatal errors

Database Engine Error Severities
http://msdn.microsoft.com/en-us/library/ms164086.aspx

SQL Server DMO and SMO

SQL Server 2000 Enterprise Manager uses SQL-DMO COM-Based API to manage SQL Server.
SQL Server 2005 replaces SQL-DMO with .NET based object libraries
(a) SQL Server Management Objects (SMO)
(b) Replication Management Objects (RMO).
You can't use SMO on VB6 or .NET 1.1 (2003). it is only available for .NET 2.0 and later

FAQ : How to Parameterize TOP clause in SQL Server 2000

CREATE PROCEDURE spTopRecords
@Top int
as
-- set how many records to return
SET ROWCOUNT @Top
SELECT * FROM
SET ROWCOUNT 0

Note : In SQL Server 2005 top can be used with varaiable

declare @i int
set @i=10
Select top(@i) * from sysobjects

Configure SQL 2005 for Remote Access

Configure SQL 2005 for remote access in a Windows Firewall enabled Box

While connecting to a SQL Server instance installed in a Windows Firewall enabled box using web browser we may get an error saying “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.” The cause of this error is that the web browser is not able to interact with the server because the port is blocked by the Firewall. It is also to be noted that the behavior differ from Named Instance to Default Instance. Generally, when we debug this error there are few steps to be followed :-

For Default Instance :-

(a) Make sure that the Database Engine is enabled for Local & Remote Connection and Using Both TCP/IP and Named Pipes (using Surface Area Configuration (SAC) Tool)
(b) Create an exception for SQL Server 2005 in Windows Firewall
loadTOCNode(3, 'moreinformation');
To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:
(i) Right click on Local Area Network in Network Connection and Select Properties
(ii) Select ‘Advanced’ Tab
(iii) Select ‘Setting’
(iv) In Windows Firewall, click the Exceptions tab, and then click Add Port
(v) Give your Instance Name
(vi) Give the SQL Server Default Instance Port No (for eg. 1433 )
(vii) Select TCP
(viii) Click OK
(You can do this for the SQL Service instead of mentioning the port number, by using Add Program in the Exception Tab)
For Named Instance
When we use named instance generally the port number will be different from 1433(as 1433 is the default port for SQL Server). For named instance, you have to mention the port number in the connection string. If you are running SQL Server 2005 by using an instance name and you are not using a specific TCP/IP port number in your connection string, you must enable the SQL Server Browser service to allow for remote connections. Then you have to follow the above mentioned step for the corresponding ports. Apart from that you should make sure that the SQL Browser Service is running and the UDP Port 1434 is added in the exception. Steps are as follows
(a) Make sure that the Database Engine is enabled for Local & Remote Connection and Using Both TCP/IP and Named Pipes (using Surface Area Configuration (SAC) Tool)
(b) Create an exception for SQL Server 2005 in Windows Firewall
loadTOCNode(3, 'moreinformation');
To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:
(i) Right click on Local Area Network in Network Connection and Select Properties
(ii) Select ‘Advanced’ Tab
(iii) Select ‘Setting’
(iv) In Windows Firewall, click the Exceptions tab, and then click Add Port
(v) Give your Instance Name
(vii) Give the SQL Server Named Instance Port No (for eg. 1433 )
(vii) Select TCP
(viii) Click OK

(c) Now, you should add an exemption for UDP port 1434
(i) In Windows Firewall, click the Exceptions tab, and then click Add Port
(ii) Give your Instance Name
(ii) Give the Port No as 1434
(iii) Select UDP
(iv) Click OK
(d) Go to the SQL Server Configuration Manager and Make sure that the SQL Browser is running

Implementation and usage of DDL Trigger and its saga in SQL Server 2005

A new feature was introduced in SQL Server 2005 called DDL Trigger, which fires in response to a variety of Server scope or Database scope, Data Definition Language (DDL) events. These events can generally be fired by any DDL statements like Create, Alter, Drop, or DCL (Data Control Language) like Grant, Deny or Revoke.

DDL trigger can be created at two levels:•

Database level (like Create, Alter, Drop table, Procedure, Function or any database scoped objects)
• Server Level trigger (like Create, Alter, Drop Databases/Login/ Certificate )
Problem
In the earlier versions, tracking DDL activities on a database or on a server was not straight forward. Running profiler or trace was the only solution. Consider, you have a group of developers responsible for all the Development activities like Creation or Modification of Stored Procedures. Typically, this group is added to DB_DDLAdmin database fixed role. Here, to track which developer has modified a particular stored procedure was not possible, since all users have the DDLAdmin privilege. To resolve these type of issues DDL trigger is introduced in SQL Server 2005.

DDL Triggers is useful for the following purpose:• Auditing
• Regulating database or server operation
• Securing the database or server from malpractice
• Issue an alert when a DDL event occurs in Database or Server

Permission
To create a DDL trigger with a server scope (ON ALL SERVER) requires CONTROL SERVER permissions on the server. To create a DDL trigger within the database scope (ON DATABASE) requires ALTER ANY DATABASE DDL TRIGGER permission in the current database.

DDL Event Group

The Figure 1 lists the DDL event groups that can be used to run a DDL trigger or an event notification and also the Transact-SQL statements they cover. You can use a top level Event group instead of mentioning all the events in the trigger. For example, using DDL_TABLE_VIEW_EVENTS event group instead of mentioning specified events like Create_Table,Alter_Table,Drop_Table,Create_view, Alter_View,Drop_View

Figure 1





How DDL Trigger works?

EventData()
EVENTDATA function returns XML data which contains the information about server or database events. EVENTDATA is called when an event notification fires and it can also be used inside the body of a DDL or logon trigger.

Database scoped DDL Trigger.

Any DDL statement, which alters the schema of an object inside the database, will be tracked in this type of trigger. (Please note that few events are not tracked (read as bug) and this will be fixed in SQL Server 2008 version). Generally, the DDL Statement at the database level are Create, Alter, Drop tables , Procedure ,Views ,Functions , Users, Role , Certificate, etc. (For all the database level events , please refer to http://msdn.microsoft.com/en-us/library/ms189871.aspx ). Either you can hard code the specific event to be tracked inside the trigger (like Create_Table,Create_Procedure etc) or use DDL_DATABASE_LEVEL_EVENTS event group to track all the database scoped events. Whether you need to track all the events or only specific events will depend upon the requirements. Also there are event groups like DDL_TRIGGER_EVENTS, DDL_FUNCTION_EVENTS, DDL_SYNONYM_EVENTS, DDL_SSB_EVENTS, DDL_DATABASE_SECURITY_EVENTS, DDL_EVENT_NOTIFICATION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS, DDL_TYPE_EVENTS, DDL_XML_SCHEMA_COLLECTION_EVENTS, DDL_PARTITION_EVENTS, DDL_ASSEMBLY_EVENTS which are also used in EventNotification. Mostly all the events are applicable for DDL Triggers as well.

Step by step in Brief

(a) Create a table to store the logged data. Here in this scenario the table created is DDLDatabaseScopedAuditTrail.
(b) Create DDL Trigger which logs the information into this table. Which event needs to be tracked will depend upon the requirements. EventData() function returns a whole lot of information. In this example, let us track Eventtype,Login,Database User name and TSQL which caused the trigger to fire and the fired Datetime. For a better understanding the output of EventData() function to an XML type column has been captured However, this may not be needed in the actual senario. Select only the necessary events as logging any extra information is always a performance hinderance.
This scenario, dipicts tacking three kind of events:
• Trigger to track all events .
• Trigger to track a group of events
• Trigger to track a specific event

Code Block for Database scoped DDL Trigger.

-- Database for DDL Trigger Demo
Create Database DDLTriggerTest
Go

-- Create Log table

If Object_ID('DDLAuditTrail') is not null
BEGIN
DROP TABLE DDLAuditTrail
END

CREATE TABLE dbo.DDLDatabaseScopedAuditTrail
(RowNum int identity,
EventType nvarchar(100) ,
AttemptedDate datetime NOT NULL DEFAULT GETDATE(),
ServerLogin nvarchar(100) NOT NULL,
DBUser nvarchar(100) NOT NULL,
TSQLText varchar(max) ,
EventData xml NOT NULL
)
Go

--Trigger to track all Database Events using EventGroup

Create TRIGGER db_DDLtrg_Audit_All_Database_Scoped_Events
ON DATABASE
FOR
DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON

If Object_ID('DDLDatabaseScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()
INSERT dbo. DDLDatabaseScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)
END
Go


-- Trigger to track specific group of Database events using Database Event Group

Create TRIGGER db_DDLtrg_Audit_All_TABLE_VIEW_EVENTS
ON DATABASE
FOR
DDL_TABLE_VIEW_EVENTS
AS
SET NOCOUNT ON

If Object_ID('DDLDatabaseScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT dbo.DDLDatabaseScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)
END
Go


-- Trigger to track very specific Database events


Create TRIGGER db_DDLtrg_Audit_Specific_Event
ON DATABASE
FOR
CREATE_Table, Create_Procedure
AS
SET NOCOUNT ON
If Object_ID('DDLDatabaseScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT dbo.DDLDatabaseScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)
END
Go

DDL Trigger for Server Scoped Objects Event tracking

Server scoped events can be tracked using a server scoped DDL trigger. Here also , you can track all the events using DDL_SERVER_LEVEL_EVENTS group event or you can be more specific tracking only CREATE_DATABASE and CREATE_LOGIN.

Steps in Breif :-(a) Create a table to store the logged data. Here the table created is DDLServerScopedAuditTrail. It is better to create this table in the Master or in any specific user database other than the business related database..
(b) Create DDL Trigger which logs the information into this table. Events depend upon the requirements. Here three types of events have been detailed.


DDL Trigger to track Server Scoped events


Use Master
GO
If Object_ID('DDLServerScopedAuditTrail') is not null
BEGIN
DROP TABLE DDLServerScopedAuditTrail
END

CREATE TABLE dbo.DDLServerScopedAuditTrail
(RowNum int identity,
EventType nvarchar(100) ,
AttemptedDate datetime NOT NULL DEFAULT GETDATE(),
ServerLogin nvarchar(100) NOT NULL,
DBUser nvarchar(100) NOT NULL,
TSQLText varchar(max) ,
EventData xml NOT NULL
)

Go

--Trigger to track all Server Events using EventGroup


CREATE TRIGGER Svr_DDLtrg_Audit_All_Server_events
ON All Server
FOR
DDL_SERVER_LEVEL_EVENTS

AS
SET NOCOUNT ON
If Object_ID('DDLServerScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT
dbo. DDLServerScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)


End
Go


Trigger to track specific group of Server events using Server Event Group

CREATE TRIGGER Svr_DDLtrg_Audit_All_DDL_SERVER_SECURITY_EVENTS
ON All Server
FOR
DDL_SERVER_SECURITY_EVENTS

AS
SET NOCOUNT ON
If Object_ID('DDLServerScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT
dbo. DDLServerScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)
End
GO


-- Trigger to track very specific Server events

CREATE TRIGGER Svr_DDLtrg_Audit_All_DDL_SERVER_SPECIFIC_EVENT
ON All Server
FOR
CREATE_DATABASE ,CREATE_LOGIN
AS
SET NOCOUNT ON
If Object_ID('DDLServerScopedAuditTrail') IS NOT NULL
BEGIN
DECLARE @Eventdata XML
SET @Eventdata = EVENTDATA()

INSERT
dbo. DDLServerScopedAuditTrail
(EventType,EventData, ServerLogin,DBUser,TSQLText)

VALUES
( @Eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Eventdata, system_USER,CONVERT(nvarchar(100), CURRENT_USER),
@Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
)

End


Limitation

• DDL triggers will not fire in response to events that affect local or global temporary tables and stored procedures.
• DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers
Known issues of DDL Trigger in SQL Server 2005
• Truncate statement is not tracked by any event
• SP_Rename event is not tracked

For more information, please refer to:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124493
For an issue reported when you alter a Replicated Table, Please refer to:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=331367

SQL Server 2008
In SQL Server 2008, there are few more events added like sp_Rename is tracked using RENAME Event.

System objects for DDL Triggers

You can use the sys.triggers catalog view to see the database scoped DDL triggers. The parent_class column in this catalog view has a value of Zero which indicates that it’s a DDL Trigger. You can use the sys.server_triggers catalog view to get a list of server scoped DDL triggers. The sys.trigger_events and sys.server_trigger_events catalog views contain a row for each time a database is scoped and a server scoped trigger is fired, respectively.

Query to find out the Events and other details of DDL Trigger

SELECT Name, s.type_desc SQL_or_CLR,is_disabled, e.type_desc FiringEvents
FROM sys.server_triggers s
INNER JOIN sys.server_trigger_events e ON s.object_id = e.object_id

Where can I find DDL trigger in SSMO?

Database level DDL triggers are listed in the Programmability -- >> Database Triggers folder in Object Explorer. The server-level triggers are listed under the Server Objects -- >>Triggers folder in Object Explorer.

Figure 2



Drop DDL Trigger

The ON DATABASE or ON ALL SERVER clause should be used with DROP TRIGGER to drop a DDL trigger. All the other operations are similar to the DML trigger ie. ALTER TRIGGER to modify a DDL trigger definition, DISABLE TRIGGER to disable a DDL trigger and ENABLE TRIGGER to enable a DDL trigger.

Summary
DDL Trigger without any doubt is one of the best features of SQL Server 2005. There are known issues like few commands are not tracked by any events. In spite of these limitations, it gives a very good value add to the product in day to day activities. Now-a-days, auditing is obvious in any application and DDL trigger fits in there. We can make a complete automated auditing system using the DDL trigger and Reporting Service of SQL Server 2005.

Monitoring Memory Requirement

System Monitor is used to monitor memory usage in the server. In addition to System Monitor Tool you can use new Dynamic Management views also to collect data about SQL Server Memory.

Steps for Analyzing Memory Requirement

(a) Check how much physical memory is installed on the Box
(b) What other application/processes are consuming memory on the server
© Use System Monitor to capture the following counters
• Memory : Available Bytes :- it indicate how many bytes of memory are available
• Memory : Pages/Sec : Specifies how many pages must be read from the disk or written to the disk to resolve page faults
• SQLServer:BufferManager : This is very important counter , which will give you the Cache-hit ratio. This counter identifies the percentage of pages that were found in the buffer pool without read the disk. The value of this counter should be over 90%. High values indicate good cache usage and minimal disk access when searching for data
• SQLServer:MemoryManager:Total Server Memory :- Determines the amount of physical memory used by each instance of SQL Server
• Process:Working Set :- Gives the set of memory pages that have been recently accessed by the threads running in the process and can be used to determine how much memory SQL Server is using
• SQLServer:Buffer Manager:Page Life Expectance : It gives the average time spent by a data page in the data cache. A value of less than 3000 seconds indicates that SQL Server need more memory.

Dynamic Management Views used to collect data about SQL Server Memory Usage

• Sys.dm_exec_query_stats : Provides statistics on memory and CPU usage for a specific query.
• Sys.dm_exec_cached_plans : Gives a list of the query plans that are currently cached in mamory
• Sys.dm_os_memory_objects : Provides information about object types in memory, such as Memobj_Compile_Adhoc and Memobj_statement
• Sys.dm_os_memory_clerks returns the se of all memory clerks that are currently active in the instance of SQL Server.