NHibernate vs stored procedures

Using ORM tools with stored procedures has security, performance and maintenance advantages over using ORM tools with direct access to tables.

In my view, ORM tools are great, but plugging them directly to the underlying tables has security, performance and manageability overheads.

The best approach I have seen is to use stored procedures as an abstraction layer between the ORM and the database tables. This will protect the code from any schema changes and allows the DBA to tighten security on the procedure level and fine tune the procedures without impacting the underlying code. In contrast, using ORM directly with tables increases coupling between the database and code, any schema change will require change in the ORM and the code need to be compiled and redeployed.

It is possible to use Views with ORM to provide some level of abstraction, and security, but views have many limitation such as the inability to order the result in a view and views can’t take parameters are two of the main shortcoming.

Security

• Grant Execute permission to stored procedure only, no need to grant permission on the database table level.
• Using stored procedures with correct parameters protect against sql injection. Most decent ORM tools use sp_executesql with parameters which protects against sql injection.
• Centralised access to the database.

Performance

• Query tuning is much easier with stored procedures. Fine tuning is restricted or unavailable for some table hint or index hints when using ORM.
• Complex queries with multiple joins tend to be cumbersome and it requires seasoned developers to get the ORM mapping correct. This can cause performance issues.
• Stored procedures tend to have good cache plan; some ORM tools cause cache bloating due to the way the dynamic sql is constructed. An example is when the ORM passes a different varchar(x) length depending on the length of the parameter (x) which causes cache bloating. Another issue that causes performance degradation with some ORM tools is when they treat string as Nvarchar which causes implicit conversion if the underlying column type is not nvarchar. This can cause considerable performance issues with large tables (see post).
• With stored procedures less data is passed over the wire, i.e. just the procedure name not the full query string.
• Most ORMs return a full object even if only one attribute is needed, for example to lookup a customer name by ID, it will return the customer object with all its attributes, and if not careful it might also load all its children (e.g. all the orders for the customer..etc).

Maintenance

• Stored procedures can be tested as a single unit without the need to compile and deploy any code.
• It might be quicker to get the code out of the door with ORM, but the ongoing maintenance cost is much higher.
• The profiler output of a statement submitted by ORM is unreadable compared to stored procedure execution.

The last point is that access to data should be designed by DBA who has knowledge of the environment and the indexes, ORM encourages top down design which does not take into account the efficiency in which data need to be accessed. The applications designed with this approach tend to work well in development but as soon as they are deployed to production with a couple of million records, they fail to perform.

And finally, most databases outlive the applications by many years, so it pays off to have a robust data abstraction layer that consists of a set of stored procedures which can be used as a gateway to access your most valuable asset which is your data.

Top 14 KPI - SQLServerCentral

Top 14 KPI - SQLServerCentral

Instant initialisation of sql server files

There is a feature in windows 2003 and above that enables sql server to instantly initialise files to the required size without delay. It is called instant file initialization.

This is very useful when growing data files, adding new files and Filegroup and if we ever have to restore any database.

In order for Sql server to use this feature, the Sql server service account needs to be granted “perform volume maintenance tasks” permission.

For this to take effect, Sql server needs to be restarted.

Kimberly Tripp has a very good blog about this which you can read here 

She explains how to enable this in the link above 

"Granting the permission "Perform Volume Maintenance Tasks"
To use instant initialization, your SQL Server service must be running with an account that has the required privilege. If your SQL Server service is running as a local administrator this permission already exists. For a service account which is not a local administrator (again, recommended!), the necessary privilege to grant is Perform Volume Maintenance Tasks. This permission can be granted by an administrator through the Local Security Policy tool (Start, All Programs, Administrative Tools) and once granted, SQL Server automatically uses instant initialization. IMPORTANT NOTE: If this permission is given while SQL Server is running, then SQL Server must be stopped and restarted. However, once the server is restarted, no other syntax or permissions are needed. All data file creation/extension options will use instant initialization for data files created on NTFS volumes when SQL Server 2005 is running on Windows XP or Windows 2003 Server."

A list of useful tools

Here I am keeping a list of useful tools that I use regularly;

Balsamiq is a rapid wireframe/ UI design tool
Dia a diagram creation product, an open source replacement for Visio.

Ext JS 4.1 controls

Partitioning a large table with existing records

Best practice when partitioning a large table with existing records.

When trying to partition a large table that contain data, the main thing is trying to minimise data movements. When creating a new partition, if data exists in the table that falls in the new partition, Sql Server will delete the data from the old partition and insert it into the new partition. This can be a very expensive operation and can lead to locking the table.

It might not be obvious at first glance, but creating the partitions in a certain order will have a significant impact on the amount of data moved and the time it takes to partition a large table. I will try to illustrate this tip with an example;

Let’s assume you have a table that contains 12 million records of historic orders made in 2012 which you want to partition by Order Date. To make the calculation easier, lets also assume that the records are distributed evenly so for each month of 2012 there are 1 million orders.

Option 1: Start by creating a partition for each month starting from February 2012 using partition right on the order date column, the result will be as shown in Table 1 below:

Steps performed to partition the table	What happens (Assuming partition right is used)
Split Partition at FEB 2012	11 Million records DELETED from the previous partition 11 Million records INSERTED into the new partition
Split Partition at MAR 2012	10 Million records DELETED from the previous partition 10 Million records INSERTED into the new partition
Split Partition at APR 2012	9 Million records DELETED from the previous partition 9 Million records INSERTED into the new partition
Split Partition at MAY 2012	8 Million records DELETED from the previous partition 8 Million records INSERTED into the new partition
Split Partition at JUN 2012	7 Million records DELETED from the previous partition 7 Million records INSERTED into the new partition
Split Partition at JUL 2012	6 Million records DELETED from the previous partition 6 Million records INSERTED into the new partition
Split Partition at AUG 2012	5 Million records DELETED from the previous partition 5 Million records INSERTED into the new partition
Split Partition at SEP 2012	4 Million records DELETED from the previous partition 4 Million records INSERTED into the new partition
Split Partition at OCT 2012	3 Million records DELETED from the previous partition 3 Million records INSERTED into the new partition
Split Partition at NOV 2012	2 Million records DELETED from the previous partition 2 Million records INSERTED into the new partition
Split Partition at DEC 2012	1 Million records DELETED from the previous partition 1 Million records INSERTED into the new partition
TOTAL RECORDS DELETED + INSERTED	132 million records

Option 2: Create the partitions in a reverse order starting from the last partition DEC 2012, will result in far less data movement as illustrated in table 2 below:

Table 2 : creating the partitions in a different order

Steps performed to partition the table	What happens (Assuming partition right is used)
Split Partition at DEC 2012	1 Million records DELETED from the previous partition 1 Million records INSERTED into the new partition
Split Partition at NOV 2012	1 Million records DELETED from the previous partition 1 Million records INSERTED into the new partition
Split Partition at OCT 2012	1 Million records DELETED from the previous partition 1 Million records INSERTED into the new partition
Split Partition at SEP 2012	1 Million records DELETED from the previous partition 1 Million records INSERTED into the new partition
Split Partition at AUG 2012	1 Million records DELETED from the previous partition 1 Million records INSERTED into the new partition
Split Partition at JUL 2012	1 Million records DELETED from the previous partition 1 Million records INSERTED into the new partition
Split Partition at JUN 2012	1 Million records DELETED from the previous partition 1 Million records INSERTED into the new partition
Split Partition at MAY 2012	1 Million records DELETED from the previous partition 1 Million records INSERTED into the new partition
Split Partition at APR 2012	1 Million records DELETED from the previous partition 1 Million records INSERTED into the new partition
Split Partition at MAR 2012	1 Million records DELETED from the previous partition 1 Million records INSERTED into the new partition
Split Partition at FEB 2012	1 Million records DELETED from the previous partition 1 Million records INSERTED into the new partition
TOTAL RECORDS DELETED + INSERTED	22 million records

By creating the partitions in the reverse order the number of records moved is drastically reduced (132 vs 22) resulting in a more efficient way to partition large tables with existing data.

DBA VS System Administrator

Database administrators (DBA) and System Administrators frequently get into conflict due to the overlap in the tasks they perform on a daily basis.
I have worked in many places, each have different rules. It mainly depends on the skills the individual have. If the DBA is more skillful, he or she tend to be doing more of the System admin tasks. However, when the system administrator have more technical skills, they tend to spread into the sql server/ database areas.

Here is a list of tasks that broadly fit into each role, some of them overlap.
The best environment I worked in is when the DBA and System administrators work together and trust each other and learn from each other..

I would be interested to hear your views!

System administrator

• Network
• Server hardware
• Server file/OS backup
• Offline backup
• Server upgrade and patching
• Monitoring and analysing server logs (Application/ security/ server ..etc)
• Disk space monitoring
• Third party backup tool/ Tape backup (e.g. veritas)
• Server level security (server certificates/ AD groups/ users management). Service accounts
• Server build and server rebuild (disaster recovery)
• Manager scheduled tasks
• Windows install and setup
• Windows configuration (Adding/ removing new features/ roles..etc)
• Windows cluster setup
• Allocate Lun from SAN
• Perform snapshot on volume/ server level (SAN)
• Server audits and change management
• Responsible for server security
• Manage server collation
• Server documentation
  

DBA

• Database performance monitoring
• Database server tunning (IO, memory, CPU, Network)
• Database backup and recovery
• Monitoring and analysing sql error logs/ sql agent error logs/ FT index error logs
• Database upgrade and patching
• Disk space monitoring
• Database instance security (logins and roles)/ object level permission management.
• Database / filegroup/ file restore (disaster recovery)
•  Manage SQL Server Agent scheduled tasks (create/ monitor/ troubleshoot)..
•  Database instance level configuration (e.g. setting Max server memory for an instance or configuring cpu affinity value)
• Database install and setup
• Configure database cluster
• Setup and configure High availability features (Database mirroring/ log shipping/ replication)
• Perform database snapshots
• Database audits and change management
• Responsible for Database security
• Manage database collation 
• Database documentation
  
  

Downgrade SQL Server database - from a higher version to a lower version

A backup of a Sql server database that was taken from a older version can be restored to a new version with ease (in most cases).
However, a database cannot be resotred into an older version. for example you cant restore a database backup that was taken in SQL Server 2008 R2 and restore it on SQL Server 2008 or SQL Server 2005.

If you are unfortunate enough to be in a situation where you have a backup of a newer version and you want to restore it onto an older version of SQL Server, here are the steps you need to do;

For this you will need access to two instances of sql server (They can be running on the same box or on seperate boxes);

• Instance 1: running the old version say SQL 2005, I will call it Destination2005
• Instance 2: running the new version say SQL 2008R2, I will call it Source2008R2

1. On the destination server, create a linked server called Source2008R2
2. Create an empty database on the Destination2005 server (this will become your database).
3. Using a tool like Redgate SQL Compare script the schema of the database on Source2008R2 and run it against the new (empty) database you have created in the previous step.
4. Create a script to disable the Foriegn key constraints and another script to enable foriegn key constraints. There are many blogs on the web for this, I got this from http://www.sqlmag.com/forums/aft/70529

Disable all Constraints
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

Disable all Triggers
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

## execute updates here...

Enable all Constraints
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Enable all Triggers
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

1. 5. Now you have an empty database on Destination2005 instance with the same schema as the one on Source2008R2 and with all constraints disabled.
2. 6. Start importing the data by running the following script on Destination2005. This script will generate the insert statements required to import the data from the source.

/*

YB: A script to copy data from one database to another.

Run it in the destination database. AT YOUR OWN RISK..

*/

DECLARE

@TABLENAME NVARCHAR(100) ,
@ColumnName  NVARCHAR(100) ,
@HasIdentity INT,
@SQL1 NVARCHAR(MAX),
@SQL2 NVARCHAR(MAX),
@NewLine NVarchar(4)

SET @newline=NCHAR(13) + NCHAR(10)
DECLARE ParentCursor CURSOR FOR
SELECT Tab.TABLE_NAME, HasIdentity =CASE WHEN Ident.INCREMENT_VALUE IS NULL THEN 0 ELSE 1 END
FROM
INFORMATION_SCHEMA.TABLES Tab
LEFT OUTER JOIN SYS.IDENTITY_COLUMNS Ident ON Tab.TABLE_NAME=OBJECT_NAME(OBJECT_ID)
WHERE
TABLE_TYPE='BASE TABLE'
and TABLE_CATALOG='MyDatabaseName'
OPEN ParentCursor;
FETCH NEXT FROM ParentCursor INTO @TABLENAME, @HasIdentity
WHILE @@FETCH_STATUS =0
BEGIN
-- PRINT 'HAS Identity : ' + convert (VARCHAR(10), @HasIdentity);
SET @SQL1=N''
SET @SQL2=N''
DECLARE ChildCursor CURSOR FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_CATALOG='MyDatabaseName'
AND TABLE_NAME=@TABLENAME
AND DATA_TYPE <> 'Timestamp'
ORDER BY
Ordinal_position
OPEN ChildCursor;
FETCH NEXT FROM ChildCursor INTO @ColumnName
WHILE @@FETCH_STATUS =0
BEGIN
SET @SQL2 = @SQL2 +'['+ @ColumnName + '],'
FETCH NEXT FROM ChildCursor INTO @ColumnName
END
CLOSE ChildCursor;
DEALLOCATE ChildCursor;
   -- Remove the last , from @SQL2
SET @SQL2=LEFT ( @SQL2, LEN(@SQL2) -1)
SET @SQL1=@SQL1 + 'TRUNCATE TABLE [MyDatabaseName].[dbo].[' + @TABLENAME + ']' + @NewLine + 'GO' + @NewLine ;
IF @HasIdentity=1
BEGIN
SET @SQL1 =@SQL1 + 'SET Identity_INSERT [' + @TABLENAME + '] ON' + @NewLine
END
ELSE
BEGIN
SET @SQL1 =@SQL1 + ' '
END
SET @SQL1=@SQL1 + 'INSERT INTO [MyDatabaseName].[dbo].[' + @TABLENAME + '] (' + @SQL2 + ')' + @NewLine;
SET @SQL1=@SQL1 + 'SELECT ' + @SQL2 + ' FROM [Source2008R2].[MyDatabaseName].[dbo].[' + @TABLENAME + ']' + @NewLine;

IF @HasIdentity=1
BEGIN
SET @SQL1=@SQL1 + 'SET Identity_INSERT [' + @TABLENAME + '] OFF' + @NewLine;
END
ELSE
BEGIN
SET @SQL1 =@SQL1 + ' '
END

SET @SQL1=@SQL1 + 'GO' + @NewLine;
 

PRINT @SQL1

FETCH NEXT FROM ParentCursor INTO @TABLEName, @HasIdentity
END

CLOSE ParentCursor;
DEALLOCATEParentCursor;