Search This Blog & Web

Friday, March 30, 2012

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)



Thursday, March 29, 2012

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.

Wednesday, March 7, 2012

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.

Monday, March 5, 2012

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 

SQL Server 2008 R2
SQL Server 2008
SQL Server 2005
SQL Server 2000
Older Versions