Earlier this week Microsoft have released two very interesting downloads which got my attention. Haddop, PowerPivot and PowerView all three are not directly related to traditional RDBMS but their important is growing in the industry as big data is taking over market.
The Microsoft SQL Server SQOOP Connector for Hadoop is now part of Apache SQOOP 1.4 and we are not providing a separate download anymore. Please note that Microsoft’s HDInsight service includes the connector as well. Linux (for Hadoop setup) and Windows (with SQL Server 2008 R2 installed). Both are required to use the SQL Server-Hadoop Connector.
With SQL Server-Hadoop Connector, you import data from:
tables in SQL Server to delimited text files on HDFS
tables in SQL Server to SequenceFiles files on HDFS
tables in SQL Server to tables in Hive
result of queries executed on SQL Server to delimited text files on HDFS
result of queries executed on SQL Server to SequenceFiles files on HDFS
result of queries executed on SQL Server to tables in Hive
With SQL Server-Hadoop Connector, you can export data from:
The Workbook Size optimizer for Excel can better compress data inside workbooks that use PowerPivot or PowerView if this data comes from external data sources. The best size compression can be achieved for workbooks based on SQL Server databases and there are a few tricks we can do for other SQL datasources as well. The optimizer will install as an add in to excel and will provide you with a nice wizard to better compress the size of your workbook. Using the optimizer you can often get more than 1,000,000 rows datasets in a workbook under 10 MB, share it in SharePointOnline and interact with it using the Excel Web App in any browser.
If you are new to the documentation set or new to Microsoft SQL Server, reading the SQL Server system overview document will help familiarize you with the organization of the documentation set as well as with SQL Server concepts and how the protocols relate to each other.
The SQL Server standards support documentation provides detailed support information for certain standards that are implemented in Microsoft SQL Server.
The Microsoft SQL Server protocol documentation provides technical specifications for Microsoft proprietary protocols that are implemented and used in Microsoft SQL Server 2008. This whitepaper provides an overview of the client and server protocols used by Microsoft SQL Server. It covers protocols that are commonly shared by SQL Server components and protocols that are used only by specific SQL Server components. Where appropriate, the document describes the relationships between protocols and provides example scenarios to show how they are used.
Microsoft SQL Server Data Portability Documentation
The SQL Server data portability documentation explains various mechanisms by which user-created data in SQL Server can be extracted for use in other software products. These mechanisms include import/export functionality, documented APIs, industry standard formats, or documented data structures/file formats. This whitepaper provides an overview of the client and server protocols used by Microsoft SQL Server. It covers protocols that are commonly shared by SQL Server components and protocols that are used only by specific SQL Server components. Where appropriate, the document describes the relationships between protocols and provides example scenarios to show how they are used.
Learning any technology is easy when we have good documentation and sample database to play along with. When I have to learn any new technology, my first action is to install the trial software and look for sample database next. Once I get sample database, I try to find a video tutorial about the technology and continue to learn onwards.
Recently Microsoft has released the samples for PowerPivot in Excel. This is excellent as this gives opportunity to developer to play with the sample and learn all the features without worrying about building a sample which can work with all the features. PowerPivot empowers users of all levels to access and mashup data from virtually any source. Users can create their own compelling reports and analytical applications, easily share insights, and collaborate with colleagues through Microsoft Excel and SharePoint.
If you open a sample for PowerPivot for Excel 2010 in PowerPivot in Excel 2013, you will get a notification stating you must upgrade the data model in the workbook. You cannot open a sample for PowerPivot in Excel 2013 data model in PowerPivot for Excel 2010. In sample database there are two different files 1) PowerPivotExamplesExcel2010.zip and 2) PowerPivotExamplesExcel2013.zip
Each sample file contains three Business Intelligence samples:
Cloud is changing the way how IT world is evolving. The new startups start thinking in terms of the cloud from the inceptions. There are new opportunities as well there are few limitations when it is about cloud. Some organizations believe using cloud will give them an edge and some use the word as a marketing term. It does not matter what is the purpose the cloud is here for a long time. Having software on the cloud is now synonymous for performance and reliability. Honestly, just like every product and service, there are always the best practices to follow for optimal outcome.
Windows Azure provides Infrastructure as a Service (IaaS) support for both Windows Server and Linux operating systems. This means now user can “pay as you go” model where they have availability for scalability and elasticity. Any product is often successful when they are properly implemented. The challenge of implementation goes to the next level when it is multi-tenant application. It is very crucial now to create and manage balance as each tenant has different requirement and each of those has to balance with available resources. Security, Partitioning and Managing multi-tenant application on cloud requires expert guidance and proper directions.
Microsoft has recently released 3rd edition of the book which basically addresses precisely the same subject. I have been reading this book for a while and I find it quite interesting. There are a few interesting thoughts as well as some really good advices drafted in the book. Though the title of the book is very heavy the book is very easy to read and caricature of the author makes it very interesting.
Datatypes are very important concepts of SQL Server and there are quite often need to convert them from one datatypes to another datatype. I have seen that deveoperoften get confused when they have to convert the datatype. There are two important concept when it is about datatype conversion.
Implicit Conversion: Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit Conversions: Explicit conversions are those conversions that require the CAST or CONVERT function to be specified.
What it means is that if you are trying to convert value from datetime2 to time or from tinyint to int, SQL Server will automatically convert (implicit conversation) for you. However, if you are attempting to convert timestamp to smalldatetime or datetime to int you will need to explicitely convert them using either CAST or CONVERT function as well appropriate parameters.
Let us see a quick example of Implict Conversion and Explict Conversion.
You can see from above example that how we need both of the types of conversion in different situation. There are so many different datatypes and it is humanly impossible to know which datatype require implicit and which require explicit conversion. Additionally there are cases when the conversion is not possible as well.
Microsoft have published a chart where the grid displays various conversion possibilities as well a quick guide.
Last week, I was attending SQLPASS 2012 and I had great fun attending the event. During the event long awaited SQL Serer 2012 Service Pack 1 was released. I am pretty excited with SP1 as new service packs are cumulative updates and upgrade all editions and service levels of SQL Server 2012 to SP1. This service pack contains SQL Server 2012 Cumulative Update 1 (CU1) and Cumulative Update 2 (CU2).
The latest SP1 has many new and enhanced features. Here are a few for example:
Cross-Cluster Migration of AlwaysOn Availability Groups for OS Upgrade
Selective XML Index
DBCC SHOW_STATISTICS works with SELECT permission
New function returns statistics properties – sys.dm_db_stats_properties
SSMS Complete in Express
SlipStream Full Installation
Business Intelligence highlights with Office and SharePoint Server 2013
Management Object Support Added for Resource Governor DDL
Please note that the size of the service pack is near 1 GB.
Microsoft has recently released the updated to Windows Azure Training Kit. Earlier this month they have updated the kit and included quite a lot of things. Now the training kit contains 47 hands-on labs, 24 demos and 38 presentations. The best part is that the kit is now available to download in two different formats 1) Full Package (324.5 MB) and 2) Web Installer (2.4 MB). The full package enables you to download all of the hands-on labs and presentations to your local machine. The Web Installer allows you to select and download just the specific hands-on labs and presentations that you need.
This Windows Azure Training Kit contains Hands on Labs, Presentations and Videos and Demos. I encourage all of you to try this out as well. The Kit also contains details about Samples and Tools. The training kit is the most authoritative learning resource on Windows Azure.
Power View, a feature of SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint Server 2010 Enterprise Edition, is an interactive data exploration, visualization, and presentation experience. It provides intuitive ad-hoc reporting for business users such as data analysts, business decision makers, and information workers. Microsoft has recently released very interesting whitepaper which covers a sample scenario that validates the connectivity of the Power View reports to both PowerPivot workbooks and tabular models. This white paper talks about following important concepts about Power View:
Understanding the hardware and software requirements and their download locations
Installing and configuring the required infrastructure when Power View and its data models are on the same computer and on different computer
Installing and configuring a computer used for client access to Power View reports, models, Sharepoint 2012 and Power View in a workgroup
Configuring single sign-on access for double-hop scenarios with and without Kerberos
This whitepaper talks about many interesting scenarios. It would be really interesting to know if you are using Power View in your production environment. If yes, would you please share your experience over here.
Microsoft SQL Server Compact 4.0 is a free, embedded database that software developers can use for building ASP.NET websites and Windows desktop applications. SQL Server Compact 4.0 is the default database for Microsoft WebMatrix. For enhanced development and debugging capabilities, including designer support, Visual Studio can be used to develop ASP.NET web applications and websites using SQL Server Compact 4.0. Enabled to work in the medium or partial trust environments in the web servers, and can be easily deployed along with the website to the third party website hosting service providers. SQL Server CE 4.0 also provides stronger data security with the use of the SHA2 encryption algorithms for encrypting the databases. Latest version also supports T-SQL syntax enhancement by adding support for OFFSET and FETCH that can be used to write paging queries. Used with ADO.NET Entity Framework, SQL Server Compact now supports the columns that have server generated keys like identity, rowguid etc. and the code-first programming model. SQL Server Compact 4.0 is freely redistributable under a redistribution license agreement. SQL Server Compact 3.5 and SQL Server Compact 4.0 can be installed and work side by side on a desktop.
During an early career of mine as a database consultant – when I was dealing with SQL Server 2000, I often needed to collect various data related to SQL Server. My favorite tool to collect the data is PSSDIAG tool. It is a general purpose diagnostic collection utility that Microsoft Product Support Services uses to collect various logs and data files. It collects Performance Monitor logs, SQL Profiler traces, SQL Server blocking script output, Windows Event Logs, and SQLDIAG output. The data collected can be used by SQL Nexus tool which help you troubleshoot SQL Server performance problems. PSSDIAG is a wrapper around other data collection APIs and utilities, the performance impact of running PSSDIAG is generally equal to the impact of the traces that PSSDIAG has been configured to capture.
If you are using SQL Server 2000 – you need to seriously consider to upgrading it to SQL Server 2012.