The Techie's world....

Conditional Page break in SSRS

2009-05-10T12:13:00.000-07:00

Conditional page breaks in SSRS are always something developer needs to work on in order to get it done. This is especially true when you need to render the same report in PDF.

Recently I need to implement the same and did that with a quick trick. Here is the sample SELECT I am using to demonstrate the same.

select 'Record 1' as record, 0 as PageBreak, 1 as grp
UNION ALL
select 'Record 2' as record, 0 as PageBreak, 1 as grp
UNION ALL
select 'Record 3' as record, 0 as PageBreak, 1 as grp
UNION ALL
select 'Record 4' as record, 1 as PageBreak, 2 as grp
UNION ALL
select 'Record 5' as record, 0 as PageBreak, 3 as grp
UNION ALL
select 'Record 6' as record, 0 as PageBreak, 3 as grp
UNION ALL
select 'Record 7' as record, 1 as PageBreak, 4 as grp
UNION ALL
select 'Record 8' as record, 0 as PageBreak, 5 as grp

"Record" column is just to show unique records. The most important column here is "PageBreak", which is setting the conditional page break with "1". That means whenever there is "1" in "pagebreak" column you need a page break otherwise keep on displaying the records on same page.

The other very important column is "grp". Well, you really don't need this column and still will be able to implement conditional page break functionality. But then SSRS will club all the records which requires page break into 1 grouping set, probably in the end with this sample data. So, in order to maintain the sequence of records we need to add a grouping column which will create a new group whenever it finds a different value in "pagebreak" column.

Now, lets move on to SSRS report. These steps will work in SSRS 2008. I am not adding standard steps of creating a data source and data set. I know you are all smart guys...

Simple enough, please follow these steps.

Add a table with 2 columns, record and pagebreak.
Now run the report and you will see all the records are coming in same page.
Next, add a parent group to this row, parent to "Details" group in "Row Groups". Please check "Image 1".
In the group expression select the "grp" column and the sorting, select "grp" in Sort by. Please check "Image 2".
MOST Important, select "Between each instance of a group" of the newly create group under "Page Break" tab. Please check "Image 3".

Yep, that's it, run the report now. And you will see 3 records on page 1, 1 record on page 2, 2 records on page 3 and 1 record on page 4 and page 5.

Image 1

Image 2

Image 3

There are also other ways of doing the same, but I find this one very easy to implement.

Don't forget to hide new group by just minimizing the width and you are good to go. Try to render the same in PDF, and it works like a charm....

- Mohit Nayyar

Data extrapolation on the fly

2008-09-27T18:53:00.000-07:00

In some cases we need to generate the data based on a starting date, to an end data (today-getdate()).

This process is called data extrapolation based on limited information (starting date). Here I am trying to achieve the same without storing data in temp./staging tables.

First I am creating a temp. table wih 2 rows and now I need to generate rest of the rows starting from given date to today's date.

Next step is to make use of CTE to generate sequence of rows and finally making use of CROSS JOIN to join the sequence with real data and get the desired output.

CREATE TABLE #TEMP (
MYNAME VARCHAR(10),
STARTDATE DATETIME)
GO

INSERT INTO #TEMP VALUES ('Mohit', '2008-09-15')
INSERT INTO #TEMP VALUES ('Nayyar', '2008-09-22')
GO

WITH FIRST2(SEQ)
AS (SELECT 1
UNION ALL
SELECT 1),

FIRST4(SEQ)
AS (SELECT 1
FROM FIRST2 X
CROSS JOIN FIRST2 Y),

FIRST16(SEQ)
AS (SELECT 1
FROM FIRST4 X
CROSS JOIN FIRST4 Y),

FIRST256(SEQ)
AS (SELECT 1
FROM FIRST16 X
CROSS JOIN FIRST16 Y),

FIRST65536(SEQ)
AS (SELECT 1
FROM FIRST256 X
CROSS JOIN FIRST256 Y),

SEQROWS
AS (SELECT ROW_NUMBER()
OVER(ORDER BY SEQ) AS SEQNUMBER
FROM FIRST65536 WITH (NOEXPAND))

SELECT MYNAME,
STARTDATE,
DATEADD(DAY,N.SEQNUMBER - 1,STARTDATE) AS EXTRAPOLATION
FROM #TEMP
CROSS JOIN SEQROWS N
WHERE N.SEQNUMBER <= DATEDIFF(DAY,STARTDATE,GETDATE())
ORDER BY MYNAME,
STARTDATE,
3
GO

DROP TABLE #TEMP
GO

Thanks
Mohit Nayyar

Things You Didn't Know About Temp Tables and Table Variables

2008-07-17T13:03:00.000-07:00

I recently found really useful information on temp. table and table variable usage. I am sure this will be useful to SQL Community as well.

http://www.sqlservercentral.com/articles/63472/

- Mohit

SSIS approach to handle Inferred Members

2008-05-29T11:02:00.000-07:00

While loading data in Fact tables we usually see a scenario where the fact data is available but there is no corresponding business key in the related dimension.

In this case we choose multiple options to resolve the issue.

Ignore that fact

Insert the associated business key in dimension table and return the newly generated surrogate key from dimension table. And now store the data in Fact table with the surrogate key.

The second approach relates to a term called “Inferred members”. All the other attributes of that dimension will also get updated in next run of dimension load (usually nightly load).

In SSIS there are multiple options available to implement the second case.

First approach is to do lookup on the dimension table and for all the rows that are now matching, insert the business key in Dimension table and then do the lookup again to get the surrogate key.

Second approach is to make use of Lookup and Script component. Lookup component will ignore rows with no matching business key in dimension table. Then script component will process only those rows where it didn’t find the associated surrogate key and finally insert the same in dimension table and return the associated surrogate key through stored procedure output parameter.

This script component approach is more efficient because its using the existing lookup component only once and then doing all the processing in script component.

But the additional benefit comes if we make use of .Net Generic.SortedDictionary class to store the cache information regarding the newly generated key. Read more about this here…
http://msdn.microsoft.com/en-us/library/f7fta44c(VS.80).aspx

- Mohit

Selective Cube Measure Groups processing using “Analysis Services Processing Task” in SSIS

2007-10-22T20:24:00.000-07:00

Sometime its not feasible to process the full cube, so it makes sense to only process the selective measure group in the cube.

This is especially true when you have really big cube which takes time to process and sometime we just need to load data for only few business metrics.

Here is the small example to process only selective measure groups using “SSIS Analysis Services Processing Task”.

I am assuming that the measure group names are stored in a variable and after that we need to create “Processing Commands” to process the cube.

Processing Commands: We need to set this property in “Analysis Services Processing Task” to process the measure groups. So I will be using “Script Task” to generate the command.

Just make sure that the “Delay Validation” property of “Analysis Services Processing Task” is set to “True”.

Follow these steps now……..

Get comma separated list of all measure groups needs to be processed in local SSIS variable let say “varMeasureGroups”. You can get this list from some sort of configuration, so no hard-coding ;)
Creates the command using “Script Task” and store the final command in local SSIS variable let say “varCubeCommand”
In “Analysis Services Processing Task” task set the property “Processing Commands” using expressions to the recently created variable named “varCubeCommand”.

That’s it………no need to process the full cube now………

Dim strSplitMeasureGroup As String(), i As Integer, strCmd As String

strSplitMeasureGroup = Split(Dts.Variables("User::varMeasureGroups").Value.ToString, ", ")

strCmd = "<Batch xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">"

For i = 0 To strSplitMeasureGroup.Length – 1

strCmd = strCmd & "<Process xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" "

strCmd = strCmd & "xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">" & Chr(13)

strCmd = strCmd & "<Object>" & Chr(13)

strCmd = strCmd & "<DatabaseID>myCube</DatabaseID>" & Chr(13)

strCmd = strCmd & "<CubeID>myCube</CubeID>" & Chr(13)

strCmd = strCmd & "<MeasureGroupID>" & strSplitMeasureGroup(i) & "</MeasureGroupID>"&chr(13)

strCmd = strCmd & "</Object>" & Chr(13)

strCmd = strCmd & "<Type>ProcessFull</Type>" & Chr(13)

strCmd = strCmd & "<WriteBackTableCreation>UseExisting</WriteBackTableCreation>" & Chr(13)

strCmd = strCmd & "</Process>" & Chr(13)

strCmd = strCmd & "</Batch>"

Dts.Variables("User::varCubeCommand").Value = strCmd

- Mohit Nayyar

UPDATE: SSIS - deadlock was detected while trying to lock variables

2007-09-23T14:46:00.000-07:00

Recently I found this issue of deadlock variables in SSIS and finally made two solutions

1. Instead of declaring variables in Script properties (ReadOnlyVariables / ReadWriteVariables), better to make use of Dts.VariableDispenser.LockForRead / Dts.VariableDispenser.LockForWrite in the script to lock variables

2. I also solved my problem by running child packages out-of-proc (ExecuteOutOfProcess=TRUE in Execute Package Task), this is more to do with script caching

But recently I saw something strange and found out that one of my script is making use of this type of code and I am getting this error again.....

"The script threw an exception: A deadlock was detected while trying to lock variables "variable names (comma separated)" for read access and variables "variable names (comma separated)" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out."

Dim var As Variables

Dts.VariableDispenser.LockForWrite("User::VarName")

Dts.VariableDispenser.GetVariables(var)

'Problem Line, Just remove the reference to Dts.Variables

Dts.Variables("User::VarName").Value = "SomeVal"

'Correct Line, make use of locally declared "var" Variable

var("User::VarName").Value = "SomeVal"

var.Unlock()

To solve the situation I made a quick fix and change my Dts.Variables to local variable collection I defined in the beginning "var".

As we can see in this script that we are locking one variable for write. But after using the GetVariables we MUST use "var" collection to write data and should NOT use Dts.Variables and that fixed my problem.

So I think this is some sort of double locking issue, like if I lock the variable using "Dts.VariableDispenser" and if I try to access the same variable again with Dts.Variables instead of "var" then SSIS tries to lock that again......

- Mohit Nayyar

SQL Query tool that generates HTML / PDF / Word / Excel output

2007-09-14T23:29:00.000-07:00

I just saw this new query tool from SQL Answers.

The beauty of this tool is to directly generate the output of any query into PDF / HTML / Excel / Word / CSV format. Please take a look here…....

http://www.sqlanswers.com/Software/SAQ/Default.aspx

This seems to be a lightweight tool to query multiple databases platforms like Microsoft SQL Server, Oracle, DB2, Microsoft Access and MySQL, but I just tested this for Microsoft SQL Server 2005 and I am really happy with this.

This makes things really easy when you need to send the output in PDF or HTML without any extra effort.

On the similar lines Microsoft also offers a “SQL Server Web Data Administrator” tool, which is very useful because of its basic web interface. You can find this here…….

http://www.microsoft.com/Downloads/details.aspx?familyid=C039A798-C57A-419E-ACBC-2A332CB7F959&displaylang=en

- Mohit Nayyar

DTS to SSIS migration

2007-09-13T09:49:00.000-07:00

Today I saw a new product coming soon to do DTS to SSIS migration called DtsXchange.

Looks promising and this is also capable to do something extra which built-in DTS migration doesn’t do like Dynamic Properties. Have a look here http://www.pragmaticworks.com/dtsxchange.htm

Users can also migrate existing DTS packages into SSIS using the FREE migration wizard provided by SQL Server 2005 but this wizard doesn’t cover complete DTS package. So in some cases manual effort is required, and SSIS doesn’t support some of the DTS features, so the user has to manually implement the functionality in new SSIS package.

You can find some known issues in migration here http://technet.microsoft.com/en-us/library/ms143462.aspx

But on top of this SSIS allows you to run the existing DTS package even without any change, using a wrapper called “Execute DTS 2000 Package Task” in the new SSIS package.

So if you don’t have the bandwidth to migrate the existing package then keep on running the old DTS packages in SSIS and start making new packages in SSIS.

Obviously you will miss the “All NEW SSIS” but it’s an easy work around. But I think you will miss a lot, because SSIS is NOT a new name of DTS but it's a truly enterprise level ETL tool. So its always better to migrate to SSIS as soon as possible.

- Mohit Nayyar

Get all user defined table information with RowCount and Size (space used)

2007-08-29T21:13:00.001-07:00

"sp_spaceused" procedure is really handy to get the updated information about space usage by database or tables.

I combined the same with another very useful procedure "sp_MSforeachtable" to get this information for all user-defined tables.

If you see any problem with the information then just change the last parameter and set it to TRUE (FALSE in the script). That will update the space usage and then report the updated details.

CREATE TABLE #temp

(

TableName                    NVARCHAR (128),

RowsCnt                       VARCHAR (11),

ReservedSpace             VARCHAR(18),

DataSpace                    VARCHAR(18),

CombinedIndexSpace VARCHAR(18),

UnusedSpace               VARCHAR(18)

)

EXEC sp_MSforeachtable 'INSERT INTO #temp (TableName, RowsCnt, ReservedSpace, DataSpace, CombinedIndexSpace, UnusedSpace) EXEC sp_spaceused ''?'', FALSE'

SELECT TableName, RowsCnt, ReservedSpace, DataSpace, CombinedIndexSpace, UnusedSpace

FROM #temp

ORDER BY TableName

DROP TABLE #temp

- Mohit Nayyar

SSIS - deadlock was detected while trying to lock variables

2007-08-27T22:33:00.000-07:00

Recently I faced one MAJOR issue in my ETL packages because of new patches deployed on Microsoft Windows 2003 Server.

"A deadlock was detected while trying to lock variables "variable names (comma separated)" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out."

OR

"The script threw an exception: A deadlock was detected while trying to lock variables "variable names (comma separated)" for read access and variables "variable names (comma separated)" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out."

It's more to do with Script component/Task used in the package and making use of "ReadOnlyVariables" and "ReadWriteVariables" properties for declaring variables that will be used in the script.

Technically if we declare variables in these two properties then there is NO need to lock these variables in the script, and this is exactly what we were doing in the past prior to these patches....

Windows 2003 Post-SP2 Hotfix - MS07-31/935840 W2K3 Server
Windows 2003 Post-SP2 Hotfix - MS07-34/929123 W2K3 Server
Windows 2003 Post-SP2 Hotfix - MS07-039/926122 W2K3 Server
Windows 2003 Post-SP2 Hotfix - KB924054 W2K3 Server
2.0 IE Update W2K3 Server

Now I don't know which one of these patches is causing the real problem but yes it's one of them for sure.

We can see these error in the SSIS logging files...

Now the quick fix to these errors are NOT using "ReadOnlyVariables" and "ReadWriteVariables" properties for declaring variables that will be used in the script. Instead a better way is to declare the same in script itself and then locking and unlocking the same using DTS object model.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim myVar As Variables

Dts.VariableDispenser.LockForWrite("User::Var1")
Dts.VariableDispenser.LockForWrite("User::Var2")

Dts.VariableDispenser.GetVariables(myVar)

myVar("User::Var1").Value = "SomeValue"
myVar("User::Var1").Value = "SomeValue"

myVar.Unlock()

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Let me know if you know the exact source of this problem.

- Mohit Nayyar

Microsoft SQL Server common query performance problems

2007-08-17T23:05:00.001-07:00

At some point of time we all have to fine tune our old SQL Server queries or some of our colleague’s queries ;)

So I thought of writing something about common query performance problems and a checklist which can be used a make sure we didn’t miss anything.

1.	Large Result Set	Common mistake to include * instead of specific column names takes first place in investigation for performance issue. We should always use specific column required by the application instead of asking all available columns. Using * get worst when there are image/text/binary or very large data columns available in the table. Making use of TOP / TOP Percent / SET ROWCOUNT is another smart move to restrict the data based on technical solution.
2.	Missing Where Clause	We should always include the specific criteria when asking for rows. Making use of WHERE clause is very beneficial when we have appropriate indexes on the columns in WHERE and SELECT clause.
3.	Lack of useful statistics	This is again very important point, statistics are the key for query optimizer to make an execution plan and get the data out. SQL Server automatically creates useful statistics, but just makes sure that “Auto Create Statistics” is set to “TRUE” in database properties.
4.	Out-of-date statistics	This is again very important point, statistics are the key for query optimizer to make an execution plan and get the data out. SQL Server automatically creates useful statistics, but just makes sure that “Auto Update Statistics” is set to “TRUE” in database properties.
5.	Lack of useful indexes	Again Indexes are the key to do any data I/O related operation. So just make sure you have all required indexes in place. Just make sure you have created required clustered / non-clustered indexes based on the business requirement and technical solution implementation. And try to create indexes on smallest possible numeric columns with respect to indexing requirement. I recommend reading BOL to understand the indexes thoroughly.
6.	Missing Joins	Yeah, that’s again is a good point to check. I have seen developers who simply put statements like “select * from table1, table2, table3”. Never do that, just make sure you have followed the proper join and I recommend ANSI joins instead of “=, =, =” type joins. They are easy to read/manage and sort of required for future SQL Server versions.
7.	Blocking	Transactions’ fighting for resources is nothing new in any RDBMS application but we need to make sure that make use of all possible options to avoid excessive blocking.
8.	Query / Table Hints	They can be good or bad, based on the scenario. But it’s recommended that we should not use them extensively unless we have excellent understanding of technical implantation and future business requirement and data load trends. SQL Server takes care of this very well, so better don’t use.
9.	Use best possible data type	We are talking about database systems, so taking care of what we are trying to store is very important in designing the whole system. We should always choose the best possible smallest data type based on the scenario. Using INT for storing something like number 2000 is not a good option.
10.	Index Fragmentation	Hmm, not only having indexes solves the whole problem, we need to make sure that indexes are not fragmentated as well. DBCC SHOWCONTIG and some of the latest DMVs are very useful in checking the fragmentation. And then re-building or defragmenting the index will solve the problem easily.
11.	Cursors	We should use cursors only when there is no option left and that too using READ ONLY, FAST FORWARD is recommended. And please make sure that we should only ask for smallest result-set possible using WHERE clause and with specific columns name instead of using star (*) in cursor definition. Otherwise cursors put extra overhead on SQL Server and make extensive use of TempDB.
12.	Extensive temp table (tempdb) usage	Big bulky queries with lots of ordering, grouping clauses which result in huge result set can make use of TempDB extensively. So try to use simple, smart queries which generate small result set and if this is not possible then make sure TempDb is on fastest possible disk system with setting a optimum size for the TempDB based on the technical solution. Using TABLE data type is another good option for storing temporary result set. CTE (Common Table Expression), new to Microsoft SQL Server 2005 can also be used to achieve the same to some extent.
13.	Triggers	Try to use Constraints instead of triggers, like for referential integrity task using triggers is not a good option and that can be easily achievable by using constraints. Triggers are more resource expensive and they work as integral part of transaction, which will makes transaction lengthy as well.
14.	Dynamic SQL Statements	Using Views, stored procedures are always beneficial over using dynamic SQL. And using dynamic SQL is an open invitation to SQL injection attacks. And when there is no option left then we should make use of sp_ExecuteSQL instead of using EXEC command.
15.	Lack of Views / Stored Procedures	Accessing direct table is not a good practice in any engagement, and a developer should always access data through views and stored procedures. SP also provides additional benefits like modular programming, security later, saves network traffic, and faster execution. SQL Server 2005 also introduces a statement level re-compilation, which is again very useful for bulky procedures.
16.	Use Distinct clause	Deleting duplicate data itself is a resource intensive operation, so making use of DISTINCT clause should be minimal in all queries.
17.	Missing SET NOCOUNT ON statement	Setting this option will stop sending the rows affected message to the calling client, which itself is a network intensive operation and moreover client applications doesn’t make use of those messages.
18.	Using UNION clause	Again using UNION will delete the duplicate rows in the result set, which is a resource intensive operation, so using UNION ALL is preferred way to doing the same thing.
19.	Multiple Network trips	Making use of stored procedures, views and small result set can save lots of network traffic. Instead of sending a lengthy query to SQL server we should send procedure name with parameters to SQL Server and then get the smallest possible result set by using all possible options I explained here.
20.	Lack of useful data striping	Data partition is the very useful option to reduce the size of the table. We can do the same using horizontal or vertical partition. This way we can save a lot of IO overhead and other related resources. There are various options available in SQL Server 2005 to do the same stuff and developers can also make their custom solutions based on business requirement. Remember, small tables are always faster than bulky ones.
21.	Front-end Application issues like looping statements	Sometimes things are going well at SQL server end but because of some problem at front-end application we assume that SQL server is not doing his job. So it’s always better to test the query at SQL Server end and then checking network resources before blaming poor SQL Server.
22.	Third party applications like anti-virus or some other services like IIS	Sometimes resource overhead because of other application running on same SQL Server is the main cause of poor performance. So we should always check the memory and CPU utilization of other applications on the server.
23.	Slow network communication	Network bandwidth plays a very vital role in whole solution and this should be tested thoroughly in the solution.
24.	Inadequate memory in the server computer or not enough memory available for SQL Server	Setting appropriate settings for SQL Server is an art; SQL Server will NOT utilize all memory available until asked to do so. Using 3GB, PAE, AWE options are the key to memory utilization for Microsoft SQL Server.
25.	High Processor usage by other applications	Sometimes resource overhead because of other application running on same SQL Server is the main cause of poor performance. So we should always check the memory and CPU utilization of other applications on the server.
26.	Incorrectly configured system parameters	Setting SQL Server system parameters is a highly skilled job, and in some case it’s an art. Things works well on one server but this doesn’t out be true on another server because of changed business/technical requirement. So it’s always better NOT to change any default option and even if we need to change some option then please read thoroughly about the option and test on development server before putting the same on production server.
27.	Disk bottlenecks	Disk IO is the key to any RDBMS application, because all we have is data and lots of data. So make sure you have used best possible RAID along with fastest possible Disk IO.

Table row count using 3 different ways

2007-08-16T09:28:00.000-07:00

Recently one of my Friend was looking for a script to get row count in all user table, so we come up with 3 different ways of doing the same thing......I hope this will help

SELECT object_name (i.id) TableName, rows as RowCnt
FROM sysindexes i INNER JOIN sysObjects o ON (o.id = i.id AND o.xType = 'U')
WHERE indid < 2
ORDER BY TableName

************************************************************

CREATE TABLE #temp (TableName VARCHAR (255), RowCnt INT)
EXEC sp_MSforeachtable 'INSERT INTO #temp SELECT ''?'', COUNT(*) FROM ?'
SELECT TableName, RowCnt FROM #temp ORDER BY TableName
DROP TABLE #temp

************************************************************

SET NOCOUNT ON
DECLARE @tableName VARCHAR (255), @sql VARCHAR (300)
CREATE TABLE #temp (TableName VARCHAR (255), rowCnt INT)
DECLARE myCursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'base table'
OPEN myCursor
FETCH NEXT FROM myCursor INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('INSERT INTO #temp (TableName, rowCnt) SELECT ''' + @tableName + ''' as tableName, count(*) as rowCnt from ' + @tableName)
FETCH NEXT FROM myCursor INTO @tableName
END
SELECT TableName, RowCnt FROM #temp ORDER BY TableName
CLOSE myCursor
DEALLOCATE myCursor
DROP TABLE #temp

- Mohit Nayyar

Microsoft SQL Server 2005 High Availability options...

2007-08-11T10:26:00.000-07:00

Recently I was proposing some of the high availability options for Microsoft SQL Server 2005, so thought of summarizing the same here....

SQL Server 2005 provides 4 basic options for high availability.

SQL Clustering
Database Mirroring
Transactional Replication
Log Shipping

Other than these 4 options some people also consider backup-restore and Database snapshots as other high availability options, which is really not true.....

"Database snapshots can be used as a recovery option from user/application specific errors or for reporting purposes when combined with other technologies. A snapshot is simply a read-only view of a database at a particular point in time, that’s why called - Database snapshots"

So let’s see all of them in more detail...

I will evaluate each high availability option on these parameters

1.	Failover type	Automatic or Manual
2.	Downtime period	Time to recover or get back the system in place
3.	Availability Scope	Database level or Server level
4.	Special hardware	Any special hardware required
5.	Standby Type	Hot / Warm
6.	Complexity	Configuration/maintenance complexity
7.	Accessibility	Is it possible to check the other server or can we make use of that anyway
8.	Client Access	Client redirection, Automatic or Manual
9.	Additional servers	Any additional Microsoft SQL Server required
10.	Distance limit	How far the servers can sit
11.	Automatic Role Change	Is it possible for the server to resume work automatically or need manual intervention
12.	Multiple storage location	Multiple data storage locations
13.	Hardware Setup Cost	How expensive the whole system will be
14.	SQL Server Editions support	What all options are available with each edition of Microsoft SQL Server 2005

	SQL Clustering	Database Mirroring	Transactional Replication	Log Shipping
Failover type	Automatic / Manual	Automatic / Manual	Manual	Manual
Downtime period	30 seconds	30 seconds	Manual effort required	Manual effort required
Availability Scope	Server	Database	Database / DB Objects	Database
Special hardware	Yes	No	No	No
Standby Type	Hot	Hot	Warm	Warm
Complexity	Complex	Little Complex	Little Complex	Easy
Data Accessibility (other than primary server)	No	Possible	Possible	Possible
Client Access	Automatic redirection	Automatic redirection	Manual effort required	Manual effort required
Additional servers (including base server)	At least >= 2 Servers	At least >= 3 Servers (automatic failover)	At least >= 2 Servers	At least >= 2 Servers
Distance limit	100 Miles	No Limit but depends upon network bandwidth	No Limit but depends upon network bandwidth	No Limit but depends upon network bandwidth
Automatic Role Change	Yes	Yes	No	No
Multiple storage location	No	Yes	Yes	Yes
Hardware Setup Cost	High, special certified hardware required	Medium	Low	Low
SQL Server Editions support	SQL Server Enterprise and Standard (2 node only)	SQL Server Enterprise and Standard (Restricted)	SQL Server Enterprise, Standard and Workgroup	SQL Server Enterprise, Standard and Workgroup

- Mohit

SQL Formatter – Excellent work and really useful

2007-08-10T20:07:00.000-07:00

I recently came across this link and I am thrilled with the work………

Basically we can convert our poor written SQL statements into well formatted statements. So it makes the statements more readable, manageable and looks beautiful.

I know how it feels when we need to redo the work done by last DBA/Developer.

And top of that it can also convert SQL statements into equivalent C#, Delphi, Java, Pro*C or XML format.

I am sure you will love this at http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl

Enjoy and be more productive!

BI and Reporting enhancements in Microsoft SQL Server 2008 "Katmai"

2007-07-03T07:57:00.001-07:00

Change Data Capture
Change Data Capture (CDC) is a really interesting feature in Katmai using which we can focus on specific business requirement to capture changed data. That means this component captures all changes asynchronously and then exposes the changes through T-SQL based on specific requirements.

The provision to capture schema-changes is also very useful in this component.

Merge SQL Statement
Truly fantastic and really useful........

So how many times you have used "IF EXISTS" to make sure that you insert new row otherwise update the existing row.....yes I am talking about UPSERT...

This MERGE statement takes care of this scenario and check for existing rows to make updates and inserts new rows in the table.

MERGE INTO FactTable F
USING TransactionTable T ON T.OrderID = F.OrderID
WHEN MATCHED THEN UPDATE
SET F.Quantity = T.Quantity
WHEN NOT MATCHED THEN INSERT (OrderID, Quantity)
VALUES (T.OrderID, T.Quantity)

C# support for SSIS scripting components

Doesn't require IIS for SSRS
Yeah, thats the good part, we don't need IIS anymore to load SSRS reports with SQL Server 2008. This is the best for a developer like me.......

Grouping Sets
So how many times you have asked by business users to give specific grouping totals, people in sales only ask for product level totals, management looks for employee-product totals for bonus, finance needs total on 10 different things........

Well now with this new feature we can define multiple grouping option within the same query....that means instead of writing multiple group by queries and then combining the result with UNION ALL, we can have just one query with multiple grouping sets

Tablix – This release of SQL Server introduces a new report component called tablix, technically it’s a combination of existing table and matrix in SSRS. We just need to change few basic properties and this will do the job, so no more patchwork required to achieve stacked columns, pivot and more than 1 subtotals.

RDL Object Model – Good news for developers who wants to write/extend reports programmatically.

Dundas RS controls – As we know that Microsoft recently acquired Dundas chart, so this release of Reporting Services will contain the full power of dundas, an absolutely must have in reporting chart world.

Soft Artisans Office Writer technology – Again, This acquisition will allow users to embed their reports in Microsoft Word and Excel documents. Talk to you manager, he will love this.

Export to Microsoft Word

SSIS Lookup "TxLookup" in SQL Server 2008
Microsoft is really working on this component, and now we can lookup into any data source, including other SSIS packages, great news. Along with this TxLookup also works on "pre-charge" query, currently we have cache-miss queries in SQL Server 2005 Integration Services.

SharePoint user interface to manage SSRS reports

Office based report authoring
Now we can used report authoring tool, which allow us to author our reports using excel or word based tools. That means we can put tables, formulas, and also apply formatting too.

FYI - Microsoft entry into MDM world
Microsoft have also purchased Stratature recently, which provides Master Data Management solutions. MDM solution is really useful and must have for organizations having multiple businesses. Read more on MDM at MSDN The What, Why, and How of Master Data Management

Agile software development - eScrum by Microsoft

2007-06-15T13:12:00.000-07:00

Here we have another great news, Microsoft has just release a new tool called eScrum to keep track of all SCRUM related activities in agile software development.

eScrum is a web-based, project management tool for SCRUM which will be the part of Microsoft Visual Studio Team Foundation.

PS: Previously all SCRUM lovers have used an offering (add-in) provided by Conchango (UK based consultancy) to manage SCRUM development, which works really well with Microsoft Team System.

Get one FREE here………
http://www.microsoft.com/downloads/details.aspx?FamilyID=55A4BDE6-10A7-4C41-9938-F388C1ED15E9&displaylang=en

T-SQL or Merge JOIN or Lookup to load Dimension data

2007-06-14T16:51:00.000-07:00

In my recent discussion with a friend we talked about loading the data in dimension table. So being a SQL guy we have at least two options available in Microsoft SQL Server 2005.

So let me first explain the objective behind this.

We need to load only new data in target dimension table. So we need to check if this data exists at the destination, if not then insert this new data based on the business keys e.g. CustomerCode, ProductCode etc.

T-SQL Solution

LEFT / RIGHT JOIN
Make a join of target table with source table on business keys and then use LEFT or RIGHT join to get only new rows.

SELECT s.CustomerCode, s.CustomerName
FROM SourceTable s
LEFT JOIN TargetTable t ON (s.CustomerCode = t.CustomerCode)
WHERE t.CustomerCode IS NULL

The above solution works fine if you have source and destination databases on the same server, or may be you can use linked server to achieve the same if it's on different servers.

SSIS Solution

Now the standard solution to use LEFT / RIGHT JOINS in SSIS is "Merge Join" transformation, you can do precisely the same thing, it works well even if you have source database in Oracle and target database in Microsoft SQL Server.

But as we are aware that Merge Join expects sorted data, so if you are planning to sort the data at source system or use SORT transformation in SSIS then it will prove to be a costly affair.

Hmm............then what else can I do.........here I present Lookup transformation in SSIS

Well, generally this component is used to load Fact tables; when you want to load surrogate keys derived from Dimension table into Fact table.

But we can also "Configure Error Output" for this component....what does that mean........let me explain.....

Technically we map a common field from two tables in this component and fetch other columns based on this common column data. So what if all the data is not available in both of these tables…..let me explain…

Let say we have 10 rows in source and 5 rows in target.........so by default this component will return error because it’s not able to find other 5 rows in target table................hmmm..........trust me this is really good for us....

Now I can "Configure Error Output" for this component, which will redirect the failure rows and these failure redirected rows are the one that we are missing in target............so we achieved the functionality of loading only new rows in target table.

I hope this will be helpful for you as well..........

LINQ & LINQ to Entities in SQL Server 2008 "Katmai"

2007-06-13T13:41:00.000-07:00

I am very excited with this new way to access data and recently learned something about LINQ and what SQL Server 2008 "Katmai" stores with respect to LINQ to Entities model.

Let’s have a look to...........Language Integrated Query (LINQ)

Today data can belong to different data domains like XML document, database, text file, excel spreadsheet. So various specific data access model are being used to access data from these sources. Let say XQuery is used to fetch XML data, SQL is used to query RDBMS and a custom code is written to query text files.

Now LINQ tries to resolve these issues by offering a uniform way to access and manage data. We can also see LINQ as a methodology that simplifies and unifies the implementation of any kind of data access.

LINQ consists of standard query operators to play with data irrespective of the data sources. So in a way we are on the path to use standard code against any data source like Microsoft SQL Server, Oracle, Access or Sybase. This makes developer life really easy, so there is no need to spend hours in learning various languages (access methods) to work in multiple projects.

So precisely Microsoft is extending the power of .Net languages specifically C# and Visual Basic.Net to include this kind of functionality named as LINQ. This kind of implementation reuses the developer's existing knowledge of Object Oriented programming to play with databases, XML, objects, and few other data domains.

“Microsoft original motivation behind LINQ was to address the impedance mismatch between programming languages and database.”

LINQ to SQL (previously known as DLINQ), translates LINQ expression into SQL Server queries. People have already started creating LINQ libraries to query WMI, LDAP, SharePoint data sources.

LINQ to Entities

LINQ to Entities is supported by latest release of ADO.NET Entity Framework. The primary benefit of this framework is to run C# LINQ queries against a conceptual data model instead of tables. So LINQ to Entities seems to be a superset of DLINQ. The entity framework provides an additional layer on database schema which is available in terms of entities or conceptual model to query in .Net language.

That means writing something against entity is far more easy and manageable than writing complex SQL queries.

var CAEmployee = from employee in Employees where employeeLocation is CaliforniaState select employee;

Above statement is really easy to understand and I am really not bothered about database schema. What all I know is “employees” and I want to fetch California employees.

I guess this makes lot of sense and I really appreciate this work by Microsoft.

MSF or SCRUM in Agile Software Development

2007-06-05T12:35:00.000-07:00

Recently I had long discussion about usage of MSF or SCRUM in Agile Software Development, and I found lots of different opinions on this in the community.

So first of all I would like to explain what agile software development is all about.

Manifesto for Agile Software Development

Individuals and interactions over processes and tools
Working software over comprehensive documentation
Customer collaboration over contract negotiation
Responding to change over following a plan

So what's the difference in achieving these using SCRUM or MSF; let’s have a look

SCRUM
Scrum provides general guidelines to the software development where the key objective is to achieve a set of prioritized shippable features within specific time limits (typically 30 days sprint) by putting primarily focus on team interaction and to adapt fast changing requirement in business process. So scrum is focused on

1. Team interaction
2. Prioritizing the customer requirement
3. Generating working software in monthly cycles (sprint)

Microsoft Solutions Framework (MSF)
MSF was introduced in 1994 as a combination of best practices and guidelines used within Microsoft product teams and Microsoft's Consulting practice and various other Microsoft internal business divisions. At the core of MSF are nine foundational principles:

1. Foster open communications
2. Work toward a shared vision
3. Empower team members
4. Establish clear accountability and shared responsibility
5. Deliver incremental value
6. Stay agile, expect and adapt to change
7. Invest in quality
8. Learn from all experiences
9. Partner with customers

We can find similarities in these MSF principles and Scrum objectives, so what’s the difference. Well as per my discussion with some of the techies who follow Scrum religiously find these issues with MSF

1. MSF defines work for the team, so it’s missing the self-organizing factor.
2. Incremental units produced using MSF are non-shippable.
3. Its more process oriented, which in-turn slows down the development process.
4. It doesn't support cross-functional behavior within the team.

On the other hand people familiar with MSF advocates the same using these points

1. It has a well defined process
2. It works well for big teams and large projects
3. Onsite - Offshore model is more suited to MSF
4. With MSF people not only share responsibility but they also share the common vision, achievable through a defined process

So finally which is good, well being a consultant I will answer "Depends"........Scrum is fast, can give results really early, but I saw few quality issues, doesn't seem to work for large teams or let say large projects. Even sometime producing independent features are also hard, so depends a lot on the project.

Again, MSF works best in offshore delivery model where the large team shares a common vision to achieve the end result in a well defined manner under agile development.

Your Data, Any Place, Any Time - SQL Server 2008 "Katmai"

2007-06-04T13:56:00.000-07:00

I guess now I should start writing my blog with the CTP (Community Technology Preview) release of SQL Server 2008 code name "Katmai". So let see what we got in this latest release of SQL Server from Microsoft.

So what Microsoft is calling its new release "Your data, any place, any time"......hmm.......sound good to me....

The major feature I see in this release is the capability to store data in file system along with existing relational and XML data. This new feature provides the facility to store large binary data in file system, still being a part of database with transactional consistency. So looks like an effective storage model with all database benefits.

Another interesting feature is working with entities instead of tables. So this new ADO.Net Entity Framework enables developers to work with business entities like "employee" or "student" instead of using tables.

LINQ (Language Integrated Query) another new feature is very useful with its capability to query the SQL Server in any programming language instead of using SQL to query the database.

Encrypting the whole database is another enhancement in this release. That means instead of Encrypting a table or column we can encrypt the whole database, log, or data file without making much change in application logic.

On the same lines encryption with third-party key is also beneficial for regulatory compliance.

Automatic recovery of corrupted data pages from mirrored server is another important enhancement in this release and the best part of this feature is transparency to end-user.

Resource Governor, as the name says coming up for the first time with SQL Server 2008. It’s very useful to define the priorities and resource limits, thus getting consistent and predictable performance benefits.

Go to http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx to know more about SQL Server 2008.