Hossam Abdel Wahab "TECHNICAL BLOG"

SQL Server perfromance Counters

2010-03-11T13:39:00.001+04:00

What's new for PerformancePoint Services (SharePoint Server 2010)

2010-01-27T14:18:00.000+04:00

Converting a Stored Procedure 2 View

2009-12-07T13:44:00.004+04:00

SQL Server 2008 R2 Pricing and Feature Changes

2009-11-19T16:10:00.005+04:00

Standard Edition: Now with Backup Compression
SQL Server 2008 introduced backup compression, but it was only available in Enterprise Edition. At the time, Enterprise Edition cost around $20,000 more per processor than Standard Edition, so companies couldn’t justify upgrading to Enterprise Edition just to get backup compression. Companies had to need Enterprise for multiple features in order to stomach the price. If all a DBA needed was compression, they could buy backup compression software much cheaper than the price of Enterprise Edition.
In SQL 2008 R2, even Standard Edition gets backup compression. That’s a game-changer, and I’d expect to see smaller companies that do backup compression – and nothing else – to start falling by the wayside.
In addition, Standard can now be a managed instance – it can be managed by some of the slick multi-server-management tools coming down the pike like the Utility Control Point (read my SQL 2008 R2 Utility review). It can’t be the management server itself – it can’t be a Utility Control Point – but at least we can manage Standard. It’s good to see that Microsoft recognizes all servers need to be managed, not just the expensive ones. Big thumbs up there.
Enterprise Edition: CPU Limits
In Enterprise, Microsoft giveth and Microsoft taketh away. SQL 2008 R2’s BI tools include a new Master Data Services tool. It’s targeted at enterprises with data warehouses that need to manage incoming data from lots of different sources, and that data isn’t always clean or correct. MDS helps make sure data follows business rules. This isn’t a common need for OLTP systems, so it’s only included in Enterprise, not Standard. Makes sense.
A little less easy to stomach, however, is a new set of caps on Enterprise Edition. The current SQL 2008 comparison page shows that Enterprise has no licensing limit on memory or the number of CPU sockets. SQL 2008 R2 Enterprise Edition is capped at 8 CPU sockets, and there’s a memory cap as well, but I haven’t been able to track down a public page showing the cap. The only hint is the SQL 2008 R2 edition comparison page, which notes that Datacenter Edition (more on that in a second) is licensed for “memory limits up to OS maximum.” If that wasn’t a unique selling point, it shouldn’t be included in the feature list.
The more expensive Enterprise can act as the management server (Utility Control Point) for up to 25 instances. However, that doesn’t mean you need to buy one Enterprise per 24-25 Standard servers, and then manage them in pools – there’s an app edition for that.
Datacenter Edition: For, Well, Datacenters
The new Datacenter Edition picks up where Enterprise now runs out of gas. It supports more than 8 sockets, up to 256 cores, and all the memory you can afford. Or can’t afford, for that matter.
If you’re going to manage over 25 instances with the Utility Control Point stuff, Datacenter Edition can manage “more than 25 instances” according to Microsoft’s edition comparison page. I like how they worded that – they didn’t say “unlimited instances,” because there will be performance impacts associated with using Utility Control Points. The performance data collections gather a lot of data, and storing it for hundreds of instances will take some pretty high performance hardware.

Parallel Data Warehouse Edition: Sold with Hardware Only
The big new fella in town getting all the press is the artist formerly known as Project Madison, formerly known as DATAllegro. It’s a scale-out data warehouse appliance, but you won’t find this appliance at Home Depot. This version of SQL Server is sold in reference architecture hardware packages from Bull, Dell, HP, EMC, and IBM. Write one check, and you get a complete soup-to-nuts data warehouse storage engine that includes everything from the servers, SAN, configuration, and training.
I had the chance to talk with Microsoft’s Val Fontama, and I’ll post more details of that interview next week, but I have to share one quick snippet. I asked what happens when a Parallel Data Warehouse system starts to have performance issues, and he explained that the DBA will need to call in specialized Parallel engineers. You won’t be popping open this rack and installing another drawer of hard drives yourself or adding additional commodity hardware boxes to scale out your datacenter. It’s more of a sealed solution than something you have to build yourself.
I have mixed feelings about this – as a guy who loves hardware, I want to dive under the hood. However, as a guy who’s managed data warehouses, I know it’s one heck of an ugly skillset to learn on the job, and when data gets into the 5-10 terabyte range, you can’t afford to make configuration mistakes.

How Much Would You Pay For All This?
It slices. It dices. And if you call now, you can get all this for the low, low sticker price of:
• Standard Edition – $7,499 per processor (socket)
• Enterprise Edition – $28,749 per processor
• Datacenter Edition – $57,498 per processor
• Parallel Data Warehouse Edition – $57,498 per processor (but you’ll be buying this in combination with hardware anyway)
Eagle-eyed readers will note it’s about a 20% price increase from SQL Server 2008. That’s probably easy to justify on Standard Edition because Microsoft can say they’re throwing in backup compression, a feature that normally would have cost extra from third party vendors.
SQL 2008 R2 Enterprise Edition, however, won’t have quite as easy of a time justifying its price increase given that it now has CPU caps and already had backup compression anyway

MS SQL Server System Databases

2009-07-01T16:26:00.003+04:00

The Resource Database
SQL Server added the Resource database. This database contains all the read-only critical system tables, metadata, and stored procedures that SQL Server needs to run. It does not contain any information about your instance or your databases, because it is only written to during an installation of a new service pack.
The Resource database contains all the physical tables and stored procedures referenced
logically by other databases. The database can be found by default in C:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\Binn.mdf and .ldf, and there is only one Resource database perinstance.
The use of drive C: in the path assumes a standard setup. If your machine is set up differently, you may
need to change the path to match your setup. Additionally, the .MSSQLSERVER is the instance name.
If your instance name is different, use your instance name in the path.
In SQL Server 2000, when you upgraded to a new service pack, you would need to run many long scripts to drop and recreate system objects. This process took a long time to run and created an environment hat couldn’t be rolled back to the previous release after the service pack. In SQL Server 2008, when you pgrade to a new service pack or quick fix, a copy of the Resource database overwrites the old database.
This enables you to both quickly upgrade your SQL Server catalog and roll back a release.
The Resource database cannot be seen through Management Studio and should never be altered unless you’re under instruction to do so by Microsoft Product Support Services (PSS). You can connect to the database under certain single-user mode conditions by typing the command USE MSSQLSystemResource.
Typically, a DBA runs simple queries against it while connected to any database, instead of having to connect to the resource database directly. Microsoft provides some functions which allow this access. For example, if you were to run this query while connected to any database, it would return your Resource database’s version and the last time it was upgraded:
SELECT serverproperty(‘resourceversion’) ResourceDBVersion,
serverproperty(‘resourcelastupdatedatetime’) LastUpdateDate Do not place the Resource database on an encrypted or compressed drive. Doing this may cause upgrade or performance issues.

The master Database
The master database contains the metadata about your databases (database configuration and file location),logins, and configuration information about the instance. You can see some of the metadata stored in master by running the following query, which returns information about the databases that exist onthe server:SELECT * FROM sys.databasesThe main difference between the Resource and master databases is that the master database holds dataspecific to your instance, whereas the Resource database just holds the schema and stored proceduresneeded to run your instance, but does not contain any data specific to your instance. You should alwaysback up the master database after creating a new database, adding a login, or changing the configurationof the server.

tempdb Database
The tempdb database is similar to the operating system paging file. It’s used to hold temporary objects created by users, temporary objects needed by the database engine, and row-version information. The tempdb database is created each time you restart SQL Server. The database will be recreated to be its original database size when the SQL Server is stopped. Because the database is recreated each time, there is no reason to back it up. Data changes made to objects in the tempdb database benefit from reduced logging.
It is important to have enough space allocated to your tempdb database, because many operations that you will use in your database applications use the tempdb. Generally speaking, you should set tempdb to autogrow as it needs space. If there is not enough space, the user may receive one of the following errors:
❑ 1101 or 1105: The session connecting to SQL Server must allocate space in tempdb.
❑ 3959: The version store is full.
❑ 3967: The version store must shrink because tempdb is full.

model Database
model is a system database that serves as a template when SQL Server creates a new database. As each database is created, SQL Server copies the model database as the new database. The only time this does not apply is when you restore or attach a database from a different server.
If a table, stored procedure, or database option should be included in each new database that you create on a server, you may simplify the process by creating the object in model. When the new database is created, model is copied as the new database, including the special objects or database settings you have added to the model database. If you add your own objects to model, model should be included in your backups, or you should maintain a script which includes the changes.

msdb Database
msdb is a system database that contains information used by SQL Server agent, log shipping, SSIS, and the backup and restore system for the relational database engine. The database stores all the information about jobs, operators, alerts, and job history. Because it contains this important system-level data, you should back up this database regularly

Shrinking Truncate Log File - Log Full

2009-05-05T01:31:00.005+04:00

Sometime, it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible.

USE DatabaseName
GO
DBCC SHRINKFILE(TransactionLogName, 1)
BACKUP LOG 'DatabaseName' WITH TRUNCATE_ONLY
DBCC SHRINKFILE(TransactionLogName, 1)
GO

Rebuild System Databases in SQL Server 2008

2009-04-11T15:16:00.002+04:00

The syntax for using setup.exe to rebuild the system databases is as follows:

setup.exe
/QUIET
/ACTION=REBUILDDATABASE
/INSTANCENAME=instance_name
/SQLSYSADMINACCOUNTS= accounts
[/SAPWD=password]
[/SQLCOLLATION=collation_name]

Here are the details about how to use this syntax and how it works:

1. Find setup.exe either from your original media or the "local" setup.exe as found in the directory where you have installed SQL Server in the 100\Setup BootStrap\Release directory. So on my machine, I changed directory to C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release.
2. Run setup.exe with the following syntax from a Windows command prompt:
If you have SQL configured for Windows Authentication Mode use this syntax:
setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME= /SQLSYSADMINACCOUNTS=
where is either the name of your named instance or MSSQLSERVER for the default instance
are Windows groups or individual accounts to provision as sysadmin If you have SQL configured for Mixed Authentication Mode use the same syntax except you must also provide the /SAPWD parameter to specify the SA password. If you don't, you will get an error.
If you want to rebuild the system databases with a different collation than what you used to install SQL Server, you would need to supply the /SQLCOLLATION parameter. If you don't supply this parameter, then SQL Server will rebuild the system databases with the collation you selected when you installed SQL Server.
3. When setup has completed rebuilding the system databases, it will return to the command prompt with no messages (It always first prints out the version). If you have any syntax problems or issues with parameters you will see these errors in the command window. If you don't see any errors, then you will need to examine the "Summary" log file to verify it was completely successful.
4. If you immediately go to the directory where logs are stored for setup (100\setup bootstrap\logs), you can open up a file called Summary.txt. This file represents the most recent summary of any execution of setup. If you run setup for any other reason after rebuilding the databases before you look at the summary.txt file you will have to look for a folder inside the logs directory that matches the datetime when you run setup to rebuild the system databases. This may not be something that is simple to do if you have run setup several times so a tip here is to use findstr.exe from the command prompt like the following:
findstr /s RebuildDatabase summary*.*
This search will point you to any summary files that are for rebuilding system databases because this is the string printed in the Summary setup log file when using setup for this purpose. Here is an example "top portion" of a Summary setup log file when rebuilding system databases was successful:

Overall summary:
Final result:
Passed
Exit code (Decimal): 0
Exit message: Passed
Start time: 2008-08-29 08:09:10
End time: 2008-08-29 08:10:25
Requested action: RebuildDatabase

Here are a few interesting notes to consider about how this feature works:
You don't need your DVD anymore!
A new feature different from previous versions of SQL Server is that the system databases files we use to rebuild the current system databases do not come from the original installation media. So where do they come from? The come from the installation folder on your local computer in BINN\templates. On my machine where I installed SQL Server 2008 RTM on a clean machine with the default instance, this full path was at:
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\Templates
In this directory you will find master, model, and msdb database and log files that were copied from your installation source as part of setup.
So the logic in rebuilding the system databases is to get these files and copy them into your DATA directory to obtain your new system databases. This is a very nice enhancements for rebuilding system databases because you are not required to have your DVD or original install media.
However, If these files are missing, then setup will fail and your summary log may look something like this:
Overall summary: Final result: Failed: see details below Exit code (Decimal): -2068643839 Exit facility code: 1203 Exit error code: 1 Exit message: Failed: see details below Start time: 2008-08-29 08:30:42 End time: 2008-08-29 08:31:32 Requested action: RebuildDatabase
Later down in the summary log file you will see the details like the following example:
Detailed results: Feature: Database Engine Services Status: Failed: see logs for details MSI status: Passed Configuration status: Failed: see details below Configuration error code: 0x8C77B9A8@1306@25 Configuration error description: The file C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\Templates\master.mdf is missing. Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20080829_083014\Detail.txt
The details show the problem. master.mdf is missing from the templates directory. The way to resolve this is to either
1) Use the Repair feature of Setup (Available from the Maintenance option of the SQL Server Installation Center installed your machine)
2) Manually copy the necessary file(s) yourself. On your media source find the directory of your platform (x86, x64, or ia64). Then go to the following directory:
setup\sql_engine_core_inst_msi\PFiles\SqlServr\MSSQL.X\MSSQL\Binn\Template
Once you have copied the file into the templates directory, re-run setup with the syntax I've described above.
What about the Resource Database?
This feature does not attempt to restore the resource database files mssqlsystemresource.mdf or mssqlsystemresource.ldf. In fact, these files are considered part of the software so are now installed in the BINN directory. Therefore, to rebuild these database files you would need to run Repair from the Installation Center.

Backup Database script

2009-03-17T16:32:00.002+04:00

Below script describe how to backup database and output file include current date

DECLARE @filename NVARCHAR ( 500 )
SET @filename = 'C:\ABC_' + CONVERT(NVARCHAR, GETDATE(),112) + '.BAK'
BACKUP DATABASE ABC TO DISK = @filename

Happy code ;)

What's New in PerformancePoint SP2?

2008-12-28T15:02:00.003+04:00

Planning
Support for Windows Server 2008 Hyper-V™
You can now use PerformancePoint Server 2007 SP2 with Windows Server 2008 Hyper-V.
Hyper-V creates new opportunities for server virtualization. You can use Hyper-V to make more efficient use of system hardware and host operating system resources to reduce the overhead that is associated with virtualization.
For more information, see the PerformancePoint Server 2007 Hyper-V guide.

Monitoring

You can now use SQL Server 2008 with PerformancePoint Server 2007 SP2.
Important: To use SQL Server 2008 with PerformancePoint Server, you must install PerformancePoint Server 2007 SP2 before you install SQL Server 2008.
You can now use the Show Details action on PerformancePoint reports that use data that is stored in SQL Server 2008 Analysis Services.
Show Details enables dashboard consumers to right-click in a cell or on a chart value and see the transaction-level details for that value.
PerformancePoint Server 2007 with SP2 now supports Windows Server 2008 Hyper-V.
Hyper-V creates new opportunities for server virtualization. You can use Hyper-V to make more efficient use of system hardware and host operating system resources to reduce the overhead associated with virtualization.
For more information, see the PerformancePoint Server 2007 Hyper-V guide (http://go.microsoft.com/?linkid=9639690).
You can now use Dashboard Designer on a computer that us running .NET Framework 3.5 alongside .NET Framework 2.0.
You must install .NET Framework 2.0 before you install .NET Framework 3.5.
You can now use PerformancePoint Server with domains that have apostrophes in their names
In previous versions of PerformancePoint Server, when a domain name included an apostrophe, the configuration tool failed for both Planning Server and Monitoring Server.
Scorecard key performance indicator (KPI) queries are improved.
Timeout errors no longer occur with scorecard key performance indicators (KPIs) that use data that is stored in SQL Server 2005 Analysis Services.
Time Intelligence Post Formula filters now display the correct number of days for each month.
In previous versions of PerformancePoint Server, the calendar control for Time Intelligence Post Formula filters sometimes displayed 31 days for each month. This is no longer the case.
Time Intelligence filters now work on scorecard KPIs that use data that is stored in Analysis Services
In previous versions of PerformancePoint Server, some Time Intelligence expressions caused filters that were linked to KPIs to fail.
For example, when a compound expression such as (Day-7:Day-1) was used in a Time Intelligence Post Formula filter and that filter was linked to a KPI, an error message occurred. In PerformancePoint Server 2007 PS2, single and compound Time Intelligence expressions work with KPIs that use data that is stored in Analysis Services.

Download SQL Server 2005 Service Pack 3 - CTP

2008-11-02T19:36:00.001+04:00

The CTP version of SQL Server 2005 Service Pack 3 (SP3) is now available. You can use these packages to upgrade any of the following SQL Server 2005 editions:

Enterprise
Enterprise Evaluation
Developer
Standard
Workgroup

For a summary list of What’s new in SQL Server 2005 SP3 CTP, review the What’s New document.

These packages have been made available for general testing purposes only. Do not deploy the CTP software in production.

Download SQL Server 2005 Service Pack 3

SQL Server 2008 SSMS Enhancements - Part I

2008-07-30T13:14:00.007+04:00

I have just installed SQL Server 2008 RC0 and had been playing a bit with the new Management Studio. I found a few interesting features. I will try to cover some of those features in this post.

The new cool splash screen SQL Server 2008 has got an all new logo and splash screen.

Activity Monitor
The next feature that attracted me is the new "Activity Monitor".

It opens a window that displays the server activities.

What I found most useful is the section that shows "Recent Expensive Queries". One of the tasks that I do quite often is the optimization of "bad" queries or even "well written" queries that does not perform well enough. This panel of the activity monitor seems to be a good tool which readily shows me the stuff that I need to work on.
Missing Index hints in Graphical Execution Plan
Another change that I noticed instantly is the addition of Missing Index Hints in the query execution plan.

You could right click on the index hint and it will show a context menu that will take you to the details of the missing index.

Upgrade live applications to SQL Server 2005 for high availability

2008-07-24T11:32:00.004+04:00

Most companies have the luxury of upgrading their applications in place. But for many industries such as credit card, banking, shopping and gaming companies, the prospect of downtime is unacceptable. Imagine eBay/PayPal taking the system down for even one hour for an upgrade. The costs quickly escalate into the millions in just lost immediate revenue, not to mention the customers that would be lost to competitors.

The challenge

Your boss has told you that the ordering application has to be upgraded to version 10 with less than 15 minutes downtime. The database in question is 4+ TB and takes 16 hours to upgrade without any traffic running to it.

Reality check
The best laid ideas often fail because the scope of the project is not set early on and strictly adhered to. You will probably need to focus on a few key application areas that are critical to convert live. In eBay's case, it might say that, throughout the course of the upgrade, activities such as placing auctions, bids, payments and so on have to be live, but it's acceptable if you can't provide feedback or account merges. Try to avoid converting any data live, but keep each functional unit (block of tables) intact and transfer them live.

Hypothetical process
What process you pick, is heavily dependent on your budget. In this hypothetical scenario, we'll use two clusters with two nodes each. But you could easily simulate this scenario using two SQL Server instances on the same physical (or cluster) box or virtual servers running something like VMware's ESX. Let's examine the figure below.

Using two clusters and two nodes for live application upgrade.

Stage 1
In stage 1, we have the live data flowing into the current version of the database application. We created a second conversion database on Cluster 1 that contains any tables we want to convert over to the upgraded database – while the application is live. The most important part at this point is determining how to maintain consistency in the tables. The most common method is saving off of the current date/reference number for all conversion data at the time you want to start converting. Then continually you'd use a job that fires off stored procedures to bring over dates/reference numbers greater than the initial numbers you saved off. You could use other methods, such as triggers or Service Broker, but both would involve modifications to the original database schema and increase your risk and chance of making a mistake.
Stage 2
Stage 2 is a second physical cluster (or second instance/virtual server, depending on budget). As far as the schema goes, the conversion database on Cluster 2 exactly matches the conversion database on Cluster 1.
As the application pushes live data into the Order App DB (V9), the SQL Agent job on Cluster 1 fires off stored procedures that move the data into the conversion database on Cluster 1. Once the data reaches the conversion database, you can set up a replication publication on Cluster 1 for the conversion database that has a subscription running on Cluster 2. This causes any data pushed into the conversion database to transfer almost immediately into the conversion database on Cluster 2.
As the application is live pushing data into Cluster 1's version 9 database and is replicated over to the conversion database on Cluster 2, you can run the database upgrade process on Cluster 2's Order App DB – which you already restored from production at some point. Regardless of the time the database upgrade takes to run, the live data will be flowing into the conversion database on Cluster 2. Once the database upgrade is complete, you can fire off the SQL Server Agent job on Cluster 2; this fires off stored procedures that convert the version 9 data tables in the conversion database into the upgraded version 10 database sitting on Cluster 2. At this point, you'll have a chance to do any necessary configuration/maintenance to the upgraded version 10 database.
Stage 3
Once the conversion database on Cluster 2 is close to empty and the clients are upgraded to version 10, it's time to simply turn off Cluster 1 and point all the clients to Cluster 2, which already have all the critical live data transferred. A quick method of switching clients to version 10 would be SMS or SoftGrid (application virtualization), or some other method of automatically deploying a new version of a client en masse to a large client base. This would allow you to deploy version 10 applications within 15 minutes and they'd point to the freshly upgraded version 10 database on Cluster 2.

Summary
Upgrading an application while it is live requires that a lot of custom code be written to ensure the tables are consistent between the clusters (servers or instances). Test the upgrade scenario/scripts out several times, verifying reports at all the various stages to make sure the upgrade flows like clockwork. Verifying the various conversion database processes and jobs and documenting and ensuring consistency is fairly boring work, but it will pay off in the end when the upgrade flows smoothly. It beats working out issues within the user's sight.

Configure IBM Cognos 8.3 with IIS 7 and Windows Vista

2008-07-22T11:22:00.003+04:00

One of the common problems I has facing is Configure IBM Cognos 8.3 with IIS 7 and Windows Vista for that I summarize the configuration in the following steps hoping be useful

1. In IIS7 goto your device name
2. There in the center screen open ISAPI and CGI RESTRICTIONS
3. On the right screen click add
4. the path: %windir%\system32\inetsrv\cgi.dll (if you dont have this file, install it from the 'turn windows features on/off) where you can find it under the IIS component.
5. Name this thing something like 'dot cgi'
6. Allow it to execute -> done
7. Now click on the right screen 'edit feature settings' and allow cgi (I allowed both cgi and isapi )
8. Click on your device name again and in the center screen open 'Handler mappings'
9. Add module mapping
10. Request path: *.cgi
11. Module: CgiModule
12. Name: dot.cgi
Now you can go to open welcome page and start navigate :http://"ServerName"/Cognos8

Warning: do not add cgi as MIME type

SQL Server Audit in SQL Server 2008

2008-07-15T12:51:00.005+04:00

SQL Server 2008 February CTP introduces a new feature, SQL Server Audit. This feature facilitates auditing of Database Engine events by providing simple T-SQL statements to enable, store, and view audits on server and database objects. Compared to auditing with SQL Server trace files, using SQL Server Audit is much easier and cleaner. SQL Server Audit is implemented in the SQL Server Extended Events infrastructure. Extended events are created by the T-SQL statements to record the occurrences of the auditable actions.

To implement SQL Server audits, you first need to create a SQL Server Audit object. This object collects server and/or database-level actions and groups of actions that you want to monitor. It is created at the SQL Server instance level. You can have multiple SQL Server Audit objects per SQL Server instance. In the SQL Server Audit object, you specify the location for the output of the audit results. The results can be written to a binary file, the Windows Application Log, or the Windows Security Log. The file or the log is the Target component and the consumer of the associated extended events. Then you create Server or Database Audit Specification objects that belong to the SQL Server audit object. A Server Audit Specification collects server-level actions grouped in server audit action groups. It cannot collect individual actions.

However, a Database Audit Specification collects both database-level audit action groups and individual actions.

In Part I of this series, we will focus on the server level events. Server audit action groups record logon and logoff operations, server principal changes, server and database role membership changes, database maintenance operations, server object changes, database object changes, server and database statement and object permission changes. Any database related action groups are collected on all the databases on the server instance.

For example, DATABASE_ROLE_MEMBER_CHANGE_GROUP in a Server Audit Specification collects any actions that add or remove a login from a database role in any database on the server. If you want to audit only one database, you will need to define a Database Audit Specification in that specific database.
As part of the auditing requirements, we would like to monitor server logins and their server-level permission changes. To monitor login additions and deletions, we can include the SERVER_PRINCIPAL_CHANGE_GROUP audit action group in our server audit specification. Server-level permissions can be changed by issuing GRANT, REVOKE, or DENY, or through assigning server role memberships. Therefore, we can include the SERVER_PERMISSION_CHANGE_GROUP and SERVER_ROLE_MEMBER_CHANGE_GROUP audit action groups.
In this script, we write the audit to the Application Log.

/* Create a SQL Server Audit Object that writes the audit results to the Windows Application Log every one second.
If the write fails, the instance continues running without stopping. */

CREATE SERVER AUDIT MyServerAudit
TO APPLICATION_LOG
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

/* Create a Server Audit Specification object for the server audit.
This object include three audit action groups related to server principal changes. */

CREATE SERVER AUDIT SPECIFICATION MyServerAuditSpecification
FOR SERVER AUDIT MyServerAudit
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP);

/* By default, both the audit and audit specification are created in the disabled state.
We need to enable them before using them to record actions. */
ALTER SERVER AUDIT SPECIFICATION MyServerAuditSpecification
WITH (STATE = ON);
ALTER SERVER AUDIT MyServerAudit
WITH (STATE = ON);

You can confirm that the server audit object and the server audit specification object have been created by selecting from the system views shown below.SELECT * FROM sys.server_audits

SELECT * FROM sys.server_audit_specifications
To confirm that an extended event session has been created, we can select from the extended events dynamic management views.select s.name as [session name], se.event_name as [event name], p.name as [package name], t.target_name as [target name]
from sys.dm_xe_sessions s
join sys.dm_xe_session_events se on s.address = se.event_session_address
join sys.dm_xe_packages p on se.event_package_guid = p.guid
join sys.dm_xe_session_targets t on s.address=t.event_session_address

As we can see above, an extended event session called MyServerAudit$A was created. It includes the audit_event from the SecAdudit package. The audit is written to Application Event log asynchronously.
Let’s test this script by adding a login called testlogin. Grant the View Server State permission to the login, and add it to the dbcreator role.CREATE LOGIN testLogin WITH PASSWORD = 'Welcome123'
GRANT VIEW SERVER STATE TO testLogin
EXEC sp_addsrvrolemember 'testLogin', 'dbcreator'
DROP LOGIN testLogin

We can examine the Application Log by running the Windows Powershell command below.

Get-EventLog -logname "application" -newest 4 where {$_.Source -eq 'MSSQLSERVER' } select EntryType, EventID, Message, TimeGenerated format-list

Integrating Telephony Services Into .NET Applications

2008-07-01T11:00:00.002+04:00

Software developers have had limited access to telecom networks and capabilities. Specialized computer-telephony integration protocols are too complex and inflexible to properly link telephony services into enterprise business applications. As such, integrating person-to-person calling, conferencing, and call routing capabilities such as find me-follow me into applications has always been the preserve of specialized developers. As organizations continually attempt to increase the efficiency of their business processes through communications, the inability to easily tie communications services into the fabric of daily operational processes has emerged as a serious obstacle to business profitability.

With the advent of next-generation IP communications, Session Initiation Protocol (SIP), and Web services, these barriers to interconnectivity are being deconstructed. IT and communications systems now share a common language. However, developers are still faced with a lack of easy-to-use development tools to take advantage of this new interconnection capability. In particular, developers using Microsoft’s .NET Framework are challenged by the lack of tools to discover and incorporate telephony services into their business applications. SIP Objects.NET from Inova IT fills this void

you can download the white paper from below URL
http://lippisreport.com/2007/09/12/integrating-telephony-services-into-net-applications/

Search into database tables - Without Cursors

2007-11-14T02:20:00.000+04:00

Is there such a thing as a task where you would not need a cursor? Hidden in the depths of the master database are a series of stored procedures that can replace some cursors with these one-liners. Behind the scenes, cursors are still used, but they will save you tons of development time.
Traditionally if you wanted to run a DBCC CHECKTABLE on every table in a database you'd have to write an elaborate cursor like below :

create table #Result
(
tablename nvarchar(1000),
columnname nvarchar(1000),
searchvalue nvarchar(1000)
)

declare @searchstring as varchar(1000)
declare @searchscript as varchar(2000)
set @searchstring = 'Hossam'
set @searchscript =
'declare @sql as varchar(8000)
set @sql = ''select '''''''' tablename,'''''''' columnname,'''''''' value where 1=0''
select
@sql = @sql + '' union all select ''''?'''','''''' +
name + '''''',['' + name + ''] from ? where ['' + name + ''] like ''''%' + @searchstring + '%''''''
from
syscolumns
where
xtype in (175,239,231,167)
and id=object_id(''?'')
insert into #Result
Exec (@sql)
print ''search is completed on ?.'''
exec sp_msforeachtable @searchscript
select * from #Result
--drop table #Result

Microsoft Windows PowerShell and SQL Server 2005 SMO

2007-11-06T13:47:00.000+04:00

As you probably know, Windows PowerShell is the new command shell and scripting language that provides a command line environment for interactive exploration and administration of computers. In addition, it provides an opportunity to script these commands so that we can schedule and run these scripts multiple times.

Windows PowerShell depends on .NET framework 2.0. SQL Server Management Objects, known as SMO, is an object model for SQL Server and its configuration settings. SMO-based applications use .NET Framework languages to program against this in-memory object model, rather than sending Transact-SQL (T-SQL) commands to SQL Server to do so.

In this article series, I am going to illustrate the power of Windows PowerShell in conjunction with SQL Server 2005. Part I of this series is going to illustrate how to install and use a simple PowerShell command and a simple SMO command.
Assumption
a. The machine you use already has .NET 2.0 installed b. The machine you use already has SQL Server 2005 client installed with the latest service pack Download and Install Microsoft PowerShell.
Download Microsoft PowerShell “WindowsXP-KB926139-x86-ENU.exe” from http://download.microsoft.com/
b. Install PowerShell Step 1: Double click on the “WindowsXP-KB926139-x86-ENU.exe’ executable. [Refer Fig 1.0]

Fig 1.0

Launch PowerShell
There are few ways to launch PowerShell. One method is to go to the command prompt and type the following command. [Refer Fig 1.6]

After a short pause, the PowerShell prompt appears. [Refer Fig 1.7]

Alternatively, you can start PowerShell by selecting Programs-Windows PowerShell 1.0-Windows PowerShell. [Refer Fig 1.8]

Setting the default Button in ASP.NET

2007-10-30T15:53:00.000+04:00

to set default button in ASp.net 2.0 is to easy but in asp.net 1.0 i think every one will be facing the problem i hade faced.
i try to use many ways to solve this problem and finally solve it by the below Syntax put it in your page load and replace the text name with your text on the page and the button y need to make it the default button and happy code

txt_Search.Attributes.Add("onkeydown", "if(event.which event.keyCode){if ((event.which == 13) (event.keyCode == 13)) {document.getElementById('" + Me.btn_Search.UniqueID + "').click();return false;}} else {return true}; ")

:)

SQL Server 2008 New Featuers

2007-10-09T15:48:00.000+04:00

SQL Server 2008 contains many new features and enhancements, Additional information can be found at the main SQL 2008 Microsoft page: http://www.microsoft.com/sql/2008/default.mspx. Listed below is a concise bulleted list of the SQL Server 2008 features .

Transparent Data Encryption. The ability to encrypt an entire database.
Backup Encryption. Executed at backup time to prevent tampering.
External Key Management. Storing Keys separate from the data.
Auditing. Monitoring of data access.
Data Compression. Fact Table size reduction and improved performance.
Resource Governor. Restrict users or groups from consuming high levels or resources.
Hot Plug CPU. Add CPUs on the fly.
Performance Studio. Collection of performance monitoring tools.
Installation improvements. Disk images and service pack uninstall options.
Dynamic Development. New ADO and Visual Studio options as well as Dot Net 3.
Entity Data Services. Line Of Business (LOB) framework and Entity Query Language (eSQL)
LINQ. Development query language for access multiple types of data such as SQL and XML.
Data Synchronizing. Development of frequently disconnected applications.
Large UDT. No size restriction on UDT.
Dates and Times. New data types: Date, Time, Date Time Offset.
File Stream. New data type VarBinary(Max) FileStream for managing binary data.
Table Value Parameters. The ability to pass an entire table to a stored procedure.
Spatial Data. Data type for storing Latitude, Longitude, and GPS entries.
Full Text Search. Native Indexes, thesaurus as metadata, and backup ability.
Reporting Server. Improved memory management.
SQL Server Integration Service. Improved multiprocessor support and faster lookups.
MERGE. TSQL command combining Insert, Update, and Delete.
SQL Server Analysis Server. Stack improvements, faster block computations.
SQL Server Reporting Server. Improved memory management and better rendering.
Microsoft Office 2007. Use OFFICE as an SSRS template. SSRS to WORD

Documenting a Microsoft SQL Server database

2007-10-09T14:42:00.000+04:00

This article describes how to document your Microsoft SQL Server database. The principles are similar for all versions of SQL Server, although SQL Server 2005 and above contain a number of neat enhancements to database documentation capabilities. The article includes database naming conventions, adding object descriptions, documenting stored procedures, and how to create technical documentation for your SQL Server databases.

Much of this article can also apply equally to other relational databases such as Oracle, MySQL, Microsoft Access and PostgreSQL.

Database Naming Conventions
If you're designing a database from scratch, then it makes sense to follow a logical database entity naming convention. The following are some suggestions for when naming the objects in your new database.

General naming conventions
Database names should if possible use alphanumeric characters only. Avoid using hyphens as they can subsequently make it difficult to write certain Transact SQL queries!

Where possible, try to avoid using spaces in database entities, particularly in table names and column names. While Transact SQL can still refer to these entities if they are enclosed in square brackets, it can sometimes lead to confusion and coding errors.

It is also a good idea to avoid using table and column names that are reserved words in Transact SQL, such as month, year or user.

Table naming conventions
Tables should be given names that relate to the data stored within them. For example, employee data should be stored in a table called Employees. Note that the plural form of the word is used, as there will more than likely be more than one employee stored in the table.

Some developers prefix table names with something like t_. Such prefixes can be particularly useful if the tables are referenced from application source code, as it makes it more obvious to the software development team that a table rather than a view or some other entity is being referenced.

Giving a table a prefix related to its function (e.g. Payroll_ can help to group tables into related categories. SQL Server 2005 introduces the concept of schemas. This allows tables to be grouped accordingly. For example, the AdventureWorks sample database contains a HumanResources schema, and the associated tables (Employee, EmployeeAddress, EmployeeDepartmentHistory etc.) are all listed under this schema in the SQL Server Management Studio table list.

Foreign key naming conventions
It is particularly useful to be able to identify the foreign keys within a database table. Prefixing them with something like fk_ makes it much more straightforward to identify table relationships just by looking at the table's columns.

Stored Procedure naming conventionsIt is always useful to name stored procedures according to their use. For example: GetUserID, InsertDateOfBirth or UpdatePaymentInfo.

On occasions, it is useful to add a suffix to show the stored procedure's input parameters. For example the stored procedure GetUserIDByUserNameAndPassword will return take a user name and password as input parameters and return a user ID . This can be used to differentiate stored procedures that have similar functionality but have different parameters. This would allow other related stored procedures to be added, e.g. GetUserIDByGUID and GetUserIDByApplicantID. The downside to this naming convention is that the stored procedure names can become quite long.

Some developers prefix all their stored procedures with certain tags. A popular convention is to name a stored procedure with a sp_ prefix. However, this is not recommended best practice for two reasons. Firstly, there is a slight reduction in database performance, as the SQL Server will check for stored procedures with this prefix in the master database first. This performance reduction is small, but it may be significant in high end enterprise systems. Secondly, since Microsoft uses this prefix for system stored procedures, there is always the chance that you could give your own stored procedure the same name as a system stored procedure. It is also possible that a future version of SQL Server could introduce a new system stored procedure with the same name as one of your existing user stored procedures.

If you do want to prefix your stored procedures then it is recommended to use something like usp_ or sproc_. Likewise a function could be prefixed with something like func_. Such prefixes can be particularly useful if the stored procedures are called from application source code, as it makes it more obvious to the software development team that a stored procedure is being called.

Documenting SQL Stored Procedures
Don't forget to document the Transact SQL code of stored procedures and functions. While simple queries should be self explanatory, larger queries will benefit from documentation. Stored procedures will also benefit from a standard header, which at the very least should describe the procedure's functionality. Including a change log will also help to track changes, particularly if you don't have any source control system in place.

An example header for a stored procedure is shown below:

/*
Description: Gets a user's UserID
Author: Brett Burridge
Create Date: 14/09/2007
Param: @UserName = User's login name
Param: @Password = User's password
Return: UserID of the user
Modified Date: 10/10/2007
Modification: Added to check to see if their account has been suspended
*/

Note that adding comments to stored procedures has no affect on their performance.

Database Object Descriptions
Being able to give database objects descriptions goes some way towards being able to create a self-documenting database.

SQL Server 7.0 introduced the useful ability to add a Description to a table through the table design window. SQL Server 2000 enhanced this functionality by introducing extended properties. Unfortunately the SQL Server 2000 Enterprise Manager has limited capabilities for allowing these properties to be edited. However, in SQL Server 2005 the SQL Server Management Studio GUI allows extended properties to be edited. Most objects in a database (e.g. tables, table columns, views, functions, stored procedures, the database itself) have extended properties that may be edited. By default there is only a single extended property, MS_Description. Even more limiting is that although you can use the MS_Description extended property to add descriptions to objects, without 3rd party add-ons such as our SQL Documention Tool, there isn't actually a lot you can do with them once entered.

Creating Database Technical Documentation

Asides from creating database diagrams, there isn't a lot of functionality within SQL Server that allows you to create technical documentation for your database.

Fortunately there are timesaving 3rd party SQL Server documentor tools that will automatically create comprehensive technical documentation for SQL Server databases. Our SQL Documentation Tool was launched in 2005, and is a low cost option for quickly creating technical documentation for SQL Server databases with the minimum of effort.

For applications that use SQL Server databases, there are utilities that will create technical documentation for that application plus its associated SQL Server databases. Examples of this are our ASP Documentation Tool (for ASP VBScript and JScript and associated databases), .NET Documentation Tool (for .NET Framework C# and VB.NET code and associated databases), VB Documentation and their associated databases), and PHP Documentation Tool (code documentor for PHP web applications and associated databases).

Monitoring Stored Procedure Usage

2007-07-11T15:41:00.000+04:00

I was attending a SQL Server event a few weeks back when a number of DBA types were talking about new features in SQL Server 2005. One of the topics that came up was how might you monitor execution of stored procedures. One DBA said it would be nice if he had a method to identify how many times a stored procedure (SP) was executed. He was looking for some information to help fine tune his environment and the applications running in his environment. Or at least identify the processes that where run frequently so they could be reviewed by developers to determine if they could be written more efficiently. So what new features in SQL Server 2005 might be used to accomplish the monitoring of SP executions? Dynamic Management views (DMV) of course. In this article I will show you how you can use a few DMV’s to identify the most frequently run SPs of an instance of SQL Server, as well as those SPs that use the most CPU, I/O or run the longest.

Execution Statistics
It is relatively easy to identify the use count and resource usage of your SP’s, but first let me discuss how SQL Server maintains the execution statistics. The SQL Server engine keeps execution statistics for each SQL Server 2005 instance. Execution Statistics are an accumulation of execution information about what has been run since the instances has started. Each time an instance is stopped and restarted all execution statistics are reset.

Individual execution statistics for an object are tied to cached execution plans. When a SP is compiled, an execution plan is cached in memory. These cached execution plans are uniquely identified by a plan_handle. Since memory is limited, SQL Server from time to time will remove execution plans from memory, if the cached plan is not being actively used. Therefore the statistics stored for a particular SP may be for an accumulation of stats since SQL Server started, if the SP has only be compiled once. Or, it may only have statistics for the last few minutes if the SP had recently been compiled.

How to Get the Execution Count of a Stored Procedure?
To determine how many times a stored procedure in the cache has been executed you need to use a couple of DMV’s and a dynamic management function (DMF). The plan_handle for the cached plans are used to join together the DMVs and retrieve records for a DMF. To get the execution counts for each cached SPs you can run the following code:

SELECT DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
,OBJECT_NAME(st.objectid,dbid) StoredProcedure
,sum(qs.execution_count) Execution_count
FROM sys.dm_exec_cached_plans
cp join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid),
OBJECT_NAME(objectid,st.dbid)
order by sum(qs.execution_count) desc
Here I used the plan_handle of the “sys.dm_exec_cached_plans” DMV to join with the “sys.dm_exec_query_stats” DMV to obtain the “Execution_count” value where the object type is a store procedure. The “execution_count” column of the “sys.dm_exec_query_stats” DMV identifies the number of time the cached_plan (or SP) has been executed since the last compilation of the SP. I use the plan_handle in conjunction with the CROSS APPLY operator to return the object information (DBName, SchemaName, and ObjectName) using the table-value DMF “sys.dm_exec_sql_text”. The output from this SELECT statement is ordered by the execution_count, so the SP with the most executions will be displayed first.

Determining Which SP is using the Most CPU, I/O, or has the Longest Duration.
Knowing which SPs are frequently executed is useful information, although from a performance standpoint you might like to know which SP is consuming the greatest amount of CPU resources. Or possibly you might be interested in which SP takes the longest to run, or which SP performs the most physical I/O operations. By modifying the above command, we can easily answer each one of these questions.

If you want to show the SP that consumes the most CPU resources, you can run the following TSQL command:

SELECT DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
,OBJECT_NAME(st.objectid,dbid) StoredProcedure
,sum(qs.execution_count) Execution_count
,sum(qs.total_worker_time) total_cpu_time
,sum(qs.total_worker_time) / (sum(qs.execution_count) * 1.0) avg_cpu_time

FROM sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)
order by sum(qs.total_worker_time) desc

The “sys.dm_exec_query_stats” view contains the column “total_worker_time”, which is the total number of microseconds that a given cached query plan has executed. Keep in mind that cached plans are sometimes removed from memory and replaced with newer ones. Therefore, the statistics for which SP has consumed the most CPU only takes into account statistics for those plans that are in the cache when this T-SQL is run.

To determine which SP has executed the most I/O requests you can run the following TSQL code:

SELECT DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName
,OBJECT_NAME(objectid,st.dbid) StoredProcedure
,sum(execution_count) execution_count
,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) total_IO
,sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) / sum(execution_count) avg_total_IO
,sum(qs.total_physical_reads) total_physical_reads
,sum(qs.total_physical_reads) / (sum(execution_count) * 1.0) avg_physical_read
,sum(qs.total_logical_reads) total_logical_reads
,sum(qs.total_logical_reads) / (sum(execution_count) * 1.0) avg_logical_read
,sum(qs.total_logical_writes) total_logical_writes
,sum(qs.total_logical_writes) / (sum(execution_count) * 1.0) avg_logical_writes
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)
order by sum(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) desc

Here I have displayed the total physical and logical read I/O’s, plus the logical write I/O’s. In addition, I have calculated the average number of I/O’s per execution of each SP. Physical reads are the number of reads that are actually made against the physical disk drives; where as logical reads and writes are the number of I/O’s against the cached data pages in memory in the buffer cache. Therefore, by adding the physical and logical I/O’s together I was able to calculate the total I/O’s for each SP.

To determine which SPs take the longest time to execute I can use the follow TSQL code:

SELECT DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName
,OBJECT_NAME(objectid,st.dbid) StoredProcedure
,sum(execution_count) execution_count
,sum(qs.total_elapsed_time) total_elapsed_time
,sum(qs.total_elapsed_time) / sum(execution_count) avg_elapsed_time
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle
where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)
order by sum(qs.total_elapsed_time) descIn this TSQL, I am calculating the duration of each SP by summing up the “total_elapsed_time” in the “sys.dm_exec_sql_query_stats” DMV by database, schema and object name. I am also calculating the average elapsed time per execution of each SP. I order the output so the SP that took the longest total duration will be displayed first. If you where interested in determining the SP that had the longest average duration all you would need to change is the “ORDER BY” clause to sort by “avg_elapsed_time”.

ConclusionThe “sys.dm_exec_query_stats” SP is used to look at the accumulated statistics for cached plans. By joining the “sys.dm_exec_query_stats” view with other DMVs, you can determine other information about the cached plan, like the object type for the cached plan, and the actual name of the object. Having these DMVs in SQL Server 2005 now provides you with some data mining tools to review some performance information for a SQL Server instance. Next time you want to review statistics related to your code run on your SQL Server box consider looking at the information available in the SQL Server 2005 dynamic management views.

What to consider when testing databases

2007-06-30T12:27:00.000+04:00

What To Test in a Relational Database

Last month I explored the assumptions made by the traditional data management community and argued that these assumptions had been shown to be false over the years. These assumptions included the belief that you can't easily evolve a database schema, that you need to do detailed up front modeling, and that reviews and inspections are an effective way to ensure data quality. Respectively, I argued that database refactoring enables you to easily evolve database schemas, that an agile approach to data modeling is significantly more effective, and that database regression testing is the best way to ensure data quality. A debate on the Agile Databases mailing list ensued and it quickly became apparent that the traditionalists could understand, although often not accept, the first two agile database development techniques but clearly struggled with the concept of database testing. I believe that the virtual absence of discussion about testing within the data management community is the primary cause of the $611 billion annual loss, as reported by The Data Warehouse Institute, experienced by North American organizations resulting from poor data quality. So this month I've decided to describe what you should consider testing in a database.

Let's start with some terminology. Database testing is the act of validating the contents, schema, and functionality within a database. From the point of view of a relational database this includes the actual data itself, the table structures and relationships between tables, and the stored procedures/functions or database classes respectively. Database interface testing validates the database at the black-box level whereas internal database testing validates it at the clear-box level -- if any database testing occurs at all it is typically at the interface level only because of the lack of tool support for internal testing. Database regression testing is the act of running the database test suite on a regular basis, ideally whenever someone does something which could potentially inject a defect into the database such as change how they write data into a database or change some of the code within the database itself. Test Driven Database Development (TDDD), also known as "Behavior Driven Database Development" (BDDD), is the act of specifying the design of a database by writing a single test then just enough database code/schema to fulfill that test.

I think that one of the reasons why data professionals are confused about the concept of database regression testing is because it is a relatively new idea within the data community. One of the assumptions that I didn't cover last month is the idea within the traditional data community that testing is something that other people do (i.e., test or quality assurance professionals). This reflects a penchant for over-specialization and a serial approach towards development by traditionalists, two ideas which have also been shown to be questionable organizational approaches at best.
The easiest thing to get your head around is the need to validate the logic implemented within a database. Relational databases contain code, in the form of stored procedures, triggers, and even object-oriented classes. There is nothing special about this code. Just like you test application code, shouldn't you also test database code? Of course you should. You'll apply the exact same types of tests to database code as you would to application code.
What isn't as obvious, at least from the questions I was getting from traditional data professionals, was the need to validate data quality via testing. As DDJ's data quality survey showed last year, data is considered a corporate asset by 96 percent of organizations yet less than half have any sort of testing strategy in place to actually ensure data quality. In short, people like to talk about data quality but not act on it. When it comes to data you could validate the following via tests: Column domain value rules. For example, the Flavor column has allowable values of Chocolate, Vanilla, and Strawberry.
Column default value rules. For example, the default value is Strawberry.
Value existence rules. For example, there should always be a value of Flavor indicated (it can never be null).
Row value rules. For example, the value of StartDate must be less than EndDate when EndDate is provided.
Size rules. For example, a code in a column must always be two characters in length or a value in a VARCHAR column must be at least five characters in length Although these data rules can be implemented via constraints, or via other means, you still need to test to ensure that the rules are being implemented properly. Constraints can easily be dropped or reworked, therefore you should have regression tests I place to validate them. Nullability is critical to test for because a NOT NULL constraint can also easily be dropped. Furthermore, "quasi-nulls" such as empty strings are often not allowed so supporting tests should be in place to ensure this.
Table structure can also be easily validated, something that is typically done as a side effect of the tests to validate the Create Read Update and Delete (CRUD) logic of an application. These tests will break whenever you change the database schema without also changing the access code. From a database design point of view, as you write CRUD tests you are effectively designing the table structure which supports those tests.
You can also write tests which validate relationships between the rows in different tables. These tests validate referential integrity (RI) rules, for example if a row in the Employee table references a row within the Position table then that row should actually exist. RI rules such as this are typically implemented as triggers, but what happens if someone drops or modifies a trigger without understanding the implications of doing so?
You may also choose to write database performance tests to both specify performance requirements and to ensure that those requirements are met. From a black-box point of view you might write tests which validate the performance characteristics surrounding database access, including object/relational (O/R) mapping logic. From a clear-box point of view you might have tests which motivate you to maintain secondary indices to support common database access paths or to refactor your database tables into structures which are more performant.
I believe that as an industry we have a lot of work ahead of us with respect to data quality. We have known for decades that testing, particularly regression testing, leads to greater quality yet for some reason we haven't applied this knowledge to relational databases. There are many things which can and should be tested within a relational database, this column touches on just a few, and it is about time that we step up and develop new tools and techniques to do exactly that. The Agile community has lead the way to bringing regression testing, and more importantly test-first development, to application programming. This has lead to noticeable increases in quality, time to market, and return on investment (ROI). We can achieve the same successes when it comes to database design and quality.

Technical Blog Hello World :)

2007-06-27T14:57:00.000+04:00

Hello everybody,

This is my technical blog, here you'll find my technical posts (as soon as I understand everything here and have the ability to write technical articles :D).

So, welcome in my technical blog and which you to enjoy your time here and be more useful for any one :)