All things SQL Server Related....

We launched the Excelerators Quiz and the Alpha Geek Challenge. Go now to the sites below and take the quiz to see if you’re anywhere near as good as Donald Farmer!  Remember to post your results on Facebook and Twitter or even challenge a friend! 

Also spread the word to all your Alpha Geek pals to participate in our challenges for some great prizes! Please send this to your friends, colleagues, and family members!

www.exceleratorsquiz.com

www.alphageekchallenge.com

Well with a great product release like SQL Server 2008 and many many features like Geospatial support, data visualisation with reporting services, DW optimisations and many other bells and whistles; one can be kept busy for a long long time.

And it only hit me how quickly time has flown when I read the following blog post that mentions that SQL Server 2008 RTM is set to hit end of mainstream support.

http://blogs.msdn.com/sqlreleaseservices/archive/2009/10/08/end-of-service-pack-support-for-sql-server-2005-sp2-and-sql-server-2008-rtm.aspx

Please ensure you have plans to upgrade your SQL Server 2008 installs to SP1. Also it may be time to start considering SQL Server 2008 R2 with its release early this year (2010).

I have been doing some loading of random text in tables, but wanted to make changes to some of the text in rows, instead of running a full update operation, I discovered that you can also use TSQL functions REPLACE or STUFF to change text.

Below are examples:

UPDATE Dbo.Table_1
SET c2 = (SELECT REPLACE(c2,'It','This'))
WHERE c1 = 1

UPDATE dbo.Table_1
SET c2 = (SELECT STUFF(c2,5, 8, 'Add this text'))
WHERE c1=2

REPLACE - this function replaces all the instances of a specified source string within a target string. The first parameter is the source string expression, next is the search string, and last is the replacement string.

STUFF - inserts one string in another. The first parameter is the source string expression. Next is the insertion point, then the number of characters to delete, and finally the string to be inserted.

Even though this is not related to SQL Server, I thought I would share it, as we know SQL Server runs on top of a OS – and it is always handy to know how we can configure the OS.

PRODUCTIVITY TIP

Do you spend precious time trying to find the right place in Windows to adjust a particular setting?   Would a single dialog containing all the settings that you would ever want to change be useful then read on…

This works in both Windows Vista and Windows 7.  Add a new folder to your desktop and name it GodMode.{ED7BA470-8E54-465E-825C-99712043E01C} - you will now see the icon above on your desktop and a pretty long list of items.  Note: You can replace the “GodMode” with anything… I use “Full Mode Control Panel”.

Figure 1. Example desktop icon.

Figure 2. Sample Full Mode Control Panel.

I was recently just asked for some best practices on SSIS, and put together a collection of the below whitepapers/articles that discuss good practices for SSIS package design/development.

· Top 10 SQL Server Integration Services Best Practices - http://sqlcat.com/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx

· Best Practices for Integration Services Configurations - http://technet.microsoft.com/en-us/library/cc671628.aspx

· Using the Microsoft Connector for Oracle by Attunity with SQL Server 2008 Integration Services - http://technet.microsoft.com/en-us/library/ee470675.aspx

· We Loaded 1 Terabyte in 30 Minutes with SSIS, and So Can You - http://technet.microsoft.com/en-us/library/dd537533.aspx

· Considerations for High Volume ETL Using SQL Server Integration Services - http://technet.microsoft.com/en-us/library/cc671624.aspx

· Defining a Configuration Approach for Integration Services Packages - http://technet.microsoft.com/en-us/library/cc671625.aspx

This should help by reducing your time in finding the above articles.

One of the key areas for the SQL Server 2008 release is to be a scalable Data Warehousing platform. Many organisations have deployed it – with DW ranging from 1TB well into the 60-80TB.

So what makes SQL Server 2008 Enterprise a better choice than SQL Server 2005 is due to the star join and few-outer-row optimisations. A recently released whitepaper goes into further details and also has some test results on IBM servers.

Link: http://msdn.microsoft.com/en-us/library/ee410012.aspx

You have no doubt heard that with SQL Server 2008 R2 and Windows Server 2008 R2 we can now address 256 processors.

This is all good in theory – now see a video showing off some of the latest hardware from Unisys running pre-release versions of SQL Server 2008 R2. With this combination you can get a 72 hour query to execute in 25 seconds! and also your ETL loads too. Parallelise those bulk insert tasks – oh yay :)

SQL Server 2005 SP2 support will be retired on the 12th January 2010. Now is a good time to start planning upgrades of applications to SQL Server 2005 SP3 or SQL Server 2008 (were possible).

Link: http://support.microsoft.com/lifecycle/search/default.aspx?sort=PN&alpha=sql+server+2005&Filter=FilterNO

Well then follow the below link and you can launch an virtual lab that takes you through the PowerPivot add-in. The only thing you need to install is an ActiveX control to let you access the virtual lab environment.

https://cmg.vlabcenter.com/default.aspx?moduleid=ad3bd3e9-8d2b-498d-94fa-e41e1b09730d

SQL Server PowerPivot is an innovative data analysis technology that redefines how organizations apply and succeed with BI. IT managers can deliver corporate BI solutions and also establish a managed BI collaboration environment that gives business users the power to get the answers they need to make decisions on their own, facilitates information sharing and collaboration on user-generated BI solutions, and enables the IT department to manage user-generated BI solutions with increased operational efficiency.

SQL Server PowerPivot changes the game of BI by satisfying user needs, IT needs, and business needs in new and compelling ways. Users can drill into any aspect of their business and gain deeper insight on their own. IT can proactively manage the user-generated BI solutions. The business benefits through maximized use of IT investments, higher productivity, increased agility, and better, faster, more relevant decisions overall.

Download:  PowerPivot for Excel & Office 2010 BETA

Learn about PowerPivot for Excel 2010 from www.powerpivot.com

The November CTP download is available for MSDN and TechNet subscribers. The public download is available here.

The Data Platform Insider blog provides an overview of what's new in CTP release for SQL Server 2008 R2.

Many of you new SQL Server DBAs may be wondering what are the things to lookout for to effectively maintain a production SQL Server database.

Paul Randal has written an excellent article on this very topic, and it covers the following points:

• Managing data and transaction log files
• Eliminating index fragmentation
• Ensuring accurate, up-to-date statistics
• Detecting corrupted database pages
• Establishing an effective backup strategy

Link: http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx

Two new premium editions in SQL Server 2008 R2

SQL Server 2008 R2 Datacenter

Built on SQL Server 2008 R2 Enterprise, SQL Server 2008 R2 Datacenter delivers a high-performing data platform that provides the highest levels of scalability for large application workloads, virtualization and consolidation, and management for an organization’s database infrastructure. Datacenter helps enable organizations to cost effectively scale their mission-critical environment.

Key features new to Datacenter:

• Application and Multi-Server Management for enrolling, gain insights and managing greater than 25 instances
• Highest virtualization support for maximum ROI on consolidation and virtualization.
• High-scale complex event processing with SQL Server StreamInsight
• Supports more than 8 processors and up to 256 logical processors for highest levels of scale
• Supports memory limits up to OS maximum

SQL Server 2008 R2 Parallel Data Warehouse (Project “Madison”)

SQL Server 2008 R2 Parallel Data Warehouse is a highly scalable data warehouse appliance-based solution. Parallel Data Warehouse delivers performance at low cost through a massively parallel processing (MPP) architecture and integration with hardware partners – scale your data warehouse from the tens to hundreds terabyte up to a petabyte.

• 10s to 100s TBs to 1+ PB enabled by MPP architecture
• Advanced data warehousing capabilities like Star Join Queries and Change Data Capture
• Integration with SSIS, SSRS, and SSAS
• Supports industry standard data warehousing hub and spoke architecture and grid copy

Investments in core SQL Server 2008 R2 editions

SQL Server 2008 R2 Enterprise

SQL Server 2008 R2 Enterprise delivers a comprehensive data platform that provides built-in security, availability, and scale coupled with robust business intelligence offerings—helping enable the highest service levels for mission-critical workloads.

The following capabilities are new to Enterprise:

• Application and Multi-Server Management for enrolling, gain insights and managing up to  25 instances
• Master Data Services for data consistency across heterogeneous systems
• Data Compression now enabled with UCS-2 Unicode support

SQL Server 2008 R2 Standard

SQL Server 2008 R2 Standard delivers a complete data management and business intelligence platform for departments and small organizations to run their applications—helping enable effective database management with minimal IT resources.

The following capabilities are new to Standard:

• Backup Compression to reduce data backup storage by up to 60% and reduce time spent on backups
• Can be a managed instance for Application and Multi-Server Management

New Benchmarks for SQL Server 2008 R2

With the support for greater than 64 logical processors, SQL Server 2008 R2 is taking advantage of multi-core processors and enabling customers to run larger workloads with higher throughput. On Nov. 3, Microsoft will announce a new world record TPC-E(OLTP) performance on a 96-core server. For data warehousing, again taking advantage of our new support for greater than 64 logical processors, we are announcing the best ever Windows Server and SQL Server 3TB TPC-H(DW) performance benchmark.

Other key industry trends include solid state disks and virtualization. SQL Server is demonstrating its strength at both these trends by announcing record beating performance for our web based Dynamics CRM offering (xRM) using solid state discs and Hyper-v on commodity hardware. 

Fast Track 2.0:

Fast Track 2.0 is the latest release of SQL Server Fast Track Data Warehouse that enables customers to accelerate their data warehouse projects at low cost.   Fast Track 2.0 features IBM as a new Partner with 3 new Reference Architectures.  In addition, Fast Track 2.0 also has updated Reference Architectures from existing Track Partners - HP, Dell, EMC and Bull. 

Fast Track 2.0 offers customers:

• More choice with up to 12 Reference Architectures from HP, Dell, Bull, IBM and EMC
• More scale with configurations that that scale from 4 – 48 TB
• Fast Track 2.0 configurations now use Intel’s Nehalem and Dunnington processors as well as AMD’s 6-core Opteron processors     

This white paper describes the technologies available in SQL Server 2008 that can be used as part of a high-availability strategy to protect critical data. As well as describing the technologies in detail, the white paper also discusses the various causes of downtime and data loss, and how to evaluate and balance requirements and limitations when planning a high-availability strategy involving SQL Server 2008.

This white paper is targeted at architects, IT pros, and database administrators (DBAs) tasked with implementing a high-availability strategy. It assumes the reader is familiar with Windows and SQL Server and has at least a rudimentary knowledge of database concepts such as transactions.

Link:http://msdn.microsoft.com/en-us/library/ee523927.aspx