Posts related to SQL,SQLBI(SSIS,SSAS,SSRS).

Whats new in each version of SQL Server.

noreply@blogger.com (Deepak Kumar Kalyanum) — Sun, 14 Jul 2019 17:12:00 +0000

What's new in SQL Server 2017

SQL Server 2017 represents a major step towards making SQL Server a platform that gives you choices of development languages, data types, on-premises or cloud, and operating systems by bringing the power of SQL Server to Linux, Linux-based Docker containers, and Windows. This topic summarizes what is new for specific feature areas and includes links to additional details. For more information related to SQL Server on Linux, see SQL Server on Linux Documentation

Note

In addition to the changes below, cumulative updates are released at regular intervals after the GA release. These cumulative updates provide many improvements and fixes. For information about the latest CU release, see SQL Server 2017 Cumulative updates.

SQL Server 2017 Database Engine

SQL Server 2017 includes many new Database Engine features, enhancements, and performance improvements.

·         CLR assemblies can now be added to a whitelist, as a workaround for the clr strict security feature described in CTP 2.0. sp_add_trusted_assembly, sp_drop_trusted_assembly, and sys.trusted_asssemblies are added to support the white list of trusted assemblies (RC1).

·         Resumable online index rebuild resumes an online index rebuild operation from where it stopped after a failure (such as a failover to a replica or insufficient disk space), or pauses and later resumes an online index rebuild operation. See ALTER INDEX and Guidelines for online index operations. (CTP 2.0)

·         The IDENTITY_CACHE option for ALTER DATABASE SCOPED CONFIGURATION allows you to avoid gaps in the values of identity columns if a server restarts unexpectedly or fails over to a secondary server. See ALTER DATABASE SCOPED CONFIGURATION. (CTP 2.0)

·         A new generation of query processing improvements that will adapt optimization strategies to your application workload's runtime conditions. For this first version of the adaptive query processing feature family, we have three new improvements: batch mode adaptive joins, batch mode memory grant feedback, and interleaved execution for multi-statement table valued functions. See Intelligent query processing in SQL databases.

·         Automatic database tuning provides insight into potential query performance problems, recommends solutions, and can automatically fix identified problems. See Automatic tuning. (CTP 2.0)

·         New graph database capabilities for modeling many-to-many relationships include new CREATE TABLE syntax for creating node and edge tables, and the keyword MATCH for queries. See Graph Processing with SQL Server 2017. (CTP 2.0)

·         An sp_configure option called clr strict security is enabled by default to enhance the security of CLR assemblies. See CLR strict security. (CTP 2.0)

·         Setup now allows specifying initial tempdb file size up to 256 GB (262,144 MB) per file, with a warning if the file size is set greater than 1GB with IFI not enabled. (CTP 2.0)

·         The modified_extent_page_count column in sys.dm_db_file_space_usage tracks differential changes in each database file, enabling smart backup solutions that perform differential backup or full backup based on percentage of changed pages in the database. (CTP 2.0)

·         SELECT INTO T-SQL syntax now supports loading a table into a FileGroup other than the user's default by using the ON keyword. (CTP 2.0)

·         Cross database transactions are now supported among all databases that are part of an Always On Availability Group, including databases that are part of same instance. See Transactions - Always On Availability Groups and Database Mirroring (CTP 2.0)

·         New Availability Groups functionality includes clusterless support, Minimum Replica Commit Availability Groups setting, and Windows-Linux cross-OS migrations and testing. (CTP 1.3)

·         New dynamic management views:

o    sys.dm_db_log_stats exposes summary level attributes and information on transaction log files, helpful for monitoring transaction log health. (CTP 2.1)

o    sys.dm_tran_version_store_space_usage tracks version store usage per database, useful for proactively planning tempdb sizing based on the version store usage per database. (CTP 2.0)

o    sys.dm_db_log_info exposes VLF information to monitor, alert, and avert potential transaction log issues. (CTP 2.0)

o    sys.dm_db_stats_histogram is a new dynamic management view for examining statistics. (CTP 1.3)

o    sys.dm_os_host_info provides operating system information for both Windows and Linux. (CTP 1.0)

·         The Database Tuning Advisor (DTA) has additional options and improved performance. (CTP 1.2)

·         In-memory enhancements include support for computed columns in memory-optimized tables, full support for JSON functions in natively compiled modules, and the CROSS APPLY operator in natively compiled modules. (CTP 1.1)

·         New string functions are CONCAT_WS, TRANSLATE, and TRIM, and WITHIN GROUP is now supported for the STRING_AGG function. (CTP 1.1)

·         There are new bulk access options (BULK INSERT and OPENROWSET(BULK...) ) for CSV and Azure Blob files. (CTP 1.1)

·         Memory-optimized object enhancements include sp_spaceused and elimination of the 8 index limitation for memory-optimized tables, sp_rename for memory-optimized tables and natively compiled T-SQL modules, and CASE and TOP (N) WITH TIES for natively compiled T-SQL modules. Memory-optimized filegroup files can now be stored, backed up and restored on Azure Storage. (CTP 1.0)

·         DATABASE SCOPED CREDENTIAL is a new class of securable, supporting CONTROL, ALTER, REFERENCES, TAKE OWNERSHIP, and VIEW DEFINITION permissions. ADMINISTER DATABASE BULK OPERATIONS is now visible in sys.fn_builtin_permissions. (CTP 1.0)

·         Database COMPATIBILITY_LEVEL 140 is added. (CTP 1.0).

For more information, see What's new in SQL Server 2017 Database Engine.

SQL Server 2017 Integration Services (SSIS)

·         The new Scale Out feature in SSIS has the following new and changed features. For more info, see What's New in Integration Services in SQL Server 2017. (RC1)

o    Scale Out Master now supports high availability.

o    The failover handling of the execution logs from Scale Out Workers is improved.

o    The parameter runincluster of the stored procedure [catalog].[create_execution] is renamed to runinscaleout for consistency and readability.

o    The SSIS Catalog has a new global property to specify the default mode for executing SSIS packages.

·         In the new Scale Out for SSIS feature, you can now use the Use32BitRuntime parameter when you trigger execution. (CTP 2.1)

·         SQL Server 2017 Integration Services (SSIS) now supports SQL Server on Linux, and a new package lets you run SSIS packages on Linux from the command line. For more information, see the blog post announcing SSIS support for Linux. (CTP 2.1)

·         The new Scale Out for SSIS feature makes it much easier to run SSIS on multiple machines. See Integration Services Scale Out. (CTP 1.0)

·         OData Source and OData Connection Manager now support connecting to the OData feeds of Microsoft Dynamics AX Online and Microsoft Dynamics CRM Online. (CTP 1.0)

For more info, see What's New in Integration Services in SQL Server 2017.

SQL Server 2017 Master Data Services (MDS)

·         Experience and performance are improved when upgrading from SQL Server 2012, SQL Server 2014, and SQL Server 2016 to SQL Server 2017 Master Data Services.

·         You can now view the sorted lists of entities, collections and hierarchies in the Explorer page of the Web application.

·         Performance is improved for staging millions of records using the staging stored procedure.

·         Performance is improved when expanding the Entities folder on the Manage Groups page to assign model permissions. The Manage Groups page is located in the Security section of the Web application. For more information about the performance improvement, see https://support.microsoft.com/help/4023865?preview. For more information about assigning permissions, see Assign Model Object Permissions (Master Data Services).

SQL Server 2017 Analysis Services (SSAS)

SQL Server Analysis Services 2017 introduces many enhancements for tabular models. These include:

·         Tabular mode as the default installation option for Analysis Services. (CTP 2.0)

·         Object-level security to secure the metadata of tabular models. (CTP 2.0)

·         Date relationships to easily create relationships based on date fields. (CTP 2.0)

·         New Get Data (Power Query) data sources, and existing DirectQuery data sources support for M queries. (CTP 2.0)

·         DAX Editor for SSDT. (CTP 2.0)

·         Encoding hints, an advanced feature for optimizing data refresh of large in-memory tabular models. (CTP 1.3)

·         Support for the 1400 Compatibility level for tabular models. To create new or upgrade existing tabular model projects to the 1400 compatibility level, download and install SQL Server Data Tools (SSDT) 17.0 RC2. (CTP 1.1)

·         A modern Get Data experience for tabular models at the 1400 compatibility level. See the Analysis Services Team Blog. (CTP 1.1)

·         Hide Members property to hide blank members in ragged hierarchies. (CTP 1.1)

·         New Detail Rows end-user action to Show Details for aggregated information. SELECTCOLUMNS and DETAILROWS functions for creating Detail Rows expressions. (CTP 1.1)

·         DAX IN operator for specifying multiple values. (CTP 1.1)

For more information, see What's new in SQL Server Analysis Services 2017.

SQL Server 2017 Reporting Services (SSRS)

SQL Server Reporting Services is no longer available to install through SQL Server setup. Go to the Microsoft Download Center to download Microsoft SQL Server 2017 Reporting Services.

·         Comments are now available for reports, to add perspective and collaborate with others. You can also include attachments with comments.

·         In the latest releases of Report Builder and SQL Server Data Tools, you can create native DAX queries against supported SQL Server Analysis Services tabular data models by dragging and dropping desired fields in the query designers. See the Reporting Services blog.

·         To enable development of modern applications and customization, SSRS now supports a fully OpenAPI compliant RESTful API. The full API specification and documentation can now be found on swaggerhub.

For more information, see What's new in SQL Server Reporting Services (SSRS).

Machine Learning in SQL Server 2017

SQL Server R Services has been renamed SQL Server Machine Learning Services, to reflect support for Python in addition to the R language. You can use Machine Learning Services (In-Database) to run R or Python scripts in SQL Server, or install Microsoft Machine Learning Server (Standalone) to deploy and consume R and Python models that don't require SQL Server.

SQL Server developers now have access to the extensive Python ML and AI libraries available in the open-source ecosystem, along with the latest innovations from Microsoft:

·         revoscalepy - This Python equivalent of RevoScaleR includes parallel algorithms for linear and logistic regressions, decision tree, boosted trees and random forests, as well as a rich set of APIs for data transformation and data movement, remote compute contexts, and data sources.

·         microsoftml - This state-of-the-art package of machine learning algorithms and transforms with Python bindings includes deep neural networks, fast decision trees and decision forests, and optimized algorithms for linear and logistic regressions. You also get pre-trained models based on ResNet models that you can use for image extraction or sentiment analysis.

·         Python operationalization with T-SQL - Deploy Python code easily by using the stored procedure sp_execute_external_script. Get great performance by streaming data from SQL to Python processes and using MPI ring parallelization.

·         Python in SQL Server compute contexts - Data scientists and developers can execute Python code remotely from their development environments to explore data and develop models without moving data around.

·         Native scoring - The PREDICT function in Transact-SQL can be used to perform scoring in any instance of SQL Server 2017, even if R isn't installed. All that's required is that you train the model using one of the supported RevoScaleR and revoscalepy algorithms and save the model in a new, compact binary format.

·         Package management - T-SQL now supports the CREATE EXTERNAL LIBRARY statement, to give DBAs greater management over R packages. Use roles to control private or shared package access, store R packages in the database and share them among users.

·         Performance improvements - The stored procedure sp_execute_external_script has been optimized to support batch mode execution for columnstore data.

For more information, see What's new in SQL Server Machine Learning Services.

What's new in SQL Server 2016

·         ‎ APPLIES TO: SQL Server (starting with 2016) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse

With SQL Server 2016, you can build intelligent, mission-critical applications using a scalable, hybrid database platform that has everything built in, from in-memory performance and advanced security to in-database analytics. The SQL Server 2016 release adds new security features, querying capabilities, Hadoop and cloud integration, R analytics and more, along with numerous improvements and enhancements.

This page provides summary information and links to more detailed SQL Server 2016 what's new information for each SQL Server component.

Top 10 Features of SQL 2008 R2
Introduction
Report Builder 3.0
SQL Server 2008 R2 Datacenter
SQL Server 2008 R2 Parallel Data Warehouse
StreamInsight
Master Data Services
PowerPivot for SharePoint
Data-Tier Application
Unicode Compression
SQL Server Utility
Multi Server Dashboards
Conclusion

What's New in SQL Server 2008 R2
November Community Technology Preview (CTP)
Analysis Services
PowerPivot for Excel
PowerPivot Field List Enhancements
PowerPivot Data Source Editing
Support for More Types of Data Sources
Enhanced Data Analysis eXpressions (DAX)
PowerPivot for SharePoint
Management Dashboard
Enhanced Infrastructure
Database Engine — Manageability Enhancements
Connectivity to SQL Azure
Data-tier Application Upgrade
SQL Server Utility
Network Connectivity
Reporting Services
SharePoint Integration
Shared Datasets and Cache Refresh Plans
New Data Visualization Report Items
Enhancements to Reporting Services Features
Business Intelligence Development Studio Support for
SQL Server 2008 Reports and Report Server projects
New Web Service Endpoint
Service Broker
Master Data Services
StreamInsight
What's New in SQL Server 2005
Feature Summary

Topic

Description

Notification Services Enhancements

Notification Services is a new platform for building highly-scaled applications that send and receive notifications. Notification Services can send timely, personalized messages to thousands or millions of subscribers using a wide variety of devices.

Reporting Services Enhancements

Reporting Services is a new server-based reporting platform that supports report authoring, distribution, management, and end-user access.

New Service Broker

Service Broker is a new technology for building database-intensive applications that are secure, reliable, and scalable. Service Broker provides message queues the applications use to communicate requests and responses.

Database Engine Enhancements

The Database Engine introduces new programmability enhancements such as integration with the Microsoft .NET Framework and Transact-SQL enhancements, new XML functionality, and new data types. It also includes improvements to the scalability and availability of databases.

Data Access Interfaces Enhancements

SQL Server 2005 introduces improvements in the programming interfaces used to access data in SQL Server databases. For example, the SQL Native Client data access technology is new, and the .NET Framework Data Provider for SQL Server, also referred to as SqlClient, is enhanced.

Analysis Services Enhancements (SSAS)

Analysis Services introduces new management tools, an integrated development environment and integration with the .NET Framework. Many new features extend the data mining and analysis capabilities of Analysis Services.

Integration Services Enhancements

Integration Services introduces a new extensible architecture and a new designer that separates job flow from data flow and provides a rich set of control flow semantics. Integration Services also provides improvements to package management and deployment, along with many new packaged tasks and transformations.

Full-Text Search Enhancements

This section describes the programmability enhancements that have been made to Full-Text Search. These enhancements include the data definition language for Full-Text Search and the ability to specify languages in queries other than the default language, and the manageability enhancements, such as side-by-side installs, backup and restore full-text catalogs, and attach and detach full-text catalogs.

Replication Enhancements

Replication offers improvements in manageability, availability, programmability, mobility, scalability, and performance.

Tools and Utilities Enhancements

SQL Server 2005 introduces an integrated suite of management and development tools that improve the ease-of-use, manageability, and operations support for large scale SQL Server systems.

x

Try SQL Server today!

·         Download the free SQL Server 2016 Developer edition!.

·         Download the latest version of SQL Server Management Studio (SSMS).

·         Have an Azure account? Spin up a Virtual Machine with SQL Server 2016 already installed.

SQL Server 2016 Database Engine

·         You can now configure multiple tempDB database files during SQL Server installation and setup.

·         New Query Store stores query texts, execution plans, and performance metrics within the database, allowing easy monitoring and troubleshooting of performance issues. A dashboard shows which queries consumed the most time, memory or CPU resources.

·         Temporal tables are history tables which record all data changes, complete with the date and time they occurred.

·         New built-in JSON support in SQL Server supports JSON imports, exports, parsing and storing.

·         New PolyBase query engine integrates SQL Server with external data in Hadoop or Azure Blob storage. You can import and export data as well as executing queries.

·         The new Stretch Database feature lets you dynamically, securely archive data from a local SQL Server database to an Azure SQL database in the cloud. SQL Server automatically queries both local and remote data in the linked databases.

·         In-memory OLTP:

o    Now supports FOREIGN KEY, UNIQUE and CHECK constraints, and native compiled stored procedures OR, NOT, SELECT DISTINCT, OUTER JOIN, and subqueries in SELECT.

o    Supports tables up to 2TB (up from 256GB).

o    Has column store index enhancements for sorting and Always On Availability Group support.

·         New security features:

o    Always Encrypted: When enabled, only the application that has the encryption key can access the encrypted sensitive data in the SQL Server 2016 database. The key is never passed to SQL Server.

o    Dynamic Data Masking: If specified in the table definition, masked data is hidden from most users, and only users with UNMASK permission can see the complete data.

o    Row Level Security: Data access can be restricted at the database engine level, so users see only what is relevant to them.

See Database Engine.

SQL Server 2016 Analysis Services (SSAS)

SQL Server 2016 Analysis Services provides improved performance, authoring, database management, filtering, processing, and much more for tabular model databases based on the 1200 compatibility level.

·         SQL Server R Services integrate the R programming language, used for statistical analysis, into SQL Server.

·         New Database Consistency Checker (DBCC) runs internally to detect potential data corruption issues.

·         Direct Query, which queries live external data rather than importing it first, now supports more data sources, including Azure SQL, Oracle and Teradata.

·         There are numerous new DAX (Data Access Expressions) functions.

·         New Microsoft.AnalysisServices.Tabular namespace manages tabular mode instances and models.

·         Analysis Services Management Objects (AMO) is re-factored to include a second assembly, Microsoft.AnalysisServices.Core.dll.

See Analysis Services Engine (SSAS).

SQL Server 2016 Integration Services (SSIS)

·         Support for Always On Availability Groups

·         Incremental package deployment

·         Always Encrypted support

·         New ssis_logreader database-level role

·         New custom logging level

·         Column names for errors in the data flow

·         New connectors

·         Support for the Hadoop file system (HDFS)

See Integration Services (SSIS).

SQL Server 2016 Master Data Services (MDS)

·         Derived hierarchy improvements, including support for recursive and many-to-many hierarchies

·         Domain-based attribute filtering

·         Entity syncing for sharing entity data between models

·         Approval workflows via changesets

·         Custom indexes to improve query performance

·         New permission levels for improved security

·         Redesigned business rules management experience

See Master Data Services (MDS).

SQL Server 2016 Reporting Services (SSRS)

Microsoft has thoroughly revamped Reporting Services in this release.

·         New web Report Portal with KPI feature

·         New Mobile Report Publisher

·         Redesigned report rendering engine that supports HTML5

·         New treemap and sunburst chart types

What's New in SQL Server 2014

·         ‎SQL Server 2014 Service Pack 1 (SP1)

1.       SQL Server 2014 Service Pack 2 (SP2)

2.       Additional Information

This topic summarizes detailed links to new features in SQL Server 2014 and summarizes services packs for SQL Server 2014

Try it out: Have an Azure account? Then go Here to spin up a Virtual Machine with SQL Server 2014 Service Pack 1 (SP1) already installed.

·         What's New (Database Engine)

·         What's New in Analysis Services and Business Intelligence

·         What's New in SQL Server Installation

SQL Server 2014 has not introduced significant new features to the following:

·         What's New (Integration Services)

·         What's New (Reporting Services)

SQL Server 2014 Service Pack 1 (SP1)

SQL Server 2014 (SP1) did not introduce significant new features.

·         SQL Server 2014 Service Pack 1 release information.

·         Download Service Pack 1 for Microsoft?? SQL Server?? 2014.

SQL Server 2014 Service Pack 2 (SP2)

·         SQL Server 2014 Service Pack 2 release information.

·         Download Service Pack 2 for Microsoft?? SQL Server?? 2014.

·         Download the SQL Server 2014 SP2 Feature Pack.

SQL Server 2014 (SP2) Includes the following improvements:

Performance and Scalability Improvements

·         Automatic Soft NUMA partitioning: With SQL Server 2014 SP2, Automatic Soft NUMA is enabled when Trace Flag 8079 is turned on during instance startup. When Trace Flag 8079 is enabled during startup, SQL Server 2014 SP2 will interrogate the hardware layout and automatically configure Soft NUMA on systems reporting 8 or more CPUs per NUMA node. The automatic, soft NUMA behavior is Hyperthread (HT/logical processor) aware. The partitioning and creation of additional nodes scales background processing by increasing the number of listeners, scaling, and network and encryption capabilities. It is recommended to first test the performance workload with Auto-Soft NUMA before turning it in production. See the Blog for more information.

·         Dynamic Memory Object Scaling: SQL Server 2014 SP2 dynamically partitions memory objects based on number of nodes and cores to scale on modern hardware. The goal of dynamic promotion is to automatically partition a thread safe memory object (CMEMTHREAD) if it becomes a bottleneck. Un-partitioned memory objects can be dynamically promoted to be partitioned by node (number of partitions equals number of NUMA nodes), and memory objects partitioned by node can by further promoted to be partitioned by CPU (number of partitions equals number of CPUs). See the blog for more information.

·         **MAXDOP hint for DBCC CHECK* commands: This improvement addresses connect feedback (468694). You can now run DBCC CHECKDB with the a MAXDOP setting other than the sp_configure value. If MAXDOP exceeds the value configured with Resource Governor, the Database Engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP (Transact-SQL). All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint. For more information, see DBCC CHECKDB (Transact-SQL).

·         Enable >8TB for Buffer Pool: SQL Server 2014 SP2 enables 128TB of virtual address space for buffer pool usage. This improvement enables SQL Server Buffer Pool to scale beyond 8TB on modern hardware.

·         SOS_RWLock spinlock Improvement: The SOS_RWLock is a synchronization primitive used in various places throughout the SQL Server code base. As the name implies, the code can have multiple shared (readers) or single (writer) ownership. This improvement removes the need for spinlock for SOS_RWLock and instead uses lock-free techniques similar to in-memory OLTP. With this change, many threads can read a data structure protected by SOS_RWLock in parallel without blocking each other and thereby providing increased scalability. Prior to this change, the spinlock implementation allowed only one thread to acquire the SOS_RWLock at a time, even to read a data structure. See the blog for more information.

·         Spatial Native Implementation: Significant improvement in spatial query performance is introduced in SQL Server 2014 SP2 through native implementation. For more information, see the knowledge base article KB3107399.

Supportability and Diagnostics Improvements

·         Database Cloning: Clone database is a new DBCC command that enhances troubleshooting existing production databases by cloning the schema and metadata without the data. The clone is created with the command DBCC clonedatabase('source_database_name', 'clone_database_name'). Note: Cloned databases should not be used in production environments. Use the following command determine if a database has been generated from a cloned database: select DATABASEPROPERTYEX('clonedb', 'isClone'). The return value of 1 indicates the database is created from clonedatabase while 0 indicates it is not a clone.

·         Tempdb supportability: A new errorlog message that indicates the number of tempdb files and the size/autogrowth of tempdb data files present at server startup.

·         Database Instant File Initialization Logging: A new errorlog message that indicates on server statup, the status of Database Instant File Initialization (enabled/disabled).

·         Module names in callstack: The Xevent callstack now includes modules names + offset instead of absolute addresses.

·         New DMF for incremental statistics: This improvement addresses connect feedback (797156) to enable tracking the incremental statistics at the partition level. A new DMF sys.dm_db_incremental_stats_properties is introduced to expose information per-partition for incremental stats.

·         Index Usage DMV behavior updated:** This improvement addresses connect feedback (739566) from customers where rebuilding an index will not clear any existing row entry from sys.dm_db_index_usage_stats for that index. The behavior will now be the same as in SQL 2008 and SQL Server 2016. See the blog for more information.

·         Improved correlation between diagnostics XE and DMVs: This improvement addresses connect feedback (1934583). Query_hash and query_plan_hash are used for identifying a query uniquely. DMV defines them as varbinary(8), while XEvent defines them as UINT64. Since SQL server does not have "unisigned bigint", casting does not always work. This improvement introduces new XEvent action/filter columns equivalent to query_hash and query_plan_hash except they are defined as INT64 which can help correlating queries between XE and DMVs.

·         Support for UTF-8 in BULK INSERT and BCP: This improvement addresses connect feedback (370419) where support for export and import of data encoded in UTF-8 character set is now enabled in BULK INSERT and BCP.

·         Lightweight per-operator query execution profiling: While troubleshooting query performance, although showplan provides lot of information on the query execution plan and cost of operator in the plan but it has limited information on actual runtime statistics like (CPU, I/O Reads, elapsed time per-thread). SQL 2014 SP2 introduces these additional runtime statistics per operator in the Showplan as well as an XEvent (query_thread_profile) to assist troubleshooting query performance. See the blog for more information.

·         Change Tracking Cleanup: A new stored procedure sp_flush_CT_internal_table_on_demand is introduced to cleanup change tracking internal tables on demand.

·         AlwaysON Lease Timeout Logging Added new logging capability for Lease Timeout messages so that the current time and the expected renewal times are logged. Also a new message was introduced in the SQL Errorlog regarding the timeouts. See the blog for more information.

·         New DMF for retrieving input buffer in SQL Server: A new DMF for retrieving the input buffer for a session/request (sys.dm_exec_input_buffer) is now available. This is functionally equivalent to DBCC INPUTBUFFER. See the blog for more information.

·         Mitigation for underestimated and overestimated memory grant: Added new query hints for Resource Governor through MIN_GRANT_PERCENT and MAX_GRANT_PERCENT. This allows you to leverage these hints while running queries by capping their memory grants to prevent memory contention. For more information, see knowledge base article KB310740

·         Better memory grant/usage diagnostics: A new extended event was added to the list of tracing capabilities in SQL Server (query_memory_grant_usage) to track memory grants requested and granted. This provides better tracing and analysis capabilities for troubleshooting query execution issues related to memory grants. For more information, see knowledge base article KB3107173.

·         Query execution diagnostics for tempdb spill:- Hash Warning and Sort Warnings now have additional columns to track physical I/O statistics, memory used and rows affected. We also introduced a new hash_spill_details extended event. Now you can track more granular information for your hash and sort warnings (KB3107172). This improvement is also now exposed through the XML Query Plans in the form of a new attribute to the SpillToTempDbType complex type (KB3107400). Set statistics on now shows sort worktable statistics. .

·         Improved diagnostics for query execution plans that involve residual predicate pushdown: The actual rows read will now be reported in the query execution plans to help improve query performance troubleshooting. This should negate the need to capture SET STATISTICS IO separately. This now allows you to see information related to a residual predicate pushdown in a query plan. For more information, see knowledge base article KB3107397.

New SQL Server 2012 Features

So what does SQL Server 2012 have to offer over SQL Server 2008 R2? Following is an overview of the new features provided in SQL Server 2012:

·         New storage features—Columnstore indexes, FileTable storage.

·         New Transact-SQL (T-SQL) constructs—Sequence objects, THROW statement, new conversion, logical, string, and date and time functions, and ad hoc query paging.

·         New scalability and performance features—Indirect checkpoints, FORCESCAN table hint, number of table partitions increased to 15,000.

·         New security features—Database Audit, user-defined server roles, contained databases.

·         New availability features—A number of high-availability enhancements known as AlwaysOn, which include AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances.

·         Statistical Semantic Search—Statistical Semantic Search builds upon the existing full-text search feature in SQL Server by querying the contextual meaning of terms within a document repository.

·         Data Quality Services—This new feature allows you to build a knowledge base of data rules and use those to perform a variety of critical data quality tasks, including correction, enrichment, standardization, and de-duplication of your data.

The following sections take a closer look at each of these new features and, where appropriate, provide references to subsequent chapters where you can find more information and detail about the new features.

New and Improved Storage Features

SQL Server 2012 provides a few new features related to data storage, primarily intended to improve performance.

SQL Server 2012 provides an enhancement to FILESTREAM storage by allowing more than one filegroup to be used to store FILESTREAM data. This can improve I/O performance and scalability for FILESTREAM data by providing the ability to store the data on multiple drives. FILESTREAM storage, which was introduced in SQL Server 2008, integrates the SQL Server Database Engine with the NTFS file system, providing a means for storing unstructured data (such as documents, images, and videos) with the database storing a pointer to the data. Although the actual data resides outside the database in the NTFS file system, you can still use T-SQL statements to insert, update, query, and back up FILESTREAM data, while maintaining transactional consistency between the unstructured data and corresponding structured data with same level of security. For more information on using FILESTREAM storage, see Chapter 20, “Creating and Managing Tables.”

A new storage features introduced in SQL Server 2012 is FileTable storage. FileTable is the new type of table that was added in SQL Server 2012. The FileTable builds upon the FILESTREAM capability that was introduced in SQL Server 2008. Like FILESTREAM, FileTable storage provides SQL Server access to files that are stored in the Windows file system. FileTable differs from FILESTREAM in that FileTable allows for Windows applications to access the files that are part of the FileTable definition. This file access is nontransactional, and it comes without having to make changes to the Windows program that is accessing the files. For more information on FileTable storage, see Chapter 44, “What’s New for Transact-SQL in SQL Server 2012.”

SQL Server 2012 also introduces a new data warehouse query acceleration feature based on a new type of index called an xVelocity memory optimized columnstore. This new index, combined with enhanced query processing features, improves data warehouse query performance by hundreds to thousands of times in some cases and can routinely give a tenfold speedup for a broad range of decision support queries. Columnstore indexes can limit or eliminate the need to rely on prebuilt aggregates, including user-defined summary tables and indexed (materialized) views. Unlike typical row storage, which stores multiple rows of data per page, a columnstore index stores the values for a single column in its own set of disk pages. The advantage of columnar storage is the ability to read the values of a specific column of a table without having to read the values of all the other columns. For more information on the structure of columnstore indexes, see Chapter 30, “Data Structures, Indexes, and Performance.” For more information on creating and using columnstore indexes, see Chapter 57, “Parallel Data Warehouse Appliance.”

New Transact-SQL Constructs

What would a new SQL Server release be without new T-SQL commands and constructs to further expand the power and capabilities of the T-SQL language? SQL Server 2012 is no exception. The new constructs provided in SQL Server 2012 include the following:

·         Sequence objects—Sequence objects can be used to generate a sequence of numeric values according to the definition of the object. Sequence objects provide an alternative for generating autosequencing values, similar to an Identity column but independent of any specific table. Sequence objects provide more flexibility and allow applications to apply a sequence value across multiple tables.

·         THROW statement—The THROW statement can be used to raise an error condition and transfer execution to a CATCH block of a TRY...CATCH construct or to re-raise the error condition that invoked the CATCH block.

·         New conversion functions—PARSE, TRY_PARSE, and TRY_CONVERT.

·         New logical functions—CHOOSE and IIF.

·         New string functions—CONCAT and FORMAT.

·         New date and time functions—DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, SMALLDATETIMEFROMPARTS, TIMEFROMPARTS.

·         New ROWS and RANGE Clauses—Provides support for support for windows framing, which can be used to restrict the results to a subset of rows by specifying the start and end points within the partition of the OVER clause.

·         New window offset functions—LAG and LEAD functions used in conjunction with OVER clause let you return a value from a specific row that’s a certain offset from the current row.

·         New window rank distribution functions—PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, CUME_DIST.

·         Ad hoc query paging—Provides a mechanism to implement paging using the relatively simple syntax provided by the ANSI standard ORDER BY ... OFFSET / FETCH clause.

For more information and examples of the new T-SQL features and enhancements, see Chapter 44.

New Scalability and Performance Features

SQL Server provides some new scalability and performance features including indirect checkpoints and the FORCESCAN table hint.

Indirect checkpoints provide a mechanism for configuring checkpoint intervals at the individual database level. Indirect checkpoints can provide potentially faster, more predictable recovery times for your critical databases than automatic checkpoints. For more information on indirect checkpoints, see Chapter 27, “Transaction Management and the Transaction Log.”

The new FORCESCAN table hint complements the FORCESEEK hint by allowing you to specify that the query optimizer use an index scan operation as the access path to the table or view referenced in the query. For more information on the FORCESCAN table hint, see Chapter 31, “Understanding Query Optimization.”

New Security Features

SQL Server 2012 introduces Database Audit. Similar to SQL Server Audit, Database Audit is based on the new Extended Events feature and enables you to audit database-level events or groups of events. For more information on Database Audit, see Chapter 16, “Security and Compliance.”

SQL Server 2012 also adds user-defined server roles. These new server roles provide the same type of flexibility that is available with user-defined database roles, but they are instead defined at the server level and improve manageability of the permissions at the server level, giving administrators an option that goes well beyond the rigid fixed server roles. For more information on creating user-defined server roles, see Chapter 17, “Security and User Administration.”

Contained database support was added in SQL Server 2012 along with the related changes to support authentication to this type of database. Users can now be added to a database without having a corresponding SQL Server login. Authentication instead takes place at the database level. Contained databases can be easily moved from one server to another without the authentication issues that make noncontained database migrations more difficult. For more information on configuring contained databases, see Chapter 19, “Creating and Managing Databases.”

New Availability Features

One of the key new features in SQL Server 2012 is the AlwaysOn availability features. The new AlwaysOn features provide SQL Server administrators more power and flexibility in their efforts toward providing both high availability and disaster recovery. The AlwaysOn features consist of AlwaysOn Availability Groups and AlwaysOn Failover Cluster Instances.

AlwaysOn Availability Groups support a failover environment for a discrete set of user databases that fail over together. A single SQL Server instance can host multiple availability groups. In the event of a failure, each availability group can be configured to fail over to different SQL Server instances. For example, one availability group can fail over to instance 2, another availability group to instance 3, and so on. You no longer need to have a standby server that is capable of handling the full load of your primary server. An availability group consists of a set of one or more read-write primary databases and from one to four remote secondary copies. The remote secondary databases can be set up as read-only copies that you can run certain backup operations and reporting activity against, taking significant load off the primary server without the maintenance and overhead of creating snapshots of the secondary databases.

The other feature that is part of the AlwaysOn Availability offering is AlwaysOn Failover Cluster Instances. This feature is an enhancement to the existing SQL Server failover clustering, which is based on Windows Server Failover Cluster (WSFC). AlwaysOn Failover Clustering provides higher availability of SQL Server instance after failover. Some of the improvements of AlwaysOn Failover Clustering over the existing SQL Server failover clustering include the ability to set up multisite failover clustering for improved site protection, more-flexible failover policies, and improved diagnostics capabilities.

For more information on the AlwaysOn Availability features of SQL Server 2012, see Chapter 43, “SQL Server 2012 AlwaysOn Features.”

Statistical Semantic Search

Statistical Semantic Search builds upon the existing full-text search feature in SQL Server but enables new scenarios that extend beyond keyword searches. While full-text search lets you query the words in a document, Semantic Search lets you query the meaning of the document. Semantic Search attempts to improve document searches by understanding the contextual meaning of the terms and tries to provide the most accurate answer from a given document repository. If you use a web search engine like Google, you are already familiar with Semantic Search technology.

SQL Server Semantic Search builds its indexes using the indexes created by full-text search. With Semantic Search, you can go beyond just searching for specific words or strings in a document. Solutions are possible that include automatic tag extraction, related content discovery, and hierarchical navigation across similar content. For example, you can query the index of key phrases to build the taxonomy for an organization, or you can query the document similarity index to identify resumés that match a particular job description.

For more information on Statistical Semantic Search, see Chapter 51, “SQL Server Full-Text Search.”

Data Quality Services

Within Master Data Services (MDS), SQL Server 2012 adds Data Quality Services. Data Quality Services complements MDS and is usable by other key data manipulation components within the SQL Server environment. This feature enables you to build a knowledge base of data rules and use those to perform a variety of critical data quality tasks, including correction, enrichment, standardization, and de-duplication of your data. This also includes the ability to do some basic data profiling to better understand the integrity and overall data quality state of your core data.

Data Quality Services (DQS) consists of a Data Quality Server and a Data Quality Client. The DQS Server consists of three SQL Server catalogs with data-quality functionality and storage. The Data Quality Client is a SQL Server shared feature that business users, information workers, and IT professionals can use to perform computer-assisted data quality analyses and manage their data quality interactively. These DQS Cleansing components can also be used in Integration Services and MDS.

For more information on working with Data Quality Services, see Chapter 56, “Master Data Services and Data Quality Services.”

By Don Schlichting
Microsoft SQL Server 2008 R2 is the latest release of SQL Server. This article will introduce the top 10 features and benefits of SQL Server 2008 R2. The “R2” tag indicates this is an intermediate release of SQL Server and not a major revision. However, there are a number of interesting new features for both DBAs and developers alike. At the time of this article, R2 is available as a CTP (Community Technology Preview). In addition to new features, there are two new editions as well, SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel Data Warehouse.
Report Builder is a tool set for developing rich reports that can be delivered over the web. Some of the features of Report Builder include the ability to create reports containing graphs, charts, tables, and printing controls. In addition, Report Builder also supports drill downs and sorting. If you are familiar with the third party tool Crystal Reports, then you have good idea of what to expect from Report Builder.
New features in SQL 2008 R2 / Report Builder 3.0 include: Map Layers, which can hold spatial and analytical data and will integrate with Microsoft Virtual Earth. Indicators, these are gauges used to show the state of one value. Report Parts, this object can be reused or shared between multiple reports. Aggregate Calculating, this allows you to calculate the total value of other aggregate calculated totals.
The new Datacenter edition of SQL Server 2008 R2 is targeted towards Enterprise Edition users who require a greater performance platform. The new edition will support 256 logical processors, high numbers of instances, and as much memory as the operating system will support.
Another new SQL Server edition, Parallel Data Warehouse, formally codenamed “Madison”, specializes in handling extremely large amounts of data. This new version uses massively parallel processing to spread large tables over multiple SQL nodes. The multiple nodes are handled by a propriety Microsoft technology called Ultra Shared Nothing. This new technology is described as a Control Node spreading queries to Computer Nodes, evenly distributed, then collecting the results.
New in SQL Server 2008 R2 is component called StreamInsight. This interesting component allows streaming data to be analyzed on the fly. Meaning the data is processed directly from the source stream prior to being saved in a SQL Server table. This could be extremely handy if you’re running a real time system and need to analyze data but can’t afford the latency of a committed write to a table first. Examples usually cited for this application include stock trading streams, click stream web analytics, and industrial process controls. Multiple input streams can be simultaneously monitored.
Master Data Services (MDS) is both a concept and a product. The concept of a Master Data Service is that there is a central data gate keeper of core business data. Data items such as customer billing addresses, employee/customer names, and product names should be centrally managed so that all consuming applications have the same information. The Microsoft example given is a company that has a customer address record in the customer table but a different address in the mailing table. A Master Data Service application would ensure that all tables would have only one correct address. While an MDS can be a homegrown application, SQL Server 2008 R2 includes an application and an interface to manage the central data.
PowerPivot is an end-user tool that works in conjunction with SharePoint, SQL Server 2008 R2, and Excel 2010 to process large amounts of data in seconds. PowerPivot works like an Excel Pivot Table, and includes analytical capabilities.
A Data-Tier Application (abbreviated as DAC –no idea what the C stands for, and not to be confused with the Windows Data Access Components also abbreviated as DAC ) is an object that stores all the needed database information for a project, such as login, tables, and procedures into one package that can be consumed by Visual Studio. By creating a Data-Tier Application, a SQL Server package version could be saved with each Visual Studio build of your application. This would allow application code builds to be married to a database build in an easily managed way.
SQL Server 2008 R2 uses a new algorithm known as Simple Compression Scheme for Unicode storage. This reduces the amount of disk spaced used by Unicode characters. This new format happens automatically and is managed by the SQL Server engine so no programming changes are required of the DBA.
The new SQL Server Utility is a repository object for centrally controlling multiple SQL Server instances. Performance data and configuration policies can be stored in a single Utility. The Utility also includes an Explorer tool where multi-server dashboards can be created.
While the SQL Server Management Studio could always connection to multiple servers, each was managed independently with no central view of all of them. Now with SQL Server 2008 R2, Dashboards showing combined server data can be created.
SQL Server 2008 R2 is the latest release of Microsoft SQL Server. The “R2” tag indicates this is an intermediate release of SQL Server and not a major revision. There are a number of compelling features in this version for both developers and DBAs alike. Here are the Top 10 new features in SQL Server 2008 R2:
1. Report Builder 3.0
2. SQL Server 2008 R2 Datacenter
3. SQL Server 2008 R2 Parallel Data Warehouse
4. StreamInsight
5. Master Data Services
6. PowerPivot for SharePoint
7. Data-Tier Application
8. Unicode Compression
9. SQL Server Utility

10.   Multi Server Dashboards

This document describes what's new in the November CTP of Microsoft® SQL Server® 2008 R2. The following components have new or enhanced features:

·         Analysis Services

·         Database Engine—Manageability

·         Reporting Services

·         Service Broker

·         Master Data Services

·         StreamInsight

If you installed and used the SQL Server 2008 R2 August CTP, you can use the information in this document to read about enhancements in the November CTP.

Note   Some PowerPivot for SharePoint features were previously named using the "Gemini" codename. The codename is being replaced by final names that will be used in the product release.

For more information about PowerPivot, see the topics "PowerPivot Overview," "PowerPivot Features," "PowerPivot Concepts," and "PowerPivot Components and Tools" in SQL Server 2008 R2 Books Online.

Numerous enhancements have been added to the PowerPivot field list. These include the following:

·         Automatic detection of relationships when columns from unrelated tables are used in a PivotTable.

·         Support for searching fields by name.

·         Support for named sets.

Support is available for the following data source editing tasks:

·         Refreshing data for table, for all tables from a data source, or for all the tables in the PowerPivot window.

·         Editing data source information such as server name, database name, and credentials.

·         Editing table settings such as data source table or query used, columns imported, and filters.

Targeted support has been introduced for importing data from the following sources:

·         Microsoft Access 2003, 2007, 2010

·         Microsoft SQL Server 2005, 2008, 2008 R2 Analysis Services

·         PowerPivot workbooks published to Analysis Services running in SharePoint Integrated Mode

·         Text files

·         Excel 97-2003, 2007, 2010

·         Microsoft SQL Azure

·         Oracle 9i, 10g, 11g

·         Teradata V2R6, V12

·         Sybase

·         Informix

·         IBM DB2 relational databases 8.1

DAX functionality has been significantly expanded in the following ways:

·         User interface enhancements include better propagation of errors and easier creation of DAX formulas.

·         Several functions have been added, including a set of functions that operate on textual data, as well as those that provide common Date and Time manipulations.

·         Several performance and functional capabilities have been introduced, including automatic recalculation.

·         Support for handling referential integrity violations between related tables has been introduced.

·         Automatic selection of data types for expressions is available.

For more information, see the topic "Data Analysis Expression Language Reference" in SQL Server 2008 R2 Books Online.

DAX is also documented in the online Help of the PowerPivot for Excel add-in. For more information, see the Help file that is installed with the add-in.

A management dashboard for IT has been introduced. This dashboard provides visibility into PowerPivot use on a SharePoint farm that includes information about the following areas, as well as a rich PowerPivot workbook that can be used to build custom reports:

·         Published PowerPivot workbooks, including number of queries executed, number of users and size per workbook in the dashboard.

·         Hardware resource utilization for the PowerPivot service, including CPU and memory, is reported on a daily basis to the dashboard.

·         Data refresh activity is reported for all PowerPoint workbooks that are accessed through the same PowerPivot service application.

For more information about this feature, see the topic "PowerPivot Management Dashboard" in SQL Server 2008 R2 Books Online.

The following enhancements to infrastructure are available:

·         Claims-aware support to enable a user's identity to flow between SharePoint components in a secure manner.

·         Parallelized refresh of PowerPivot data.

·         Performance optimizations.

The ability to connect to Microsoft SQL Azure Database from the client utilities has been introduced:

·         The Generate and Publish Scripts Wizard can use SQL Azure as both the source and destination for the scripts it publishes.

The November CTP introduces several new features for the data-tier applications that were introduced in the August CTP:

·         You can upgrade a deployed DAC from one version to another.

·         You can unpack a DAC package to review the contents before using the package to deploy or upgrade the DAC. Also, the DAC package file is now a zipped file containing multiple XML files.

·         You can now register a DAC for an existing database, creating a DAC instance.

The November CTP provides enhanced functionality for SQL Server Utility features that were introduced in the August CTP:

·         A Getting Started page in SQL Server Management Studio.

·         Improved performance and scale.

·         Improved workflow for removing a managed instance of SQL Server from the SQL Server Utility.

·         Sample scripts for create UCP, enroll instance, and remove instance operations.

The VIA protocol has been deprecated.

SharePoint Integration has several new features that include the following:

·         Support for multiple SharePoint Zones.

·         Support for the SharePoint Universal Logging service.

·         A new data extension.

·         A query designer for SharePoint Lists as a data source.

·         Support for right-to-left text with Arabic and Hebrew in the SharePoint user interface.

·         In a report, the data extension supports include data from SharePoint lists for SharePoint Foundation 2010, SharePoint Server 2010, Windows SharePoint Services 3.0, and Office SharePoint Server 2007.

·         Report Parts.

Shared datasets are a new type of report server item that can retrieve data from shared data sources that connect to external data sources.

Cache refresh plans let you cache reports or shared dataset query results on first use or from a schedule.

The November CTP introduces several new report items that depict data:

·         Sparklines and data bars are simple charts that convey a lot of information in a little space. These are often used in tables and matrices. Their impact comes from viewing many of them together and being able to quickly compare them, instead of viewing them singly.

·         Indicators are minimal gauges that convey the state of a single data values at glance.

The November CTP provides the following enhancements to Reporting Services features that were released in earlier SQL Server 2008 R2 CTPs or SQL Server 2008:

·         Configuring a Map Layer—The Map wizard displays data from both the spatial data source and the analytical data source to help you choose the field on which to match.

·         Calculating Aggregates of Aggregates—You can create expressions that calculate an aggregate of an aggregate.

·         Enhanced Support for Expressions—New globals and a new property for report variables provide support for overall page numbering, naming pages, and information that is specified in the renderer.

·         Rotating Text 270 Degrees—Text boxes can now be rotated 270 degrees.

·         Report Pagination—Page breaks on tablix data regions (table, matrix, and list), groups, and rectangles give you better control of report pagination.

·         Naming Excel worksheet tabs—You can set properties on the report to name worksheet tab when you export the report to Excel.

Business Intelligence Development Studio supports working with both SQL Server 2008 and SQL Server 2008 R2 reports, and with Report Server projects in the SQL Server 2008 R2 version of Business Intelligence Development Studio.

The report server includes a new management endpoint named T:ReportService2010.ReportingService2010. This endpoint merges the functionalities of both the T:ReportService2005.ReportingService2005 and T:ReportService2006.ReportingService2006 endpoints, and can support management operations of the report server in both native mode and SharePoint integrated mode. It also includes new features that are introduced in SQL Server 2008 R2, such as shared datasets and cache refresh.

The November CTP introduces the option to turn on and off poison message handling on a queue. A queue that has poison message turned off will not be disabled after consecutive transaction rollbacks. With this feature, a custom poison message handing strategy can be defined by an application if you want. The Transact-SQL CREATE QUEUE and ALTER QUEUE statements have been updated, and the IsPoisonMessageHandlingEnabled property has been added to the Microsoft.SqlServer.Management.Smo.Broker.ServiceQueue API.

Master Data Services is comprised of a database, configuration tool, Web application, and Web service that you use to manage your organization's master data and maintain an auditable record of that data as it changes over time. You use models and hierarchies to group and organize data to prepare it for further use in business intelligence and reporting tools, data warehouses, and other operational systems. Master Data Services integrates with source systems and incorporates business rules to become the single source of master data across your organization.

Microsoft StreamInsight is a powerful platform that you can use to develop and deploy complex event processing (CEP) applications. Its high-throughput stream processing architecture and the Microsoft .NET Framework-based development platform enable you to quickly implement robust and highly efficient event processing applications. Event stream sources typically include data from manufacturing applications, financial trading applications, Web analytics, and operational analytics. By using StreamInsight, you can develop CEP applications that derive immediate business value from this raw data by reducing the cost of extracting, analyzing, and correlating the data; and by allowing you to monitor, manage, and mine the data for conditions, opportunities, and defects almost instantly. StreamInsight samples and documentation can be installed from the StreamInsight folder on the SQL Server 2008 R2 media.

Microsoft SQL Server 2005 extends the performance, reliability, availability, programmability, and ease-of-use of SQL Server 2000. SQL Server 2005 includes several new features that make it an excellent database platform for large-scale online transactional processing (OLTP), data warehousing, and e-commerce applications.

The following topics discuss the new features in the SQL Server 2005 components.

Topic	Description
Notification Services Enhancements	Notification Services is a new platform for building highly-scaled applications that send and receive notifications. Notification Services can send timely, personalized messages to thousands or millions of subscribers using a wide variety of devices.
Reporting Services Enhancements	Reporting Services is a new server-based reporting platform that supports report authoring, distribution, management, and end-user access.
New Service Broker	Service Broker is a new technology for building database-intensive applications that are secure, reliable, and scalable. Service Broker provides message queues the applications use to communicate requests and responses.
Database Engine Enhancements	The Database Engine introduces new programmability enhancements such as integration with the Microsoft .NET Framework and Transact-SQL enhancements, new XML functionality, and new data types. It also includes improvements to the scalability and availability of databases.
Data Access Interfaces Enhancements	SQL Server 2005 introduces improvements in the programming interfaces used to access data in SQL Server databases. For example, the SQL Native Client data access technology is new, and the .NET Framework Data Provider for SQL Server, also referred to as SqlClient, is enhanced.
Analysis Services Enhancements (SSAS)	Analysis Services introduces new management tools, an integrated development environment and integration with the .NET Framework. Many new features extend the data mining and analysis capabilities of Analysis Services.
Integration Services Enhancements	Integration Services introduces a new extensible architecture and a new designer that separates job flow from data flow and provides a rich set of control flow semantics. Integration Services also provides improvements to package management and deployment, along with many new packaged tasks and transformations.
Full-Text Search Enhancements	This section describes the programmability enhancements that have been made to Full-Text Search. These enhancements include the data definition language for Full-Text Search and the ability to specify languages in queries other than the default language, and the manageability enhancements, such as side-by-side installs, backup and restore full-text catalogs, and attach and detach full-text catalogs.
Replication Enhancements	Replication offers improvements in manageability, availability, programmability, mobility, scalability, and performance.
Tools and Utilities Enhancements	SQL Server 2005 introduces an integrated suite of management and development tools that improve the ease-of-use, manageability, and operations support for large scale SQL Server systems.

Integrated Secuirty = SSPI

noreply@blogger.com (Deepak Kumar Kalyanum) — Wed, 11 Apr 2018 20:34:00 +0000

This is an interesting one - anyone with accurate answers?

Dynamic data loading of tab delimited text files using stream reader.

noreply@blogger.com (Deepak Kumar Kalyanum) — Thu, 05 Oct 2017 07:42:00 +0000

So I had this recent POC work , urgently needed by the client. Here is the requirement :

Load input tab delimited text files. The column names might change, columns will be added , removed dynamically.
Some files have a weird format , but which is consistent - mostly a single line will be split into 2 lines.
A data warehouse to be built based on the text files.

Design approach :

Use Stream Reader to load all the text files into a common staging table first.
Perform transformations on top of the staging table to load into the final data mart. Either use SSIS entirely or SQL code.

Output :

Output should hold a normalized data from these text files.

Technicalities : SSIS documentor generated - just a gist of contents :

Contents

ErrorDataMartLoad

Sequence Container

Mail - Sent Error Email Notification

XSQL - UPDATE_ERRORCOLUMNVALUE_IN_FACT

FST - ARCHIVE_EMPTY_FILES

Trips and tricks on T-SQL ,SSIS,SSAS, SSRS , datawarehousing,Interview questions,reuse tools and utilities.

noreply@blogger.com (Deepak Kumar Kalyanum) — Wed, 06 Apr 2016 13:24:00 +0000

I will be updating this soon.

Add a new table and import from the data source - sometimes confusing and results in a new connection - Here is a review.

noreply@blogger.com (Deepak Kumar Kalyanum) — Wed, 06 Apr 2016 13:19:00 +0000

Just to Model : Existing Connections - Click on Open , that is it - will enable you to import new objects not resulting in a new connection - other wise from the Table menu , it creates a new connection.

QUERY HINTS and effects on performance.

noreply@blogger.com (Deepak Kumar Kalyanum) — Wed, 06 Apr 2016 13:16:00 +0000

OPTION HASH INDEX decreased the amount of query time - will provide more details on it.

Dimensional modelling to Data Vault.

noreply@blogger.com (Deepak Kumar Kalyanum) — Tue, 15 Mar 2016 04:39:00 +0000

Isolation levels always wondered - a very useful reference.

noreply@blogger.com (Deepak Kumar Kalyanum) — Tue, 15 Mar 2016 04:38:00 +0000

https://www.simple-talk.com/sql/t-sql-programming/questions-about-t-sql-transaction-isolation-levels-you-were-too-shy-to-ask/

New BI ETL tools.

noreply@blogger.com (Deepak Kumar Kalyanum) — Mon, 14 Mar 2016 15:30:00 +0000

MIST,Data Vault. Quite interesting.

Full Text search in SQL Server.

noreply@blogger.com (Deepak Kumar Kalyanum) — Mon, 01 Dec 2014 11:23:00 +0000

People understand this feature has been mentioned in MSDN article , but this just gives a gist of most important observations / excerpts out from there :

Feature introduced in SQL server 2008 and then propagated to 2008R2,2012 , and 2014.

Full Text index to be maintained on the table before a FULL TEXT query can be applied.

When Full text is heard , four key terms come into mind :

full-text predicates (CONTAINS and FREETEXT) and functions (CONTAINSTABLE and FREETEXTTABLE

Key differences between Full Text and LIKE. Worth of them like Full Text is faster than LIKE.

Will continue again .....

POWER function , DBCCSHOWCONTIG.

noreply@blogger.com (Deepak Kumar Kalyanum) — Tue, 25 Nov 2014 07:46:00 +0000

POWER function - is straight forward - gives you the power value of a value.
DBCCSHOWCONTIG - a very good piece of code -

USE AdventureWorks2012;
GO
DECLARE @id int, @indid int
SET @id = OBJECT_ID('Production.Product')
SELECT @indid = index_id
FROM sys.indexes
WHERE object_id = @id
AND name = 'AK_Product_Name'
DBCC SHOWCONTIG (@id, @indid);
GO

References :

http://msdn.microsoft.com/en-IN/library/ms174276.aspx - POWER
http://msdn.microsoft.com/en-us/library/ms175008.aspx - DBCC SHOWCONTIG.

Preparing a book on all my BI experience and learning.

noreply@blogger.com (Deepak Kumar Kalyanum) — Mon, 24 Nov 2014 20:04:00 +0000

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SSRS challenges.

noreply@blogger.com (Deepak Kumar Kalyanum) — Sun, 08 Jun 2014 08:55:00 +0000

Very good and useful blog from Ryan Duclos :

https://www.simple-talk.com/sql/reporting-services/ten-common-sql-server-reporting-services-challenges-and-solutions/#hr

SSRS body properties

noreply@blogger.com (Deepak Kumar Kalyanum) — Sat, 07 Jun 2014 20:38:00 +0000

Sometimes , there is a confusion on where can the body properties be set when there is already existing tablix available in the report.

Solution is to create some space by dragging in the body where the already created tablix is created. Click on the open space created by the drag and then its properties can be edited.

Switch SSRS server modes

noreply@blogger.com (Deepak Kumar Kalyanum) — Tue, 03 Jun 2014 20:10:00 +0000

We can change the server mode from Sharepoint to native after the installation. Possible through Reporting Services Configuration Manager. There is a nice article on Books Online for this.

Scripts..Scripts...Scripts. Whats with them in SSIS.?

noreply@blogger.com (Deepak Kumar Kalyanum) — Tue, 03 Jun 2014 20:06:00 +0000

Common issues with script task which are being commonly raised over forums :

1 - It does not work as expected and the functionality is disturbed haywire.
2 -Breakpoints do not work.

Most of the cases the following steps resolve these issues :

a - Recreate the script task and paste your code ( backup the code prior to this).
b - Recompile the script task.

Weird Script task issue faced recently.

noreply@blogger.com (Deepak Kumar Kalyanum) — Sat, 31 May 2014 13:05:00 +0000

One of the script tasks did not run in job , but when brought out and compiled , the result is a success.
The root cause is unknown still. Gathering all the inputs for this weird issue.

Please check this thread which is still open.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7db71f60-8deb-4fd8-8bc9-13103e803bba/weird-or-an-inherent-issue-or-a-bug-?forum=sqlintegrationservices

Folks who are reading this blog , can provide any insights.

Version and other details : SSIS 2008 , running in a batch.

SSIS 2012 does not succeed , but there is no error.

noreply@blogger.com (Deepak Kumar Kalyanum) — Wed, 21 May 2014 20:06:00 +0000

One of the SSIS 2012 strange behavior question put up on forums - Please check here :

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6130d2e1-0c2e-47e0-930a-a3745e03a944/fake-errors-within-ssis-package?forum=sqlintegrationservices

Checkpoint disadvantages in SSIS.

noreply@blogger.com (Deepak Kumar Kalyanum) — Wed, 21 May 2014 20:00:00 +0000

Object variables are not stored in checkpoints - one of the drawbacks in checkpoints.

Check Jamie Thompson's nice blog on checkpoint drawbacks.

http://sqlblog.com/blogs/jamie_thomson/archive/2011/03/10/why-i-don-t-use-ssis-checkpoint-files.aspx

Good one.

Interesting property of SSIS variables - IncludeInDebugDump.

noreply@blogger.com (Deepak Kumar Kalyanum) — Wed, 21 May 2014 19:51:00 +0000

Please check the msdn article here : http://msdn.microsoft.com/en-us/library/ms141085.aspx

Will be back soon on my blog!

noreply@blogger.com (Deepak Kumar Kalyanum) — Sun, 06 Apr 2014 16:29:00 +0000

Boolean Calculated Measure in SSAS cube.

noreply@blogger.com (Deepak Kumar Kalyanum) — Fri, 07 Oct 2011 11:04:00 +0000

This post describes a way to create a calculated mesaure which is boolean (TRUE/FALSE):

1. Consider 2 measures x and y . Lets create a new calculated measure called z derived out of x and y.
2. z is boolean and is determined by the x and y values. How ? lets assume z = x - y
3. If x > y then , lets say z has to show false else true.
4. Frame an MDX in the formula for calculated measure like :

NOT (Measures.x - Measures.y)

5. The format string of z should be "True/False".

Dynamic data load in script task without a data flow task.( under copyrights)

noreply@blogger.com (Deepak Kumar Kalyanum) — Sat, 27 Aug 2011 19:38:00 +0000

This post / development has been pending and pondering in my mind and I am quite relieved now after what I achieved the dynamic data load in script task. Mind you , there is no data flow task involved here :-). This is after some heavy time investment during weekends …

What does this solution talk about ?

1. All about the data load from excel source into destination tables in SSIS using script task. Only script task.
2. While I insist on excel source , this can well be extended to table to table, table to excel , excel to excel.
3. A great characteristic I induced into this : Data validations. It can detect the data type mismatches between source and destination. While I tested this on excel to table. It could well be possible from table to table. But for table to excel and excel to excel , need more insight.
4. Error handling/ data type validation is designed for Decimal, INT, NVARCHAR (length) which can be customized further and further validations for varchar and other types can be added.

What are the inputs provided , prerequisites and limitations for this solution and enhancements which can be made further :

1. The source excel sheet has sheets named Sheet_test1$ and Sheet_test2$. (for test).
2. The table naming conventions are followed according to the source sheet names.(Wrk_Sheet_test1 and Wrk_Sheet_test2). The table names should be in hand before the dynamic data load as obvious it is , the tables are project specific. While as an alternative , all the table list can be stored in a table and they can be looped through to load the data into corresponding tables from the source sheets.

What are the advantages of this solution:

1. This package can load the data from these sheets irrespective of number of columns being added in future or irrespective of number of sheets added in future and it is very flexible.
2. The good feature is data type validation and error column detection. The error is logged into a separate error table. On your mark on error column detection with a very good specific error message with respect to data type validations.

Notes :

1. As far as testing is considered , I had this idea tested quite thoroughly in some form or the other(reliable ), but I would like to still burn down my arms on testing and customizing this.
2. I can really insist on the fact that this solution has some great deal of .NET coding in VB in script task. Now apart from that , you could learn how to loop through the source columns whether it can be excel or excel table.
3. There are many other interesting facts when you go through the package.

Here is some preview of code to loop through the columns :

For excel : ColumnsInTable = cnnXls.GetSchema("COLUMNS", columnRestrictions)
For database connections here is a special case of connection casting(otherwise the connection does not work inside the script):

'CONNECT TO THE DESTINATION DATABASE
DatabaseConnectionManager = DtsConvert.GetExtendedInterface(Dts.Connections("DatabaseConnection"))
DatabaseConnectionString = DatabaseConnectionManager.ConnectionString
'MAKE AN ARRAY OF THE CONNECTION STRING OF THE OLEDB CONNECTION.
arrDatabaseConnectionString = DatabaseConnectionManager.ConnectionString.Split(";")
'CREATE A NEW SQL CONNECTION , WE CANNOT CAST THE EXISTING OLEDB CONNECTION TO SQL CONNECTION AS UNMANAGED
'CONNECTIONS CANNOT BE CASTED UNLESS IT IT ADO.NET CONNECTION
oSQLCnn.ConnectionString = arrDatabaseConnectionString(0) + ";" + arrDatabaseConnectionString(1) + ";" + arrDatabaseConnectionString(3) + ";"
oSQLCnn.Open()

Where can you find the solution(the package source code) , scripts and source sheets to test this and also you can customize on your own way :

 https://skydrive.live.com/?cid=c43b6c5c822dd98a&sc=documents&id=C43B6C5C822DD98A%21107#!/?cid=c43b6c5c822dd98a&permissionsChanged=1&id=C43B6C5C822DD98A%21104

Revamp!

noreply@blogger.com (Deepak Kumar Kalyanum) — Tue, 23 Aug 2011 08:02:00 +0000

Folks who read my blog, I am in a revamp of this blog. The existing posts are being renovated with additional steps and screen shots. The following are some of them which are being renovated : 1) Data validation in script component. 2)Dynamic data load in script task without a data flow task.I am on tests on this component and as soon as I complete this I can share the package , scripts and test files. 3)Also there are pending tasks in the posts which I am in continuous process to complete them. I have deleted 1, 2 blogs above and will come up with renovated paragraphs and screenshots as they would really help a long way for developers. Thanks all.

Source and Destination Assistants in Denali!

noreply@blogger.com (Deepak Kumar Kalyanum) — Sat, 20 Aug 2011 06:54:00 +0000

The data flow task in Denali now has Source and Destination assitants which makes our life easier now.

Here goes :

Check the new Assistants in Data Flow Task.Now,whats the difference made, it directly takes you to create a connection without actually opening the source/destination and clicking on New connection. Great!. Here are few snapshots:

1) Open the SSIS toolbox pane in the data flow task:

2)This is how the source assistant looks like :

3) This is how destination assistant looks like :