Audit functionality using database trigger

USE [dbname]
GO

/****** Object:  DdlTrigger [DDL_Structure_Audit]    Script Date: 03/21/2012 17:57:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE trigger [DDL_Structure_Audit]
on database
for alter_table, drop_table, create_table,alter_procedure,create_procedure,drop_procedure
as

--set nocount on
-- RAISERROR (N'You have not permission to perform this action.', -- Message text.
-- 15, -- Severity,
-- 1 -- State,
-- )
-- Rollback transaction


declare @data xml
declare @vcapture varchar(max)
declare @vUserName varchar(25)
declare @vEventType varchar(50)
declare @vDatabaseName varchar(50)
Declare @ip varchar(75)

set @data = EVENTDATA()

Select @vcapture = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)') ,
@vUserName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)') ,
@vEventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)') ,
@vDatabaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(50)')


select @ip=
  IpMachineandUserInfo from(
Select top 1 client_net_address+' '+[host_name] as IpMachineandUserInfo from
(
SELECT ec.client_net_address, es.[program_name],
es.[host_name], es.login_name,
ec.session_id
FROM sys.dm_exec_sessions AS es
INNER JOIN sys.dm_exec_connections  AS ec
ON es.session_id = ec.session_id

)a
where a.login_name=@vUserName
)b

set @vUserName=@vUserName+@ip


if PATINDEX('%PROCEDURE%', upper(@vcapture)) > 0 and upper(@vUserName) = 'shamas'
Begin
Begin Transaction
IF PATINDEX('%DROP%', upper(@vcapture)) > 0
Begin
RAISERROR (N'You have not permission to perform this action.', -- Message text.
15, -- Severity,
1 -- State,
)
Rollback transaction
END
ELSE
BEGIN
INSERT INTO EventLogForDB.dbo.EvtLog  (PostTime ,LoginName , EventType ,TSQLCommand,DatabaseName )
values (getdate(),@vUserName,@vEventType,@vcapture,@vDatabaseName)
Commit Transaction
END
END
ELSE
INSERT INTO EventLogForDB.dbo.EvtLog  (PostTime ,LoginName , EventType ,TSQLCommand,DatabaseName )
values (getdate(),@vUserName,@vEventType,@vcapture,@vDatabaseName)

Step by Step guide to create Synonym in SQL Server 2008

Following are the main purposes of synonyms:

·         Provides an alternative name for another database object, referred to as the base object that can exist on a local or remote server.

·         Provides a layer of abstraction that protects a client application from changes made to the name or location of the base object.

To create synonyms we need to take care of followings

·         A synonym belongs to a schema.

·         Synonym must be unique in a schema.

·         You can create synonyms for the following database objects:

Assembly (CLR) stored procedure	Assembly (CLR) table-valued function
Assembly (CLR) scalar function	Assembly (CLR) aggregate functions
Replication-filter-procedure	Extended stored procedure
SQL scalar function	SQL table-valued function
SQL inline-tabled-valued function	SQL stored procedure
View	Table

On demand of one of my friend I have posted the blog post to show how we can create table using synonym. In following example I have created a sample database Blog and a table synonym which has two columns ID and name. I have inserted 3 sample values in this table. We will start synonym generation process from synonym tab in SSMS

When click on New Synonym we got following screen

We have following attributes

·         Synonym name: name of the table used as synonym

·         Synonym schema: schema that used with synonym. You can insert different schema then original table schema.

·         Server name: if we want to call a table from different server then we need to mention its name. as we can see in later attached pictures

·         Database name: name of the database from where we pick a table for synonym

·         Schema: current table schema

·         Object type: we can select Table and other options mentioned above. But for our example we will select Table now

·         Object name: tables listed from mentioned database above and select required table. In this example I have select ‘Synonym’

I have named my synonym student

In the above attached screen you can see Synonym as table and Student as synonym. When I ran a query on both tables I return same dataset.

Following are the options you can select creating synonym.

This screen will show you how you can mention server name

And look at the last screen shot. If you need to call a table from master database or any other server. You need to mention database name before table name. Just for reminder you need to create link server for cross server database table.

major benefit using this technique we do not need to mention database name before table.

Microsoft presentation on SQL SERVER history

A very good presentation describing SQL SERVER history from 1989 to 2012.

Microsoft flash presentation

Failover Techniques in SQL SERVER Snapshot vs. Log Shipping vs. Mirroring vs. Replication vs. Clustering

For a new person this is difficult to find differences in snapshot, log shipping, mirroring and replication. I have gone through multiple sites and videos last night and now summing up my findings for all 5 techniques. According to many expert persons I have found priorities in terms of failover & point in time recovery support of these techniques in SQL SERVERS, following are these priorities.

1-    Failover Clustering

2-    Replication

3-    Mirroring

4-    Log Shipping

5-    Snapshot

Here is the basic and major difference between all techniques

1) Failover Clustering is a high availability option used with clustering technology provided by hardware and Operating system. Data resides on SAN network storage instead of any particular server. Advantages of SAN storage is large efficient hot pluggable disk storage. You might use other technique like mirroring with failover clustering.

2) Replication is used mainly when data centers are distributed geographically. It is used to replicate data from local servers to the main server in the central data center. There is no stand by server like in mirroring and publisher & subscriber both are active. There are different data updating types in replication

Type of replication	Use when…
Merge replication	·         There are a large number of Subscribers. ·         Data is replicated to mobile users. ·         Replicated data is frequently updated at the Subscriber. ·         Data filtering is needed so that Subscribers receive different partitions of data.
Peer-to-peer transactional replication	·         Replication is used to improve scalability and availability. ·         Minimal latency is required. ·         Data is not partitioned among Subscribers. ·         Conflicts typically do not occur, but they must be detected if they do.
Transactional replication with updating subscriptions	·         There are a small number of Subscribers. ·         Replicated data is mostly read-only at the Subscriber. ·         Subscriber, Distributor, and Publisher are connected most of the time (for immediate updating subscriptions).
Snapshot Replication	·         Provide initial data set for transactional and merge publications ·         Can be used by itself ·         It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time. ·         Replicating small volumes of data. ·         Snapshot replication is most appropriate when data changes are substantial but infrequent.

3) Mirroring which was introduced with 2005 edition, works on top of Log Shipping. Main difference is the uptime for the standby server is quite less in mirroring. Standby server automatically becomes active in this case with the help of Witness, Additional advantages of Mirroring include support at .NET Framework level plus some new features like Auto Page Recovery introduced with SQL SERVER 2008.

4) Log Shipping is an old technique available since SQL SERVER 2000. Here the transactional log (ldf) is transferred periodically to the standby server. If the active server goes down, the stand by server can be brought up by restoring all shipped logs. As this process will complete manually you can also jobs to copy backups to destination folder and other job use that backup to restore.

5) Snapshot is a static read only picture of database at a given point of time. It is used as snapshot replication. Data is updated to snapshot when you have mentioned SQL Agent to run snapshot job. You can perform this for to get offline database with latest data changes for reporting.

I will soon upload videos for each technique. Thanks.

Changing database status from suspect to Online

Problem: Making a suspect database to online in SQL SERVER.

Solution:

Sometimes SQL server database mark as suspect due to error in its physical files like log file. It is difficult to make database online when made as suspect database. There are several ways we can identify that a database is in suspect mode

select DATABASEPROPERTYEX ( 'database_name' , 'status')

or

SELECT * FROM sys.databases WHERE state = 4;

Or

You can also check yellow ! sign from SSMS.

You can recover suspect db using 2 ways

1-      Restore latest database backup with transactional backup if you have

2-      If there is any log error recorded in sql server error log. You can recover error to check that or by replacing log file.

3-      Use following command to change stats but this will loss uncommitted data from log file.

ALTER DATABASE  DBName  SET  EMERGENCY

GO

ALTER DATABASE  DBName  SET SINGLE_USER

GO

This command will set database in emergency condition so that single sign on user can work on it.

DBCC CheckDB (DBName , REPAIR_ALLOW_DATA_LOSS)

GO

You will receive folloiwng output.

DBCC results for 'dbNAME'.

Service Broker Msg 9675, State 1: Message Types analyzed: 14.

Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.

Service Broker Msg 9667, State 1: Services analyzed: 3.

Service Broker Msg 9668, State 1: Service Queues analyzed: 3.

Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.

Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.

Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.

Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.

DBCC results for 'sys.sysrscols'.

There are 632 rows in 7 pages for object "sys.sysrscols".

DBCC results for 'sys.sysrowsets'.

There are 91 rows in 1 pages for object "sys.sysrowsets".

DBCC results for 'sys.sysallocunits'.

There are 103 rows in 2 pages for object "sys.sysallocunits".

DBCC results for 'sys.sysfiles1'.

There are 2 rows in 1 pages for object "sys.sysfiles1".

DBCC results for 'sys.syspriorities'.

There are 0 rows in 0 pages for object "sys.syspriorities".

DBCC results for 'sys.sysfgfrag'.

There are 2 rows in 1 pages for object "sys.sysfgfrag".

DBCC results for 'sys.sysphfg'.

There are 1 rows in 1 pages for object "sys.sysphfg".

DBCC results for 'sys.sysprufiles'.

There are 2 rows in 1 pages for object "sys.sysprufiles".

DBCC results for 'sys.sysftinds'.

There are 0 rows in 0 pages for object "sys.sysftinds".

DBCC results for 'sys.sysowners'.

There are 14 rows in 1 pages for object "sys.sysowners".

DBCC results for 'sys.sysprivs'.

There are 130 rows in 1 pages for object "sys.sysprivs".

DBCC results for 'sys.sysschobjs'.

There are 53 rows in 1 pages for object "sys.sysschobjs".

DBCC results for 'sys.syscolpars'.

There are 483 rows in 8 pages for object "sys.syscolpars".

DBCC results for 'sys.sysnsobjs'.

There are 1 rows in 1 pages for object "sys.sysnsobjs".

DBCC results for 'sys.syscerts'.

There are 0 rows in 0 pages for object "sys.syscerts".

DBCC results for 'sys.sysxprops'.

There are 0 rows in 0 pages for object "sys.sysxprops".

DBCC results for 'sys.sysscalartypes'.

There are 34 rows in 1 pages for object "sys.sysscalartypes".

DBCC results for 'sys.systypedsubobjs'.

There are 0 rows in 0 pages for object "sys.systypedsubobjs".

DBCC results for 'sys.sysidxstats'.

There are 107 rows in 1 pages for object "sys.sysidxstats".

DBCC results for 'sys.sysiscols'.

There are 259 rows in 1 pages for object "sys.sysiscols".

DBCC results for 'sys.sysbinobjs'.

There are 23 rows in 1 pages for object "sys.sysbinobjs".

DBCC results for 'sys.sysaudacts'.

There are 0 rows in 0 pages for object "sys.sysaudacts".

DBCC results for 'sys.sysobjvalues'.

There are 109 rows in 14 pages for object "sys.sysobjvalues".

DBCC results for 'sys.sysclsobjs'.

There are 16 rows in 1 pages for object "sys.sysclsobjs".

DBCC results for 'sys.sysrowsetrefs'.

There are 0 rows in 0 pages for object "sys.sysrowsetrefs".

DBCC results for 'sys.sysremsvcbinds'.

There are 0 rows in 0 pages for object "sys.sysremsvcbinds".

DBCC results for 'sys.sysxmitqueue'.

There are 0 rows in 0 pages for object "sys.sysxmitqueue".

DBCC results for 'sys.sysrts'.

There are 1 rows in 1 pages for object "sys.sysrts".

DBCC results for 'sys.sysconvgroup'.

There are 0 rows in 0 pages for object "sys.sysconvgroup".

DBCC results for 'sys.sysdesend'.

There are 0 rows in 0 pages for object "sys.sysdesend".

DBCC results for 'sys.sysdercv'.

There are 0 rows in 0 pages for object "sys.sysdercv".

DBCC results for 'sys.syssingleobjrefs'.

There are 146 rows in 1 pages for object "sys.syssingleobjrefs".

DBCC results for 'sys.sysmultiobjrefs'.

There are 106 rows in 1 pages for object "sys.sysmultiobjrefs".

DBCC results for 'sys.sysguidrefs'.

There are 0 rows in 0 pages for object "sys.sysguidrefs".

DBCC results for 'sys.syscompfragments'.

There are 0 rows in 0 pages for object "sys.syscompfragments".

DBCC results for 'sys.sysftstops'.

There are 0 rows in 0 pages for object "sys.sysftstops".

DBCC results for 'sys.sysqnames'.

There are 97 rows in 1 pages for object "sys.sysqnames".

DBCC results for 'sys.sysxmlcomponent'.

There are 99 rows in 1 pages for object "sys.sysxmlcomponent".

DBCC results for 'sys.sysxmlfacet'.

There are 112 rows in 1 pages for object "sys.sysxmlfacet".

DBCC results for 'sys.sysxmlplacement'.

There are 18 rows in 1 pages for object "sys.sysxmlplacement".

DBCC results for 'sys.sysobjkeycrypts'.

There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".

DBCC results for 'sys.sysasymkeys'.

There are 0 rows in 0 pages for object "sys.sysasymkeys".

DBCC results for 'sys.syssqlguides'.

There are 0 rows in 0 pages for object "sys.syssqlguides".

DBCC results for 'sys.sysbinsubobjs'.

There are 3 rows in 1 pages for object "sys.sysbinsubobjs".

DBCC results for 'sys.syssoftobjrefs'.

There are 0 rows in 0 pages for object "sys.syssoftobjrefs".

DBCC results for 'sys.queue_messages_1977058079'.

There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".

DBCC results for 'sys.queue_messages_2009058193'.

There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".

DBCC results for 'sys.queue_messages_2041058307'.

There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".

DBCC results for 'sys.filestream_tombstone_2073058421'.

There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".

DBCC results for 'sys.syscommittab'.

There are 0 rows in 0 pages for object "sys.syscommittab".

CHECKDB found 0 allocation errors and 0 consistency errors in database 'dbNAME'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

ALTER DATABASE  DBName  SET MULTI_USER

GO

This command will change single user status to multi user and your database is now online again. 

SQL SERVER Latest Versions List

Here is the latest release information for SQL SERVER's 

Microsoft Live support

SQL Server 2008 R2

SQL Server 2008 R2 Datacenter
SQL Server 2008 R2 Developer
SQL Server 2008 R2 Enterprise
SQL Server 2008 R2 Express
SQL Server 2008 R2 Express with Advanced Services
SQL Server 2008 R2 Standard
SQL Server 2008 R2 Standard Edition for Small Business
SQL Server 2008 R2 Web
SQL Server 2008 R2 Workgroup
SQL Server 2008 R2 Service Pack 1

SQL Server 2008

SQL Server 2008 Developer
SQL Server 2008 Enterprise
SQL Server 2008 Express
SQL Server 2008 Express with Advanced Services
SQL Server 2008 Standard
SQL Server 2008 Standard Edition for Small Business
SQL Server 2008 Web
SQL Server 2008 Workgroup
SQL Server 2008 Service Pack 1
SQL Server 2008 Service Pack 2
SQL Server 2008 Service Pack 3

SQL Server 2005

SQL Server 2005 Compact Edition
SQL Server 2005 Developer Edition
SQL Server 2005 Enterprise Edition
SQL Server 2005 Enterprise Edition for Itanium-based Systems
SQL Server 2005 Enterprise X64 Edition
SQL Server 2005 Express Edition
SQL Server 2005 Express Edition with Advanced Services
SQL Server 2005 Service Pack 1
SQL Server 2005 Service Pack 2
SQL Server 2005 Service Pack 3
SQL Server 2005 Service Pack 4
SQL Server 2005 Standard Edition
SQL Server 2005 Standard Edition for Itanium-based Systems
SQL Server 2005 Standard X64 Edition
SQL Server 2005 Workgroup Edition

SQL Server 2000

SQL Server 2000 64-bit Edition
SQL Server 2000 Desktop Engine
SQL Server 2000 Desktop Engine Release A
SQL Server 2000 Developer Edition
SQL Server 2000 Enterprise Edition
SQL Server 2000 Reporting Services Service Pack 1
SQL Server 2000 Reporting Services Service Pack 2
SQL Server 2000 Service Pack 1
SQL Server 2000 Service Pack 2
SQL Server 2000 Service Pack 3a
SQL Server 2000 Service Pack 4
SQL Server 2000 Standard Edition
SQL Server 2000 Windows CE Edition 2.0
SQL Server 2000 Workgroup Edition

Older Versions

View the Support Lifecycle Index