Monday, 13 August 2012

SQL SERVER 2012 OVER VIEW


SQL Server 2012, due out sometime in the first half of 2012, promises to be an exciting release on several fronts. I'll provide an overview of SQL Server 2012's new features and packaging options. I'll also briefly tell you about the system requirements, upgrade process, and some changes in the setup program. 

Relational Database Engine Features
SQL Server 2012's relational database engine includes numerous availability, performance, security, manageability, and T-SQL improvements and features. I'll review the highlights in each area.
Availability. SQL Server 2012 includes the new AlwaysOn capabilities for high availability and disaster recovery. AlwaysOn lets you include up to four mirrors of your data, grouped into what are called AlwaysOn Availability Groups. You can choose the speed of mirroring, much like you can do in SQL Server 2008 R2. What's new is that the solution is built on top of Windows failover clustering, so the server uses virtual network names and virtual IP addresses. This allows clients to connect to the virtual names and IP addresses rather than depending upon coding the available partner servers in the connection string. This approach dramatically improves failover times to the secondary servers.
You can use the secondary servers for backup, reporting, or other read-only workloads, lightening your primary server's workload. This means your secondary servers can add business value and improve availability.
SQL Server 2012 also supports multi-subnet clustering, making geographically dispersed failover clusters across data centers (a much-anticipated feature among enterprise customers) a reality. This improvement, combined with the capability to use local disks for tempdb, offers some nice benefits for those organizations that use failover clustering.
The initial configurations for AlwaysOn must be made in the SQL Server Configuration Manager utility. If you try to use SQL Server Management Studio (SSMS) first, you'll receive an informative error message telling you exactly how to enable AlwaysOn. After you've enabled (and restarted) the SQL Server service, you can use the SSMS tools, such as the New Availability Group Wizard. As you step through the wizard, it's smart enough to tell you not only when a database can't work in an availability group but also why (e.g., you don't yet have a full database backup, you're not in the correct recovery mode). In a single interface, you configure all the available secondary replicas, as Figure 1 shows. Once configured, the AlwaysOn High Availability tree in Object Explorer is populated with the respective replicas, databases, and listeners, as Figure 2 shows
 
Figure 1: Specifying the secondary replicas in the New Availability Group Wizard


Figure 2: Using Object Explorer to see the Always On replicas, databases, and listeners 
Windows Server Core installations will be officially supported, which means you can run your SQL Server instances on servers without the Windows graphical interface. This capability should reduce the number of required Windows updates and reboots and give you a little more of your CPU and memory back to focus on being a server. However, note that only the 64-bit version of Windows Server 2008 R2 SP1 is supported in a Server Core installation. In addition, some SQL Server 2012 features -- such as Master Data Services, Data Quality Services, and the graphical interface -- aren't supported in a Server Core installation.
Performance. A new ColumnStore index, improved partitioning, and faster full-text searches are the most important performance enhancements in SQL Server 2012. If you have read-only data, you can create ColumnStore indexes on your frequently queried columns to dramatically improve query processing time. (The ColumnStore index capabilities work only on read-only data.) Partitioning has been extended to support 15,000 partitions in a single table. With the amount of data that organizations store growing, this expansion should help with the granularity of storage and usage of historical data. Although the Full-Text Search capabilities in SQL Server 2012 are very similar to those in SQL Server 2008 R2, there are significant improvements in performance and scalability.
Security. User-defined server roles have finally made it into SQL Server 2012. You can now define roles for a server and assign server-level permissions in unique configurations. These roles let you give DBAs and developers the permissions they need, without having to make them sysadmin server role members.
Auditing has been enhanced. There's a more robust auditing mechanism, and you have more control over how much disk space is used. Newer, stronger encryption algorithms are supported. Using the new concept of contained databases, support has also been added for bypassing logins altogether.
Manageability. SQL Server 2012's new Distributed Replay feature will be incredibly useful for testing, upgrades, and tuning. Like SQL Server Profiler, Distributed Replay lets you "replay" captured workloads. Unlike Profiler, Distributed Replay lets you replay captured workloads across multiple systems, simulating a more realistic set of workloads against SQL Server.
SSMS has a number of improvements, including a redesigned Restore Database dialog box. The SQL Server 2012 team enhanced not only its core capabilities but also its graphical interface. Improvements to the core capabilities include a new page-restore feature and improved logic. For example, SSMS does a much better job figuring out the correct order in which to do a restore from your full, differential, and log backups. The Restore Database dialog box now includes friendly condition notifications when you launch the dialog box and a really useful visual timeline that you use when restoring a database from multiple backups.

Support for Windows PowerShell has been extended in SQL Server 2012. The sqlps utility now snaps into the PowerShell 2.0 environment rather than being its own PowerShell 1.0 mini-shell. This change will be a big win for integrating Windows and SQL Server management actions.
T-SQL. The new THROW statement in SQL Server 2012 will be much more helpful than the RAISERROR statement. With the THROW statement, you can build dynamic, meaningful error messages for your code and pass them as proper errors, improving error-handling in T-SQL code.
The new PARSE, TRY_CONVERT, and TRY_PARSE functions for numeric, date, and time conversions look promising, particularly for international or multi-language programmers. I'm glad to see them because working with dates and times can be complicated.
The OVER clause has been enhanced to support Windows functions. If you're working with a set of rows and want to apply functions to those sets, this capability is for you. Itzik Ben-Gan has been lobbying for this capability for a number of years. For example, see his blog "Window Functions (OVER Clause) -- Help Make a Difference." 
Finally, a number of analytic functions have been added to T-SQL. These functions enable more data-warehousing functionality to run natively within SQL Server.

Window Functions (OVER Clause)—Help Make a Difference


If I had to name one concept in standard SQL that I thought was the most important one, and that is worth Microsoft’s investment for future versions of SQL Server, I’d say window functions, hands down, without a doubt. Window functions are a subset of what the standard calls set functions, meaning, functions that are applied to a set of rows. The term window is used to describe the set of rows that the function operates on, and the language provides a clause called OVER where you provide the window specification. So what’s the big deal, and what makes window functions more important than other features that are missing in SQL Server? There are so many reasons… But first I’ll give a bit more background about window functions, and then I’ll get to the reasons and demonstrate use cases…
First, to clarify, SQL Server 2005 already introduced some support for window functions—the ranking calculations: ROW_NUMBER, RANK, DENSE_RANK and NTILE, and partial support for window aggregate functions with only the partitioning part implemented. SQL Server 2005 was a great release for developers with so many cool and practical T-SQL features. The number of solutions that I simplified and optimized just with the ROW_NUMBER function and CTEs is amazing. Still, there are many standard features related to window functions that SQL Server didn’t yet implement (as of SQL Server 2008 R2) and that can help address quite a wide variety of business problems with simpler and more efficient solutions.
These days the next major release of Microsoft SQL Server—version 11—is being developed. These are pivotal days for candidate features where decisions are made whether they will or will not make it to the final release. And even though I think that more complete support for window functions is so important to developers and to the success of SQL Server, I’m not sure at all that we will see those in the product. This is time for us as part of the SQL Server community to express our strong opinion. Hopefully Microsoft will realize how important it is for us to have those features in the product, as well as to show that the SQL Server community’s opinion matters.
In this article I will explain some of the key features that are missing in SQL Server and why it’s important to add support for such features. If you share my opinion, and haven’t done so already, you can cast your vote in the following feature request items:
Ordering for aggregates (used to allow subsequent framing options):
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254387
PERCENT_RANK and CUME_DIST Distribution Functions
https://connect.microsoft.com/SQLServer/feedback/details/600484
Reuse of window definition using the WINDOW clause:
https://connect.microsoft.com/SQLServer/feedback/details/600499
Like with any thing in life that you’re not aware of, you don’t know how it can help you if you don’t know that it exists. My feeling is that many developers are not really aware of the capabilities of the standard window functions and therefore Microsoft doesn’t see a lot of demand for it. Education and raising the topic to people’s awareness is therefore key to the realization of the benefits, and as a consequence, encourage people to ask Microsoft for more support.
The unfortunate part is that all of SQL Server’s leading competitors; including Oracle, DB2 and Teradata for some time now already have a far more complete support for window functions. So even though my focus and passion is for SQL Server, I sometimes find myself in the awkward situation of demoing standard SQL window functions on Oracle when teaching or presenting.
So what’s missing…
The most important missing features are probably ordering and framing options for window aggregate functions. Other key features that are still missing are distribution and offset functions, and reusability of window definitions. More details shortly.
Why are window functions so powerful?
SQL is often referred to as a set-based language. The reason is that the language is based on the relational model, which in turn is based, in part, on mathematical set theory. When writing SQL queries you’re supposed to deal with a table (or relation, which is a set) as a whole, as opposed to the table’s individual rows. Also, since sets have no order, you’re not supposed to make any assumptions in regards to the physical ordering of the data.
The reality is that for many developers set-based thinking is far from being intuitive, and it can take a few good years to truly think in SQL terms. This is why often developers tend to use cursors—because using those feel like an extension to what they already know. Cursors allow you to deal with one row at a time, and also rely on specified order of the data.
Window functions have an ingenious design. They do operate on sets, or windows, while allowing you to indicate ordering as part of the calculation where relevant. Not to confuse with cursors, window functions allow defining ordering for the calculation without making any expectations in regards to ordering of the input data given to the query or the output coming out of the query. In other words, no relational concepts are violated. Ordering is only part of the specification of the calculation. Similarly, other common elements in querying problems, like partitioning, framing of applicable rows, are all intuitive parts of the window specification. So in a sense, I see window functions as bridging the big gap that exists between cursor/iterative and set-based thinking.
Now, that’s a lot of words before showing even one example. So let’s look at a few more concrete examples of some of the missing features…
Sample Data
Most of the examples I’ll show are against a database called InsideTSQL2008. You can find the script creating it here: http://www.InsideTSQL.com/books/source_code/InsideTSQL2008.zip. In addition, the following view will be used in some of the examples:
SET NOCOUNT ON;
USE InsideTSQL2008;
GO

IF OBJECT_ID('Sales.EmpOrders', 'V') IS NOT NULL
  DROP VIEW Sales.EmpOrders;
GO

CREATE VIEW Sales.EmpOrders 
  WITH SCHEMABINDING 
AS 

SELECT 
  O.empid,
  DATEADD(month, DATEDIFF(month, 0, O.orderdate), 0) AS ordermonth, 
  SUM(OD.qty) AS qty,
  CAST(SUM(OD.qty * OD.unitprice * (1 - discount)) 
       AS NUMERIC(12, 2)) AS val,
  COUNT(*) AS numorders 
FROM Sales.Orders AS O 
  JOIN Sales.OrderDetails AS OD 
    ON OD.orderid = O.orderid 
GROUP BY empid, DATEADD(month, DATEDIFF(month, 0, O.orderdate), 0);
GO
Ordering and Framing for Window Aggregate Functions
As mentioned, currently window aggregate functions support only a partitioning element. What’s missing are ordering and framing options. The standard supports an ORDER BY clause to define ordering in the window and ROWS and RANGE clauses that frame the window based on the defined ordering. A classic example that would benefit from ordering and framing is running totals. Consider the following Accounts table definition:
CREATE TABLE dbo.Accounts
(
  actid  INT   NOT NULL,                -- partitioning column
  tranid INT   NOT NULL,                -- ordering column
  val    MONEY NOT NULL                 -- measure
  CONSTRAINT PK_Accounts PRIMARY KEY(actid, tranid)
);
The table represents deposit (positive value) and withdrawal (negative value) transactions in bank accounts. You need to calculate at each point what the account balance was. Like with many querying problems there’s a partitioning element (actid), ordering element (tranid), and a measure that the calculation applies to (val). Window aggregate functions in standard SQL support all three elements. Here’s how you would express the query calculating the balance at each point for each account:
SELECT actid, tranid, val,
  SUM(val) OVER(PARTITION BY actid
                ORDER BY tranid
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW) AS balance
FROM dbo.Accounts;
You can achieve such calculations today in SQL Server using a subquery or a join:
-- Set-Based Solution Using Subqueries
SELECT actid, tranid, val,
  (SELECT SUM(S2.val)
   FROM dbo.Accounts AS S2
   WHERE S2.actid = S1.actid
     AND S2.tranid <= S1.tranid) AS balance
FROM dbo.Accounts AS S1;
-- Set-Based Solution Using Joins
SELECT S1.actid, S1.tranid, S1.val,
  SUM(S2.val) AS balance
FROM dbo.Accounts AS S1
  JOIN dbo.Accounts AS S2
    ON S2.actid = S1.actid
   AND S2.tranid <= S1.tranid
GROUP BY S1.actid, S1.tranid, S1.val;
But besides the fact that these solutions are not as straightforward and intuitive as the one using a window function, there’s a big problem with the way SQL Server currently optimizes the subquery and join solutions. Assuming you defined a covering index on the partitioning column, followed by the ordering column, and including the aggregated measure, for each row SQL Server will scan all rows with the same partitioning value and an ordering value that is less than or equal to the current. Given p partitions with r rows in average, and fairly even distribution of rows in partitions, the total number of rows processed in such a plan is pr + p(r + r^2)/2. This means that in respect to the partition size, the algorithmic complexity, or scaling, of the solution s quadratic (N^2). That’s bad. The window function form lends itself to good optimization, especially with the fast track case like the above (rows between unbounded preceding and current row). It should be straightforward to the optimizer to optimize this query with one ordered scan of the index, translating to simply pr rows being scanned.
Another example for running totals is querying a table called EmpOrders with a row for each employee and month, and calculating the cumulative performance for each employee and month; in other words, the total value for the employee from the beginning of his/her activity until the current month. Here’s how you would express it with a window aggregate:
SELECT empid, ordermonth, qty,
  SUM(qty) OVER(PARTITION BY empid
                ORDER BY ordermonth
                ROWS BETWEEN UNBOUNDED PRECEDING
                         AND CURRENT ROW) AS run_qty
FROM Sales.EmpOrders;

empid       ordermonth              qty         run_qty
----------- ----------------------- ----------- -----------
1           2006-07-01 00:00:00.000 121         121
1           2006-08-01 00:00:00.000 247         368
1           2006-09-01 00:00:00.000 255         623
1           2006-10-01 00:00:00.000 143         766
1           2006-11-01 00:00:00.000 318         1084
...
2           2006-07-01 00:00:00.000 50          50
2           2006-08-01 00:00:00.000 94          144
2           2006-09-01 00:00:00.000 137         281
2           2006-10-01 00:00:00.000 248         529
2           2006-11-01 00:00:00.000 237         766
...
There are many business examples where ordering and framing options can be useful besides calculating account balances. Those include inventory, running totals for reporting, moving averages, and so on. Here’s an example for a query calculating the average of the last three recorded periods:
SELECT empid, ordermonth,
  AVG(qty) OVER(PARTITION BY empid
                ORDER BY ordermonth
                ROWS BETWEEN 2 PRECEDING
                         AND CURRENT ROW) AS avg_last_three
FROM Sales.EmpOrders;
There are also various temporal querying problems where running totals serve part of the solution.
For simplicity I showed examples where framing is based on the ROWS clause where you indicate an offset in terms of number of rows. The standard also supports a RANGE clause that allows indicating an offset in terms of values, such as time intervals, as in the following example returning the average of the last three months:
SELECT empid, ordermonth, qty,
  SUM(qty) OVER(PARTITION BY empid
                ORDER BY ordermonth
                RANGE INTERVAL '2' MONTH PRECEDING) AS sum_3m_qty
FROM Sales.EmpOrders
ORDER BY empid, ordermonth;
Offset Functions
The SQL standard defines several offset functions that would make developers’ life so much easier compared to the tools available today for similar needs. Among the missing offset functions are LAG and LEAD, returning a value from a row in a given offset from the current row based on specified ordering. For example, the following query will return, for each current order, also the order date of the previous and next orders:
SELECT custid, orderdate, orderid,
  LAG(orderdate)  OVER(PARTITION BY custid
                       ORDER BY orderdate, orderid) AS prvod,
  LEAD(orderdate) OVER(PARTITION BY custid
                       ORDER BY orderdate, orderid) AS nxtod
FROM Sales.Orders;
custid  orderdate   orderid  prvod       nxtod
------- ----------- -------- ----------- -----------
1       2007-08-25  10643    NULL        2007-10-03
1       2007-10-03  10692    2007-08-25  2007-10-13
1       2007-10-13  10702    2007-10-03  2008-01-15
1       2008-01-15  10835    2007-10-13  2008-03-16
1       2008-03-16  10952    2008-01-15  2008-04-09
1       2008-04-09  11011    2008-03-16  NULL
2       2006-09-18  10308    NULL        2007-08-08
2       2007-08-08  10625    2006-09-18  2007-11-28
2       2007-11-28  10759    2007-08-08  2008-03-04
2       2008-03-04  10926    2007-11-28  NULL
...
Notice how elegant and intuitive this form is. The default offset is one row, but you can also be explicit if you need an offset that is other than one row, e.g., three rows:
SELECT custid, orderdate, orderid,
  LAG(orderdate, 3) OVER(PARTITION BY custid
                         ORDER BY orderdate, orderid) AS prv3od
FROM Sales.Orders;
There are lots of business examples for the usefulness of these functions, like recency calculations, trend analysis, and others. Here’s an example for a query addressing recency calculations, returning the difference in terms of days between the current and previous orders:
SELECT custid, orderdate, orderid,
  DATEDIFF(day,
    LAG(orderdate) OVER(PARTITION BY custid
                        ORDER BY orderdate, orderid),
    orderdate) AS diff
FROM Sales.Orders;
Other missing offset functions are FIRST_VALUE, LAST_VALUE, returning the value from the first or last rows in the partition based on specified ordering. Here’s an example returning the value of the first and last orders per customer with each order:
-- FIRST_VALUE, LAST_VALUE
SELECT custid, orderdate, orderid, val,
  FIRST_VALUE(val) OVER(PARTITION BY custid
                        ORDER BY orderdate, orderid) AS val_firstorder,
  LAST_VALUE(val)  OVER(PARTITION BY custid
                        ORDER BY orderdate, ordered
                        ROWS BETWEEN UNBOUNDED PRECEDING
                                 AND UNBOUNDED FOLLOWING) AS val_lastorder
FROM Sales.OrderValues;
custid  orderdate   orderid  val     val_firstorder  val_lastorder
------- ----------- -------- ------- --------------- --------------
1       2007-08-25  10643    814.50  814.50          933.50
1       2007-10-03  10692    878.00  814.50          933.50
1       2007-10-13  10702    330.00  814.50          933.50
1       2008-01-15  10835    845.80  814.50          933.50
1       2008-03-16  10952    471.20  814.50          933.50
1       2008-04-09  11011    933.50  814.50          933.50
2       2006-09-18  10308    88.80   88.80           514.40
...
And here’s an example calculating the difference between the current order value and the first and last:
SELECT custid, orderdate, orderid, val,
  val - FIRST_VALUE(val) OVER(PARTITION BY custid
                              ORDER BY orderdate, orderid) AS diff_first,
  val - LAST_VALUE(val)  OVER(PARTITION BY custid
                              ORDER BY orderdate, ordered
                              ROWS BETWEEN UNBOUNDED PRECEDING
                                       AND UNBOUNDED FOLLOWING) AS diff_last
FROM Sales.OrderValues;
Distribution Functions
Standard SQL supports window distribution functions that performing statistical calculations. Specifically it supports the PERCENT_RANK and CUM_DIST functions, calculating a percentile rank and cumulative distribution. These functions give you a relative rank of a row in respect to other rows in the window partition, expressed as ratio/percent. The specific formulas used by the two variants are:
PERCENT_RANK: (RK-1)/(NR-1), where RK = rank, NR = number of rows in partition
CUME_DIST: NP/NR, where NP = number of rows preceding or peer with current row (same as next rank - 1)
Here’s an example using these functions:
SELECT custid, COUNT(*) AS numorders,
  PERCENT_RANK() OVER(ORDER BY COUNT(*)) AS percentrank,
  CUME_DIST() OVER(ORDER BY COUNT(*)) AS cumedist
FROM Sales.Orders
GROUP BY custid;
custid  numorders  percentrank  cumedist
------- ---------- ------------ ---------
13      1          0.0000       0.0112
33      2          0.0114       0.0337
43      2          0.0114       0.0337
42      3          0.0341       0.1124
53      3          0.0341       0.1124
...
37      19         0.9545       0.9663
24      19         0.9545       0.9663
63      28         0.9773       0.9775
20      30         0.9886       0.9888
71      31         1.0000       1.0000
Reuse of Window Definition using WINDOW Clause
Suppose you need to write several window functions that rely on the same window definition (or part of it). You will end up with a lot of repetition of code. Standard SQL has a clause called WINDOW that allows naming a window definition or part of it, making it reusable. For example, instead of:
SELECT empid, ordermonth, qty,
  SUM(qty) OVER ( PARTITION BY empid
                   ORDER BY ordermonth
                   ROWS BETWEEN UNBOUNDED PRECEDING
                           AND CURRENT ROW ) AS run_sum_qty,
  AVG(qty) OVER ( PARTITION BY empid
                   ORDER BY ordermonth
                   ROWS BETWEEN UNBOUNDED PRECEDING
                           AND CURRENT ROW ) AS run_avg_qty,
FROM Sales.EmpOrders;
You would use:
SELECT empid, ordermonth, qty,
  SUM(qty) OVER W1 AS run_sum_qty,
  AVG(qty) OVER W1 AS run_avg_qty,
FROM Sales.EmpOrders
WINDOW W1 AS ( PARTITION BY empid
               ORDER BY ordermonth
               ROWS BETWEEN UNBOUNDED PRECEDING
                          AND CURRENT ROW );
As you can see, with the WINDOW clause the code is shorter, more readable, and easier to maintain.
Conclusion
I showed just part of the standard support for window functions that SQL Server is still missing. There’s more, like window frame exclusion. There are also other set functions not implemented, like ordered set functions, and so on. But here I wanted to make a point in hope that Microsoft will realize how important it is to add such support in SQL Server 11. If you feel so as well, help make a difference by voting for the items, write about the topic, talk about it, increasing people’s awareness. Hopefully this request will find open ears. As a reminder, here are the open items for some of the requests for enhancements:
Ordering for aggregates (used to allow subsequent framing options):
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254387
PERCENT_RANK and CUME_DIST Distribution Functions
https://connect.microsoft.com/SQLServer/feedback/details/600484
Reuse of window definition using the WINDOW clause:
https://connect.microsoft.com/SQLServer/feedback/details/600499


SSAS Features
The unified Business Intelligence Semantic Model (BISM) affects a number of components in the business intelligence (BI) space, including SQL Server Analysis Services (SSAS). There are changes across many SSAS features to support BISM. For example, you'll find changes in SSAS's Analysis Management Objects library and XML for Analysis protocol.
You'll find a number of new features in SSAS, including:
  • A new Tabular mode for installing the SSAS server; you use the VertiPaq engine, which was previously used in PowerPivot only
  • New statistical, table, and search functions
  • New row-level security functions and capabilities
SSAS's manageability has been improved across the board as well. For example, SSAS and PowerPivot now offer PowerShell cmdlets. And SQL Server Data Tools has an updated look and feel (which I'll discuss shortly).
SSRS Features
SQL Server Reporting Services (SSRS) now includes a new data-driven alerting capability known as Data Alerts. With Data Alerts, you can have emails sent to specific people based on changes in reports or based on a specific set of alert conditions.
Rendering SSRS reports has been improved, with a number of earlier limitations removed. You can now render a report as a Microsoft Excel document (Excel 2007 and later) or a Microsoft Word document (Word 2007 and later).
SSRS's integration into SharePoint has also been improved. Administrators will appreciate that they can now completely configure SSRS's integration into SharePoint with SharePoint utilities or with PowerShell. SharePoint users will appreciate Power View, the new presentation and visualization interface for SSRS. Power View, which is an addition to rather than a replacement for Report Builder, uses a new report file format (.rdlx).
SSIS Features
The updated dependency viewer can enhance your ability to perform SQL Server Integration Services (SSIS) package analysis and debugging. The dependency viewer helps you understand objects' dependencies, right from within SSMS. The viewer offers a number of troubleshooting views, stored procedures, and functions to help you troubleshoot package problems. The UI has been upgraded with several usability improvements.
When you open an existing SSIS package, the SSIS Package Upgrade Wizard launches to help convert your package to SQL Server 2012. The wizard points you to the Upgrade Advisor for a look at potential upgrade issues. (I'll talk more about the Upgrade Advisor later.)
After your package is converted, you'll see the new design environment with the updated look and feel, which Figure 3 shows. I also found this environment more intuitive than Business Intelligence Development Studio (BIDS) 2008 R2.

Figure 3: Presenting the new design environment for SSIS packages 
Master Data Services Features
Master Data Services has a number of improvements as well, including improvements in the Excel add-on for Master Data Services and the Multiple Data Management web application interface. Security has been simplified, and the web UI works better with SharePoint.
Development Tools
With its incorporation of the new SQL Server Data Tools (formerly code-named Juneau) to create relational database projects in SQL Server 2012, SQL Server BIDS has been renamed SQL Server Data Tools. When you open Visual Studio to create a new project, you'll see the same Business Intelligence project templates, which you use to create SSAS, SSIS, and SSRS projects. If you navigate to Database and expand it, you'll see SQL Server as a category, as Figure 4 shows. Highlight SQL Server, and you can see options to create and work on SQL Server 2005 and SQL Server 2008 projects, SQL Server data-tier applications, and other types of database projects.
Figure 4: Using the SQL Server 2012 Business Intelligence project templates 
Developing database tools will be much easier with these database project options. And if you're using the Visual Studio Team Data toolset, you'll be pleased to see even better functionality and capabilities now included with SQL Server.
Data Quality Services
Data Quality Services is a new component available in the Enterprise Edition and the new Business Intelligence Edition of SQL Server 2012. Data Quality Services integrates with SSIS and Master Data Services to improve data correctness, addressing such issues as making corrections to data based on recognized patterns, removing duplicate data, and performing other data-cleansing functions. To learn more about Data Quality Services, which consists of both server and client components, see SQL Server 2012 Books Online (BOL).

Packaging Options
There are quite a few changes to the product editions. The Datacenter Edition, which was introduced in SQL Server 2008 R2, has been rolled back into the Enterprise Edition. The Workgroup Edition and the Small Business Edition (available only to OEMs) are no longer available. The Itanium processor is no longer supported, so the IA64 port is no longer provided. And the licensing model has significant changes.
As I mentioned previously, the Business Intelligence Edition is new to SQL Server 2012. This edition would be a good choice if you want additional functionality in SSAS and SSRS and additional capabilities such as Data Quality Services, Master Data Services, Tabular mode, and PowerPivot for SharePoint, but you don't need more processor core support.
SQL Server Express LocalDB, which replaces SQL Server Express (now deprecated), will make many application developers happy. It's a lightweight installation of just the database engine. It doesn't require you to manage services and service accounts, which makes a lot of sense. SQL Server Express LocalDB is fully compatible with SQL Server yet is easy for application developers to install and use.
The SQL Server Enterprise, Standard, Evaluation, Developer, and Compact Editions are still available, as before. The Web Edition will be offered only to web hosters with a special license agreement.
System Requirements and the Upgrade Process
If you're interested in SQL Server 2012, you're probably wondering what's involved in the upgrade process. First, you need to make sure you're in compliance with the OSs and SQL Server versions supported for a direct upgrade:
  • You must be running Windows Server 2008 R2 SP1, Windows Server 2008 SP2, Windows 7 SP1, or Windows Vista SP2.
  • You must be running SQL Server 2008 R2 SP1 (or a later service pack, as applicable, for each edition), SQL Server 2008 SP2, or SQL Server 2005 SP4.
If you're running an older version of SQL Server or the Windows OS, you must first upgrade to a supported version before you can upgrade to SQL Server 2012.
SQL Server 2012 also requires that PowerShell 2.0 and both Microsoft .NET Framework 3.5 SP1 and Microsoft .NET Framework 4.0 are installed. Unlike SQL Server 2008 R2, SQL Server 2012 won't necessarily install the required software for you.
Hyper-V is fully supported in Windows Server 2008 SP2 and later. Microsoft Internet Explorer (IE) 7.0 or later is required for the SQL Server Data Tools, SSMS, and the report designer component of SSRS. For the graphical tools, your display must support 1024 ´ 768 screen resolution. Although there isn't an Itanium version of SQL Server 2012, 32-bit and 64-bit versions of SQL Server 2012 are available.
To make sure your environment is ready for an upgrade, you can run the SQL Server Upgrade Advisor. You can install the Upgrade Advisor from the home page of the SQL Server setup program. Once installed, you can run it by selecting Launch the Upgrade Advisor Analysis Wizard on the Start menu.
As Figure 5 shows, Upgrade Advisor consists of a wizard and a report viewer. The wizard is reasonably logical to follow. Make sure that all your SQL Server services are started before you run the wizard. After the analysis is complete, you'll get a report with any problems found, along with suggestions for how to address those problems. Note that the Upgrade Advisor also can analyze captured profiler traces to do an even better job assessing your existing environment.

Figure 5: Running Upgrade Advisor 
For the server components, the upgrade will be relatively straightforward. However, I recommend that you first read about the upgrade process on SQL Server 2012 BOL and look at the readme notes for any special instructions. For some of the client-side components, such as PowerPivot for Excel, you'll need to uninstall the SQL Server 2008 (or other) version before you install the SQL Server 2012 version. The usual caution applies if you're using replication: You need to plan carefully and observe the restrictions, as documented in SQL Server 2012 BOL.
Setup Program Changes
The SQL Server 2012 setup experience involves some changes because of new capabilities that have been added, particularly to the BI components. However, if you're using SQL Server 2008 R2 or SQL Server 2008, you'll be right at home because their setup experiences are similar. I did find it a bit disconcerting, though, that the setup program repeatedly closed windows and then opened new windows. So be patient, and don't assume that the setup program isn't still running because the setup window has temporarily disappeared.
The setup program automatically attempts to check for updates before it launches, and I'm happy to say it's smart enough not to fail if there's no Internet connection. When you get to the Feature Selection page shown in Figure 6, you can select the capabilities and features you want to install. A nice enhancement is that the setup program automatically selects default service accounts for all the services. So, for many installations, the setup program auto-configures the correct defaults.

Figure 6: Selecting and installing the SQL Server 2012 features 
One thing to watch out for is the changes to service accounts. If you're using Windows Server 2008 R2 or Windows 7, there are two new options: managed service account and virtual accounts. If you intend to use failover clustering or AlwaysOn Availability Groups, you shouldn't use the virtual accounts. You can read more about these new service account options in the Microsoft article "Configure Windows Service Accounts and Permissions."
Take the Plunge
There's a lot to evaluate in SQL Server 2012, and I hope you take the plunge. Download the product and give it a try!

Top 10: New Features in SQL Server 2012

The release of Microsoft SQL Server 2012 brings a whole host of important changes to Microsoft's enterprise data platform, including changes in the editions that Microsoft will offer as well as a new licensing model. In addition, SQL Server 2012 introduces many performance, business intelligence (BI), and development enhancements. Here's a rundown of the top 10 most important new features in SQL Server 2012.
1. Simplified editions -- SQL Server 2012 will be delivered in three main editions: Enterprise, Business Intelligence, and Standard. The Enterprise edition contains all of the product's features. The Business Intelligence edition contains all of the BI capabilities but lacks some of the higher-end availability features. The Standard edition provides basic relational, BI, and availability capabilities. Microsoft has retired the Datacenter, Workgroup, and Standard for Small Business editions but will continue to provide the Developer, Express, and Compact Editions. The Web edition will be offered only to web hosting providers who have a Services Provider License Agreement (SLPA).
2. Processor core licensing model -- With SQL Server 2012, Microsoft has moved away from counting sockets to a new core-based licensing model. The Enterprise edition can be licensed only per core. The list price is $6,874 per core. The Business Intelligence edition is licensed only per server; it goes for $8,592 per server. You must also purchase Client Access Licenses (CALs) per user. The CAL price has increased to $209 per CAL. The Standard edition has the option of being licensed either per core or per server; it costs $1,793 per core or $898 per server.
3. Support for Windows Server Core -- Windows Server Core is designed for infrastructure applications such as SQL Server that provide back-end services but don't need a GUI on the same server. The inability to run previous versions of SQL Server on Windows Server Core always seemed ironic. SQL Server 2012's support for Windows Server Core enables leaner and more efficient SQL Server installations and also reduces potential attack vectors and the need for patching.
4. Data Quality Services -- Data Quality Services (DQS) is a knowledge-based tool that helps ensure your databases contain high-quality, correct data. DQS performs data cleansing, which can modify or remove incorrect data. It also does data matching to identify duplicate data and profiling that intelligently analyzes data from different sources. DQS is integrated with both SQL Server Integration Services and Master Data Services.
5. T-SQL enhancements -- SQL Server 2012 provides many T-SQL enhancements, including support for sequences, a new TRY_CONVERT operator for data conversions, OFFSET and FETCH for data paging, a new FORMAT() function for easier data formatting, a new THROW operator for enhanced error handling, and improved T-SQL windowing functions.
6. Contained databases -- Contained databases make it easy to deploy new databases and to move databases between different SQL Server instances. Users of a contained database don't need logins for the SQL Server instance. Instead, all authentications are stored in the contained database. Contained databases have no configuration dependencies on the instance of SQL Server that they're hosted on.
7. Columnar index -- Primarily intended to support data warehousing, the columnar index feature incorporates the same high-performance/high-compression technology that Microsoft uses in PowerPivot into the SQL Server relational database engine. Columnar indexes store data column-wise, and only the necessary columns are returned as query results. Depending on the data, Microsoft states this technology can provide up to 10 times improvement in query performance with reduced I/O.
8. SQL Server Data Tools -- One of the most important developer-oriented features in SQL Server 2012 is the new SQL Server Data Tools development environment (formerly coded-named Juneau). SQL Server Data Tools uses the Visual Studio 2010 shell, and it enables model-driven database development as well as T-SQL and SQLCLR development and debugging. SQL Server Data Tools can connect to SQL Server 2005 and later as well as to SQL Azure.
9. Power View -- Power View (formerly code-named Crescent) is a graphical data navigation and visualization tool that enables end-user reporting. Power View provides a report designer that lets users take elements from a semantic data model that's constructed by IT and use them to create powerful interactive reports that can be embedded in .NET applications or published to SharePoint.
10. AlwaysOn Availability Groups -- The most important feature in the SQL Server 2012 release is the new AlwaysOn Availability Groups high availability technology. AlwaysOn Availability Groups is essentially the evolution of database mirroring. AlwaysOn can protect multiple databases. It supports up to four replicas and lets you mix and match both synchronous and asynchronous connections. In addition, unlike database mirroring, the data in the replicas can be actively queried.

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)