Martyn

Martyn Bullerwell's Blog

SQL 2008 Feature Pack

Not so much a Pack, as a collection of very useful tools, that can be downloaded here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=D68DE3C9-60A9-49C9-A28C-5C46BBC3356F&displaylang=en

I will be investigating the Reporting Services Integration with MOSS 2007 very soon, but for the time being here is some information on the downloads, that are being called the SQL 2008 feature pack.

  • Reporting Services Add-in for Microsoft SharePoint Technologies - pretty much the same as the one for SQL 2005, but with the ability to use the enhanced reporting in 2008.

  • SQL Server Native Client - One DLL, that contains OLEDB and SQL ODBC drivers for close application integration, they allow the development of new application that take advantage of some of the new features in SQL 2008.

  • SQL Server 2005 Backward Compatibility Components - Pretty much what it says on the tin,  Data Transformation Services 2000 runtime (DTS), SQL Distributed Management Objects (SQL-DMO), Decision Support Objects (DSO),  and SQL Virtual Device Interface (SQLVDI) are all supported with this feature pack.

  • SQL Server Data Mining Add-ins for Microsoft Office 2007 - Allows you take advantage of SQL Server 2008 predictive analytics in Excel 2007 and Visio 2007. Table analysis for excel allows you to use simple data mining models on data contained within your spreadsheets.  Data Mining for Visio allows you to render data mining models directly in Visio.

All in all pretty good stuff for nothing!

Installing MOSS 2007 on Windows Server 2008

SharePoint Server 2007 RTM or WSS 3.0 will not install on Windows Server 2008 as it requires SP1.  In order to install MOSS 2007 on Windows Server 2008 you will need to install 'MOSS with Service Pack 1' (this includes WSS with Service Pack 1). This is known as a slipstream installation and it contains the original image for MOSS/WSS RTM and the MOSS/WSS SP1 files. The problem is that this 'all in one' slipstream for MOSS was not available for download, however now it seems that this has been released and can be found here:

Office SharePoint Server 2007 with SP1 (32 bit)
http://www.microsoft.com/downloads/details.aspx?FamilyId=2E6E5A9C-EBF6-4F7F-8467-F4DE6BD6B831&displaylang=en
Office SharePoint Server 2007 with SP1 (64 bit)
http://www.microsoft.com/downloads/details.aspx?FamilyId=3015FDE4-85F6-4CBC-812D-55701FBFB563&displaylang=en

This seems odd as the Install is about 600mb and states that it is a trial, however bear with it, you will need to register for download, and you will find 2 executables  (OfficeServer.exe 295.0 MB and OfficeServerwithSP1.exe 409.2 MB) (What would we do without broadband!).

Install the OfficeServerwithSp1 executable.

Initially you will be asked to enter your Product Key.  If you have an official product key you can use that, otherwise you should have been sent a trial key.

Now you have a SharePoint Server that will install on Windows Server 2008, with no problems at all.  Microsoft have not yet released this as a bundle, or even on MSDN, however I am sure they will do this in time. 

C# Coalesce

Although this has been around for a long time and this is slightly off topic, I needed it this week, and just think it is worth mentioning.  With objects you occasionally need to know if they are null, and if they are get something else, or do something else.  This used to be very convoluted with .NET 1.1:

if (a != null)
{
  return a;
}
  else if (b != null)
{
return b;
}
  else if (c!= null)
{
  return c;
}
else
{
  return new object();
}

Now you can simply use this (.NET 2.0 and above):

return a ?? b ?? c ?? new object();

Now you can not use this with types that get default values, such as Integer's, or boolean's, however still very usefull.

Microsoft Office Sharepoint 2007 Versions

Microsoft versions can be become very confusing, not too long ago I was helping a small client get the correct version of SQL 2005 (There are an astonishing 6 editions, before breaking those editions into licensing methods).  Although not the topic of the article, research into business needs against actual required version can be quite time consuming, and confusing.  Microsoft have helped this with regards to Sharepoint 2007 by supplying a spreadsheet of version comparisons (Download here).  There are 3 versions of Sharepoint 2007, excluding the free bundled WSS 3.0 (a comparison can be found here); these are SharePoint 2007 for Search, Standard and Enterprise.  

MOSS 2007 For Search has the core search functionality but not the complete, integrated MOSS 2007 offering.

MOSS 2007 for Search indexes common data sources out of the box with security trimmed search results and can be extended to search third-party repositories. It can also provide search functionality for public-facing Internet sites. It delivers search functionality for structured and unstructured data sources, security trimmed search results, search for third-party content sources and repositories, as well as people and expertise search.

Standard vs Enterprise comes down to a few extra features (the following is cribbed directly from the Downloadable spreadsheet), which includes all of the Business Intelligence features of SharePoint.  Items Only available in Enterprise:

  • All Business Intelligence Offerings (11 Key Items) - So for us, in our ever growing world of Business Intelligence, means that the enterprise version is the only way forward.
  • All Forms Server Offerings (7 Key Items) - Although these features are implemented in Office Forms Server.
  • Business Data Search - Search data residing in your line of business applications using the Business Data Catalog. Structured content sources as well as LOB application data and reports accessible through web services or ADO.NET can be indexed and retrieved through the Business Data Catalog as search results or into a Sharepoint list
  • Excel Spreadsheet Developer Reuse and Extensibility - Spreadsheets published to SharePoint sites are accessible more securely using Web services. This means that application developers can take advantage of the logic embedded in a spreadsheet by remotely accessing spreadsheets from any platform. Business analysts can modify or update the model without affecting the business application or having to involve an application developer in the process.

Maximum Message Size For Web Services (.NET 3.5)

A new introduction to .NET 3.5 is the ability to limit the size of the incoming messages when using Web services.  Apparently this is to help combat Denial of Service (DoS) attacks.

However, it is not clear how to change this setting, its simple when you know how.  In you App.Config, or Web.Config you should have a Bindings section for each of web services references.  Within this there are all sorts of useful settings, however by default the maximum message size is quite small, so to alter this you must change maxBufferSize and maxRecievedMessageSize.  Now don't go crazy just up it to what you may need, this may be quite large if you are building all your internal applications through a web service layer.

LINQ and SQL Server

Some time ago I reported on LINQ whilst Visual Studio 2008 was still in BETA. On Monday, Nov. 19 2007, Microsoft announced that Visual Studio 2008 and the .NET Framework 3.5 was released to manufacturing (RTM).  Since then I am sure many of you have had many hours playing with VS 2008, WPF, Expression Blend, and all that these new products contain.  Having finally got my hands on it, and found the time, LINQ was the first stop.  So we have a database that we need to run complex queries on where stored procedures just would not be flexible enough.  Further to this Inline SQL is an obvious No No, so perfect scenario for a LINQ to SQL implementation. 

Deciding that a DBML (Database Markup Language) structure would work best, generating this file, and getting this file to be update-able was crucial.  So after some research we found a small, unknown command line application called SQLMetal.  This allows you to drop and re-create DBML files, based entirely on a given database.  

SqlMetal will always produce a consistent data access layer:

  • SQLMetal is a command line tool that can generate the data access layer in seconds.
  • SqlMetal will produce either C# or VB.Net code.
  • SqlMetal generates a strongly typed data access layer. This is great for reducing runtime error. Now those runtime errors pop up in the development cycle as compile errors, reducing stress on the QA dept, Support dept and upper management, later in the life cycle.

SqlMetal can generate strongly typed interfaces for stored procedures and user-defined functions. From the developer point of view, it is now a complete breeze to call either a stored proc and/or a user-defined function.

To generate source code from SQL database directly, execute the following:

C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin> SqlMetal /server:baker   /database:AdventureWorks  /namespace:AdventureWorks  /dbml:AdventureWorks.dbml

This creates a DBML file that can be added to your project:

image  

This now gives an entire database structure accessible via LINQ, one thing to be aware of though; ensure you have a solid database design, as SQLMetal will transform you Database design identifying all keys and relationships.

image

And once you have the database as an object model, you can use LINQ as you wish.

image

LINQ to SQL uses ADO.NET under the covers but offer several additional benefits:

    • Reduces complexity

    • Fewer lines of code

    • Strong Typing

    PerformancePoint Server over the Internet

    This is quite a simple little thing that really hasn't been mentioned yet, maybe because it is so simple, however nevertheless less worth mentioning.   You may have the requirement to be able to allow your users to forecast from where ever, and security permitting, this is a very easy thing to set up.   All communication between Excel and PPS happens over basic HTTP web services, this lends itself very well to configuring PPS for remote working. 

    image

    To set it up you need to open a port on your firewall to allow communication over the PPS port (46787), and then simply point your connection to your Domain that is pointing to the correct server... and it all works, no issues.  So in short, PPS works over the Internet, SSL is the next challenge.   Initial testing against SSL, i.e. HTTPS, looks very possible.  So to begin this add a new SSL port to the setting in IIS and create a new certificate in IIS, I chose to use 46797. for testing purposes it is as simple as adding a connection to Excel PPS add-in and testing.

    image

    So, PPS can be deployed over the Internet, using SSL.  This therefore also means that all PPS functions can be secured with SSL and accessed over the Internet.

    SQL Server 2008 - CTP

    OK so we should have been getting our mitts on SQL Server 2008 RTM yesterday, but it has been delayed, until Q3 which has been know for some time.  Microsoft did, however, go ahead with the official release yesterday, regardless that the product has not been released to manufacture.  However to keep our appetites "wet" Microsoft have released another CTP (February) which can be downloaded here: Download

    SQL Server 2008 is just an enhancement to the already extremely successful end to end BI stack that delivers a well rounded platform for the majority of organisations planning, reporting and analysis needs .  Their BI and performance management offering works best when it is implemented as part of a full stack of Microsoft infrastructure that includes SQL Server (including 2008), SharePoint Server 2007 and Microsoft Office (including Excel 2007). This platform is complemented by PerformancePoint Server 2007, and the obvious tight integration with Excel. 

    Microsoft are also making further inroads into competing with Teradata, IBM and Oracle with improving the relational database engine with features such as partitioned tables and support for partitioned table parallelism that position it more effectively within the large scale enterprise data warehousing market where IBM, Oracle and Teradata all play. Although still not ready for databases that run into the 100's of terabytes and possibly petabyte range,  the company is making in-roads, it currently has customers working comfortably in the one to 15 Terabyte range and expects SQL Server 2008 to extend this into the 20 to 50 Terabyte range.

    PerformancePoint and Windows Server 2008

    Well, the testing has continued this week after having issues with SQL 2008 with PerformancePoint, I rebuilt one of our test servers with the Latest Server CTP.  Obviously as I am keen to always know what Microsoft are up to in as many areas as possible, so I thought it wise to install Server 2008 with Hyper-V.  Now this seems like a great idea, however, it seems that this particular version is only available as a 64Bit variant at the moment, and they do not make that all to clear while installing.  So after installing this, and SQL server 2005, as to test PerfomancePoint on Server 2008, it seems that the version check for Windows fails, with is not ideal.  This seems to be solely due to the fact that it is a 64 Bit version. 

    So, the next step is to try the Install with the NoReqCheck option ( post on this can be found here ), now this does actually get PPS Server to install, however the configuration will not run.  I have not tested the 64 bit version of PPS on Server 2008, as it seems that Pro Clarity 6.3 is ONLY 32 bit. PPS 2007 currently doesn't offer the Analytics Component that Pro Clarity 6.3 offers. I'm sure, however this will be released in the next version of PPS 2007.

    PerformancePoint Server and SQL 2008

    So after many hours looking into this issue, it seems that the current release of PPS does not work on SQL Server 2008, and there seems to be no tricking it.  Should you manage to get round the install checks, while setting up the Database Connections to SQL and Analysis servers, the versions are checked once again, and the door is currently closed.

    In the mean time, SQL 2005 will still work!  SQL 2008 is still due to Launch SQL 2008 in February, however it now looks as if the RTM (Release to Manufacture) will now not be until Q3 of 2008 - so we are unlikely to see,even early adoption

    until July time.  Microsoft see  SQL server 2008 as a critical step forward towards their "broader vision for business intelligence", so I think we can be rest assured that PerformancePoint will be integrated before release date. 

    In the mean time I will update you with any further developments on this, and will continue testing with further CTP's and patches.