Join Fusion-io and Microsoft, the leaders in flash memory and enterprise database solutions, to learn how to Unleash the Performance of your business and mission critical SQL Server-based applications.

See how Microsoft and Fusion-io products combine for highly available, low-latency database solutions that really scream. Specifically, you will learn about:

• Key SQL Server 2012 features, combined with Fusion-io flash solutions, that optimize performance today
• How to build the best SQL Server 2012 platform now, and for the future
• Sneak-peak of SQL Server 2014 and the value-add with Fusion-io

Seminar Details

Location: Level 39, 2 Park Street, Sydney, NSW 2000, Australia

Dates: 7th November 2013

Time: 9:30am-1:30pm

Register for the seminar where you will hear how real customers have deployed joint Fusion-io and SQL Server solutions to drive enterprise business. One lucky attendee will also win a Microsoft Surface Pro!

Seminar Agenda:

• 9:30 - Registration
• 10:00 - Presentation and Demos
• 11:30 - Q&A
• 11:55 - Surface^TM Tablet Drawing
• 12:00 - Lunch
• 12:45 - Microsoft Technology Center tour (as applicable)

Register now for this exciting Microsoft & Fusion-io SQL Server Sydney event.

Below are two methods that can be used to determine the default location of data and transaction log files for your installation of SQL Server. This script can come handy when automating the deployment of databases in your environment.

Method 1

-- Check if temp database exists
-- Tempdatabase is used for determining the default database path
--if the zztempDefaultPath db exists drop  

IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = 'zzTempDBForDefaultPath')  

BEGIN 
    DROP DATABASE zzTempDBForDefaultPath  
END;

-- Create temp database. Because no options are given, the default data and --- log path locations are used

CREATE DATABASE zzTempDBForDefaultPath;

--Declare variables for creating temp database  

DECLARE @Default_Data_Path VARCHAR(512),  
        @Default_Log_Path VARCHAR(512);

--Get the default data path  

SELECT @Default_Data_Path =   
(   SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)
    FROM sys.master_files mf  
    INNER JOIN sys.[databases] d  
    ON mf.[database_id] = d.[database_id]  
    WHERE d.[name] = 'zzTempDBForDefaultPath' AND type = 0);

--Get the default Log path  

SELECT @Default_Log_Path =   
(   SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)  
    FROM sys.master_files mf  
    INNER JOIN sys.[databases] d  
    ON mf.[database_id] = d.[database_id]  
    WHERE d.[name] = 'zzTempDBForDefaultPath' AND type = 1);

--Clean up. Drop de temp database

IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = 'zzTempDBForDefaultPath')  
BEGIN 
    DROP DATABASE zzTempDBForDefaultPath  
END;

PRINT    @Default_Data_Path;  
PRINT    @Default_Log_Path;

Method 2

This method uses the registry entries to work out the default location path. Note: be vary of using this method as it is possible that registry entries may change and also the extended stored procedures to read the registry may not be supported in future versions.

For SQLPath:

declare @rc int,
@dir nvarchar(4000)

exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLPath', @dir output, 'no_output'
SELECT @dir

For the default data location:

declare @rc int,
@dir nvarchar(4000)
exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @dir output, 'no_output'

if (@dir is null)
begin
exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @dir output, 'no_output'
select @dir = @dir + N'\Data'
end

SELECT @dir

With the December update to SharePoint 2010, you can now view your existing BI solutions in your SharePoint on your iPad devices that use the iOS 5 Safari browser.

Users can view PerformancePoint scorecards, analytic charts and grids, Excel Services reports, and SQL Server Reporting Services reports by using the Safari browser on iPad. However, not all kinds of reports can be viewed on iPad. For example, users will be unable to view Visio Services reports or PerformancePoint strategy maps because of client-side requirements that iPad devices cannot support.

For more detailed information see the following Technet article - http://technet.microsoft.com/en-au/library/hh697482.aspx#part2

Thank you for the great in-memories this year with SQL Server 2014 and Powerful insights. It got a little unstructured but it was good to know Polybase was there to make it easy. With Ozure coming online everyone can relax this holiday season knowing your data is safe in Australia.

Thank you for your continued support this year. Wish you and your families a very Merry Christmas and Happy New Year. Look forward to a bigger brighter more insightful 2015.

I have not had the opportunity to blog in the last month and this has partly been due to a busy work schedule, however the greater reason was a technological one. This of course was the release of the Surface. I had pre-ordered mine and it was always my intent to use it as my main machine, which I have been successful at till date.

With the transition to Windows RT I have had to work out what are alternate Apps to be productive in my job. With Office 2013 being included in the Surface this covered most of my work which is to create PowerPoint presentations and writing this blog post in Word 2013. In my role I also have to demonstrate features of SQL Server and the Microsoft BI stack, including integration with Hadoop, you may wonder how I can do this on a Windows 8 RT device. Well with Remote Desktop to an Azure VM and Internet access it is Q.E.D (quiet easily done). I am really living the device + services life were I use the device to access my resources that are in the cloud.

Enough about my new device and now onto the world of data platform and what has taken place in the last month:

SQL PASS Conference

SQL PASS – the largest conference for users of SQL Server took place in the USA and there were many major announcements including the following:

• Project Hekaton – this in a nutshell was the announcement of a feature that will be part of the next major release of SQL Server which will enable in-memory OLTP databases on commodity hardware (no requirement for specialized appliances or flash cache, etc). For more information see - http://blogs.technet.com/b/dataplatforminsider/archive/2012/11/08/breakthrough-performance-with-in-memory-technologies.aspx
• Parallel Data Warehouse v2 (MPP architecture appliance) –the next version of Microsoft's enterprise-class appliance, will be available during the first half of 2013.  SQL Server 2012 PDW includes PolyBase, a fundamental breakthrough in data processing that will enable queries across relational data and non-relational Hadoop data. For more information on PolyBase and other enhancements in PDWv2 see http://blogs.technet.com/b/dataplatforminsider/archive/2012/11/09/seamless-insights-on-structured-and-unstructured-data-with-sql-server-2012-parallel-data-warehouse.aspx
• SQL Server 2012 SP1 – was officially released during the week of SQL PASS, below are the major highlights of SP1:
  • BI Functionality update for Office 2013
    • xVelocity integration – natively baked into Excel
    • Power View integration
    • PowerPivot integration
  • SharePoint 2013 – upgraded support for Power View, Power Pivot, and interactivity to Excel Services
  • SQL Server Database Engine updates
    • AlwaysON AG OS Upgrade – minimize downtime while upgrading to Windows Server 2012
    • Selective XML Index Performance Update – allowing users to promote certain paths from XML documents
  • SSMS Complete in Express – full capabilities of SSMS in Express
  • SlipStream Full Installation
  • Other performance, bug & security fixes - http://support.microsoft.com/kb/2674317

Microsoft BI Poster

Ever wanted to know what Microsoft BI solution looks like. Download this poster (pdf file) http://sdrv.ms/Um0av0 and it has a great overview of all the components and capabilities of the Microsoft BI platform.

Microsoft BI in Windows Azure VM

Liked what you see in the poster and now want to implement it but don't have the appropriate versions of software. Leverage the Virtual Machines available in Windows Azure. I have put together an easy to follow power point deck that goes through the steps. Download it from http://sdrv.ms/TKxvB3. It even includes Powershell script to turn off the VM and remove it to save you costs when you are not using it.

Master Data Services Whitepapers

We've heard from customers that they can be confused about how SQL Server 2012 Master Data Services and Data Quality Services work together and how they differ. The following white paper indicates how you can use MDS, DQS, and SSIS together to ensure the quality of master data. It addresses scenarios for building and cleansing a new master data entity using interactive processes, updating master data using an automated process, and performing matching, both in an automated process and interactively. The white paper also compares MDS and DQS use, such as how you can import and export data to a knowledge base or an entity, and the difference in how they use rules.

Whitepaper link:  Cleanse and Match Master Data by Using EIM" white paper at http://msdn.microsoft.com/en-us/library/jj836269.aspx

A tutorial is also available that walks through the implementation of the materials discussed in the whitepaper. This is available from http://www.microsoft.com/en-us/download/details.aspx?id=35462.

Big Data Case Study - Yahoo

The Yahoo! 24TB Analysis Services / Big Data Case Study is now live:

Yahoo! Improves Campaign Effectiveness, Boosts Ad Revenue with Big Data Solution

 This shows a real life integration of SQL Server and Hadoop. Some key numbers from this case study include:

• 24TB Analysis Services MOLAP cube 
• 2PB source data of a 14PB Hadoop cluster
  • 700M unique users, 47% of the global online population
  • 3.5B ad impressions/day

 The key quote is:

 "Yahoo! can now provide more relevant advertising data which has increased advertising spending and campaign effectiveness. We have achieved this by combining Hadoop and Hive technologies that handle large data sets with the powerful analytic insight provided by the Microsoft BI platform."   -- Dianne Cantwell, Lead TAO Developer, Yahoo!

To dive deeper, check out the PASS session: Tier-1 BI in the world of Big Data (the Yahoo! Case study starts at slide 38).