Wednesday, July 28, 2010

UPDATE STATISTICS for all database tables

/***** BEGIN - UPDATE STATISTICS *****/
DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), TableName VARCHAR(128))

INSERT INTO @t(SchemaName, TableName)
SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES t
WHERE t.table_type = 'BASE TABLE'

DECLARE @Rows INT, @i INT;

SELECT @Rows = (SELECT COUNT(RowID) FROM @t), @i = 1;

WHILE(@i <= @Rows)
BEGIN
 DECLARE @sqlCommand NVARCHAR(512);

 SELECT @sqlCommand =
     'UPDATE STATISTICS ' +
     t.SchemaName + '.' +
     t.TableName +
     ' WITH FULLSCAN'
 FROM @t t
 WHERE t.RowID = @i;

 PRINT @sqlCommand
 EXEC sp_executesql @sqlCommand;

 SET @i = @i + 1;
END
GO
/***** END - UPDATE STATISTICS *****/

Wednesday, July 21, 2010

WCF Communications using wsHttpBinding with Username Authentication and TransportWithMessageCredentials

It's always a good idea to secure the transport medium and require authentication when invoking Enterprise-Level Web Services.  In the .NET World, these services are WCF - Windows Communications Foundation.

The challenge with WCF is all the required configuration and getting it right - at least for me...  Another challenge is choosing the right Authentication implementation.

In my specific scenario, we have a partner system that communicates with a WCF service.  The parter system is just that - a system.  With respect to Authentication, you may be thinking that the obvious choice is Windows Authentication.  Well, network engineers typically cringe when your requirement requires them to create Window's accounts for service-based transactions.  In all fairness, creating a bunch of Windows Accounts for services can become maintenance nightmare, so I wouldn't necessarily disagree with the network engineers' concerns.

So, what are the alternatives?  Create you own authentication implementation?  Probably not a good idea!  What does ASP.NET use?  That's right, ASP.NET uses the Membership Provider design pattern.  Microsoft provides two two out-of-box Membership Providers: SQLMembershipProvider (SQL Server table-based accounts), and ActiveDirectoryMembershipProvider (Windows account/Active Directory).

In my scenario, the ActiveDirectoryMembershipProvider presents the same challenges as Windows Authentication.  My network engineers would still need to create Windows accounts.  As for the SQLMembershipProvider, creating, implementing, and maintaining a SQL Server to authenticate and authorize a service request is a bit overkill (IMHO).  So, what how did I ultimately implement my solution?  Good question...

I used a composite implementation using Microsoft's WCF Implementation Guidance How to article 'How to: Use wsHttpBinding with Username Authentication and TransportWithMessageCredentials in WCF Calling from Windows Forms' and the open source ASP.NET XMLProviders project hosted on CodePlex.  I'm very happy with this implementation.

Thanks for reading...