SQL 2012


SQL Server 2012 supports upgrade from the following versions of SQL Server:

* SQL Server 2005 SP4 or later

* SQL Server 2008 SP2 or later

* SQL Server 2008 R2 SP1 or later
The supported upgrade scenarios from earlier versions of SQL Server to SQL Server 2012.

Upgrade from Supported upgrade path
SQL Server 2005 SP4 Enterprise SQL Server 2012 EnterpriseSQL Server 2012 Business Intelligence
SQL Server 2005 SP4 Developer SQL Server 2012 Developer
SQL Server 2005 SP4 Standard SQL Server 2012 EnterpriseSQL Server 2012 Business IntelligenceSQL Server 2012 Standard
SQL Server 2005 SP4 Workgroup SQL Server 2012 EnterpriseSQL Server 2012 Business IntelligenceSQL Server 2012 Standard

SQL Server 2012 Web

SQL Server 2005 SP4 Express,SQL Server 2005 SP4 Express with Tools, andSQL Server 2005 SP4 Express with Advanced Services SQL Server 2012 EnterpriseSQL Server 2012 Business IntelligenceSQL Server 2012 Standard

SQL Server 2012 Web

SQL Server 2012 Express

SQL Server 2008 SP2 Enterprise SQL Server 2012 EnterpriseSQL Server 2012 Business Intelligence
SQL Server 2008 SP2 Developer SQL Server 2012 Developer
SQL Server 2008 SP2 Standard SQL Server 2012 EnterpriseSQL Server 2012 Business IntelligenceSQL Server 2012 Standard
SQL Server 2008 SP2 Web SQL Server 2012 EnterpriseSQL Server 2012 Business IntelligenceSQL Server 2012 Standard

SQL Server 2012 Web

SQL Server 2008 SP2 Workgroup SQL Server 2012 EnterpriseSQL Server 2012 Business IntelligenceSQL Server 2012 Standard

SQL Server 2012 Web

SQL Server 2008 SP2 Express,SQL Server 2008 SP2 Express with Tools, andSQL Server 2008 SP2 Express with Advanced Services SQL Server 2012 EnterpriseSQL Server 2012 Business IntelligenceSQL Server 2012 Standard

SQL Server 2012 Web

SQL Server 2012 Express

SQL Server 2008 R2 SP1 Datacenter SQL Server 2012 EnterpriseSQL Server 2012 Business Intelligence
SQL Server 2008 R2 SP1 Enterprise SQL Server 2012 EnterpriseSQL Server 2012 Business Intelligence
SQL Server 2008 R2 SP1 Developer SQL Server 2012 Developer
SQL Server 2008 R2 SP1 Standard SQL Server 2012 EnterpriseSQL Server 2012 Business IntelligenceSQL Server 2012 Standard
SQL Server 2008 R2 SP1 Web SQL Server 2012 EnterpriseSQL Server 2012 Business IntelligenceSQL Server 2012 Standard

SQL Server 2012 Web

SQL Server 2008 R2 SP1 Workgroup SQL Server 2012 EnterpriseSQL Server 2012 Business IntelligenceSQL Server 2012 Standard

SQL Server 2012 Web

SQL Server 2008 R2 SP1 Express,SQL Server 2008 R2 SP1 Express with Tools, andSQL Server 2008 R2 SP1 Express with Advanced Services SQL Server 2012 EnterpriseSQL Server 2012 Business IntelligenceSQL Server 2012 Standard

SQL Server 2012 Web

SQL Server 2012 Express

MS SQL Server 2012 HotfiX’s use syntax i.e. Select @@version

Result:

Release Product Version
SQL Server 2012 Service Pack 2 11.0.5058.0

 

Build File version KB / Description Release Date
11.00.5582 2011.110.5582.0 3037255 Cumulative update package 5 (CU5) for SQL Server 2012 Service Pack 2 March 16, 2015
11.00.5569 2011.110.5569.0 3007556 Cumulative update package 4 (CU4) for SQL Server 2012 Service Pack 2 January 20, 2015
11.00.5556 2011.110.5556.0 3002049 Cumulative update package 3 (CU3) for SQL Server 2012 Service Pack 2 November 17, 2014
11.00.5548 2011.110.5548.0 2983175 Cumulative update package 2 (CU2) for SQL Server 2012 Service Pack 2 September 15, 2014
11.00.5532 2011.110.5532.0 2976982 Cumulative update package 1 (CU1) for SQL Server 2012 Service Pack 2 July 24, 2014
11.00.5522 2011.110.5522.0 2969896 FIX: Data loss in clustered index occurs when you run online build index in SQL Server 2012 (Hotfix for SQL2012 SP2) June 20, 2014
11.00.5058 2011.110.5058.0 SQL Server 2012 Service Pack 2 (SP2) June 10, 2014
11.00.3487 2011.110.3487.0 3038001 Cumulative update package 15 (CU15) for SQL Server 2012 Service Pack 1 March 16, 2015
11.00.3486 2011.110.3486.0 3023636 Cumulative update package 14 (CU14) for SQL Server 2012 Service Pack 1 January 19, 2015
11.00.3460 2011.110.3460.0 2977325 MS14-044: Description of the security update for SQL Server 2012 Service Pack 1 (QFE) August 12, 2014
11.00.3482 2011.110.3482.0 3002044 Cumulative update package 13 (CU13) for SQL Server 2012 Service Pack 1 November 17, 2014
11.00.3470 2011.110.3470.0 2991533 Cumulative update package 12 (CU12) for SQL Server 2012 Service Pack 1 September 15, 2014
11.00.3449 2011.110.3449.0 2975396 Cumulative update package 11 (CU11) for SQL Server 2012 Service Pack 1 July 21, 2014
11.00.3437 2011.110.3437.0 2969896 FIX: Data loss in clustered index occurs when you run online build index in SQL Server 2012 (Hotfix for SQL2012 SP1) June 10, 2014
11.00.3431 2011.110.3431.0 2954099 Cumulative update package 10 (CU10) for SQL Server 2012 Service Pack 1 May 19, 2014
11.00.3412 2011.110.3412.0 2931078 Cumulative update package 9 (CU9) for SQL Server 2012 Service Pack 1 March 18, 2014
11.00.3401 2011.110.3401.0 2917531 Cumulative update package 8 (CU8) for SQL Server 2012 Service Pack 1 January 20, 2014
11.00.3393 2011.110.3393.0 2894115 Cumulative update package 7 (CU7) for SQL Server 2012 Service Pack 1 November 18, 2013
11.00.3381 2011.110.3381.0 2874879 Cumulative update package 6 (CU6) for SQL Server 2012 Service Pack 1 September 16, 2013
11.00.3373 2011.110.3373.0 2861107 Cumulative update package 5 (CU5) for SQL Server 2012 Service Pack 1 July 16, 2013
11.00.3368 2011.110.3368.0 2833645 Cumulative update package 4 (CU4) for SQL Server 2012 Service Pack 1 May 31, 2013
11.00.3350 2011.110.3350.0 2832017 FIX: You can’t create or open SSIS projects or maintenance plans after you apply Cumulative Update 3 for SQL Server 2012 SP1 April 17, 2013
11.00.3349 2011.110.3349.0 2812412 Cumulative update package 3 (CU3) for SQL Server 2012 Service Pack 1 March 18, 2013
11.00.3339 2011.110.3339.0 2790947 Cumulative update package 2 (CU2) for SQL Server 2012 Service Pack 1 January 25, 2013
11.00.3335 2011.110.3335.0 2800050 FIX: Component installation process fails after you install SQL Server 2012 SP1 January 14, 2013
11.00.3321 2011.110.3321.0 2765331 Cumulative update package 1 (CU1) for SQL Server 2012 Service Pack 1 November 20, 2012
11.00.3153 2011.110.3153.0 2977326 MS14-044: Description of the security update for SQL Server 2012 Service Pack 1 (GDR) August 12, 2014
11.00.3128 2011.110.3128.0 2793634 Windows Installer starts repeatedly after you install SQL Server 2012 SP1 January 3, 2013
11.00.3000 2011.110.3000.0 SQL Server 2012 Service Pack 1 (SP1) November 6, 2012
11.00.2845 2011.110.2845.0 SQL Server 2012 Service Pack 1 Customer Technology Preview 4 (CTP4) September 20, 2012
11.00.2809 2011.110.2809.24 SQL Server 2012 Service Pack 1 Customer Technology Preview 3 (CTP3) July 5, 2012
11.00.2424 2011.110.2424.0 2908007 Cumulative update package 11 (CU11) for SQL Server 2012 December 17, 2013
11.00.2420 2011.110.2420.0 2891666 Cumulative update package 10 (CU10) for SQL Server 2012 October 21, 2013
11.00.2419 2011.110.2419.0 2867319 Cumulative update package 9 (CU9) for SQL Server 2012 August 21, 2013
11.00.2410 2011.110.2410.0 2844205 Cumulative update package 8 (CU8) for SQL Server 2012 June 18, 2013
11.00.2405 2011.110.2405.0 2823247 Cumulative update package 7 (CU7) for SQL Server 2012 April 15, 2013
11.00.2401 2011.110.2401.0 2728897 Cumulative update package 6 (CU6) for SQL Server 2012 February 18, 2013
11.00.2395 2011.110.2395.0 2777772 Cumulative update package 5 (CU5) for SQL Server 2012 December 18, 2012
11.00.9000 2011.110.9000.5 Microsoft SQL Server 2012 With Power View For Multidimensional Models Customer Technology Preview (CTP3) November 27, 2012
11.00.2383 2011.110.2383.0 2758687 Cumulative update package 4 (CU4) for SQL Server 2012 October 18, 2012
11.00.2376 2011.110.2376.0 Microsoft Security Bulletin MS12-070 October 9, 2012
11.00.2332 2011.110.2332.0 2723749 Cumulative update package 3 (CU3) for SQL Server 2012 August 29, 2012
11.00.2325 2011.110.2325.0 2703275 Cumulative update package 2 (CU2) for SQL Server 2012 June 18, 2012
11.00.2316 2011.110.2316.0 2679368 Cumulative update package 1 (CU1) for SQL Server 2012 April 12, 2012
11.00.2218 2011.110.2218.0 Microsoft Security Bulletin MS12-070 October 9, 2012
11.00.2214 2011.110.2214.0 2685308 FIX: SSAS uses only 20 cores in SQL Server 2012 Business Intelligence April 6, 2012

 

 

SQL Server Keyboard Shortcuts, to change the keyboard scheme or add additional keyboard shortcuts, on the Tools menu, click Options. Select the desired keyboard scheme on the Environment, Keyboard page. SQL Server Management Studio Keyboard Shortcuts

Action SQL Server 2012 SQL Server 2008 R2
Toggle the full-screen display SHIFT+ALT+ENTER SHIFT+ALT+ENTER
Scroll text up one line CTRL+UP ARROW CTRL+UP ARROW
Scroll text down one line CTRL+DOWN ARROW CTRL+ DOWN ARROW
Reverse the last editing action CTRL+ZorALT+BACKSPACE CTRL+Z
Restore the previously undone edit CTRL+SHIFT+ZorCTRL+YorALT+SHIFT+BACKSPACE CTRL+SHIFT+ZorCTRL+YorALT+SHIFT+BACK SPACE
Save the selected item CTRL+S CTRL+S
Save all CTRL+SHIFT+S CTRL+SHIFT+S
Close CTRL+F4 CTRL+F4
Print CTRL+P CTRL+P
Exit ALT+F4 ALT+F4
Open the current file in a browser CTRL+SHIFT+W No equivalent
Delete all text in the current file CTRL+SHIFT+DEL CTRL+SHIFT+DEL
Display the Go To Line dialog box CTRL+G CTRL+G
Display the Navigate To dialog box. CTRL+PLUS SIGN (+) No equivalent
Increase line indent TAB TAB
Decrease line indent SHIFT+TAB SHIFT+TAB
Make the selected text upper case CTRL+SHIFT+U CTRL+SHIFT+U
Make the selected text lower case CTRL+U CTRL+SHIFT+L
Make the selected text a comment CTRL+K, CTRL+C CTRL+K, CTRL+C
Uncomment the selected text CTRL+K, CTRL + U CTRL+K, CTRL + U
Open a new query with current connection CTRL+N CTRL+N
Open database in Object Explorer ALT+F8 ALT+F8
Specify values for template parameters CTRL+SHIFT+M CTRL+SHIFT+M
Run the selected portion of the query editor or the entire query editor if nothing is selected F5orCTRL+SHIFT+E F5orCTRL+EorALT+X
Parse the selected portion of the query editor or the entire query editor if nothing is selected CTRL+F5 CTRL+F5
Display the estimated execution plan CTRL+SHIFT+ALT+L CTRL+L
Cancel the executing query ALT+BREAK ALT+BREAK
Include actual execution plan in the query output CTRL+SHIFT+ALT+M CTRL+M
Output results in a grid CTRL+SHIFT+D CTRL+D
Output results in text format CTRL+T CTRL+T
Output results to a file CTRL+SHIFT+T CTRL+SHIFT+F
Show or hide the query results pane CTRL+R CTRL+R
Show the query results pane CTRL+SHIFT+ALT+R
Toggle between query and results pane F6 F6
Copy the result grid and headers to the clipboard CTRL+SHIFT+C No equivalent
Move to the next active window in Management Studio ALT+F6 ALT+F6
Open SQL Server Profiler CTRL+ALT+P CTRL+ALT+P
Display the Query Designer dialog from the query editor window CTRL+SHIFT+Q No equivalent
Run the sp_help system stored procedure ALT+F1 ALT+F1
Run the sp_who system stored procedure CTRL+1 CTRL+1
Run the sp_lock system stored procedure CTRL+2 CTRL+2
Run the stored procedure configured for this shortcut in the Tools, Options, Keyboard, Query Shortcuts dialog CTRL+3 CTRL+3
Run the stored procedure configured for this shortcut in the Tools, Options, Keyboard, Query Shortcuts dialog CTRL+4 CTRL+4
Run the stored procedure configured for this shortcut in the Tools, Options, Keyboard, Query Shortcuts dialog CTRL+5 CTRL+5
Run the stored procedure configured for this shortcut in the Tools, Options, Keyboard, Query Shortcuts dialog CTRL+6 CTRL+6
Run the stored procedure configured for this shortcut in the Tools, Options, Keyboard, Query Shortcuts dialog CTRL+7 CTRL+7
Run the stored procedure configured for this shortcut in the Tools, Options, Keyboard, Query Shortcuts dialog CTRL+7 CTRL+7
Run the stored procedure configured for this shortcut in the Tools, Options, Keyboard, Query Shortcuts dialog CTRL+8 CTRL+8
Run the stored procedure configured for this shortcut in the Tools, Options, Keyboard, Query Shortcuts dialog CTRL+9 CTRL+9
Run the stored procedure configured for this shortcut in the Tools, Options, Keyboard, Query Shortcuts dialog CTRL+0 CTRL+0
Action SQL Server 2012 SQL Server 2008 R2
Move to the SQL Server Management Studio menu bar ALT ALT
Activate the menu for a tool component ALT+HYPHEN ALT+HYPHEN
Display the context menu SHIFT+F10 SHIFT+F10
Display the New File dialog box to create a file CTRL+N CTRL+N
Display the New Project dialog box to create a new project CTRL+SHIFT+N CTRL+SHIFT+N
Display the Open File dialog box to open an existing file CTRL+OorCTRL+SHIFT+G CTRL+O
Display the Open Project dialog box to open an existing project CTRL+SHIFT+O CTRL+SHIFT+O
Display the Add New Item dialog box to add a new file to the current project CTRL+SHIFT+A CTRL+SHIFT+A
Display the Add Existing Item dialog box to add an existing file to the current project SHIFT+ALT+A SHIFT+ALT+A
Display the Query Designer CTRL+SHIFT+Q CTRL+SHIFT+Q
Close a menu or dialog box, canceling the action ESC ESC
Action SQL Server 2012 SQL Server 2008 R2
Move the cursor left LEFT ARROW LEFT ARROW
Move the cursor right RIGHT ARROW RIGHT ARROW
Move the cursor up UP ARROW UP ARROW
Move the cursor down DOWN ARROW DOWN ARROW
Move the cursor to the beginning of the line HOME HOME
Move the cursor to the end of the line END END
Move the cursor to the beginning of the document CTRL+HOME CTRL+HOME
Move the cursor to the end of the document CTRL+END CTRL+END
Move the cursor up one screen PAGE UP PAGE UP
Move the cursor down one screen PAGE DOWN PAGE DOWN
Moves the cursor one word to the right CTRL+ RIGHT ARROW CTRL+ RIGHT ARROW
Moves the cursor one word to the left CTRL+ LEFT ARROW CTRL+ LEFT ARROW
Returns the cursor to the last item. SHIFT+F8 No equivalent
Moves the cursor to the top of the document CTRL+PAGE UP No equivalent
Moves to the previous tab in the document CTRL+PAGE UP
Moves the cursor to the bottom of the document CTRL+PAGE DOWN No equivalent
Moves to the next tab in the document CTRL+PAGE DOWN No equivalent
Action SQL Server 2012 SQL Server 2008 R2
Start or continue debugging ALT+F5 ALT+F5
Stop debugging SHIFT+F5 SHIFT+F5
Step into F11 F11
Step over F10 F10
Step out SHIFT+F11 SHIFT+F11
Step into specific statement SHIFT+ALT+F11 No equivalent
Set next statement Ctrl+3 0 No equivalent
Show next statement ALT+NUM No equivalent
Implement the Run To Cursor command CTRL+F10 CTRL+F10
Display the QuickWatch dialog box CTRL+ALT+QorSHIFT+F9 CTRL+ALT+Q
Toggle breakpoint F9 F9
Enable breakpoint CTRL+F9 No equivalent
Delete the breakpoint. Only available in the Breakpoints window ALT+F9, D No equivalent
Open the Edit breakpoint labels dialog. Only available in the Breakpoints window ALT+F9, L No equivalent
Delete all breakpoints CTRL+SHIFT+F9 CTRL+SHIFT+F9
Display the Breakpoints window CTRL+ALT+B CTRL+ALT+B
Break all CTRL+ALT+BREAK CTRL+ALT+BREAK
Break at function CTRL+B No equivalent
Display the Watch 1 window CTRL+ALT+W, 1 No equivalent
Display the Watch 2 window CTRL+ALT+W, 2 CTRL+ALT+W, 1
Display the Watch 3 window CTRL+ALT+W, 3 CTRL+ALT+W, 3
Display the Watch 4 window CTRL+ALT+W, 4 CTRL+ALT+W, 4
Display the Autos window CTRL+ALT+V, A CTRL+ALT+V, A
Display the Locals window CTRL+ALT+V, L CTRL+ALT+V, L
Display the Immediate window CTRL+ALT+I CTRL+ALT+I
Display the Call Stack window CTRL+ALT+C CTRL+ALT+C
Display the Threads window CTRL+ALT+H CTRL+ALT+H
Display the Parallel Stacks window. CTRL+SHIFT+D, S No equivalent
Display the Parallel Tasks window CTRL_SHIFT+D, K No equivalent
Action SQL Server 2012 SQL Server 2008 R2
Help F1orSHIFT+F1 F1
Display SQL Server Books Online CTRL+F1 No equivalent
Open the Help Library Manager CTRL+ALT+F1 No equivalent
Display the SQL Server Resource Center Web page CTRL+ALT+F2 No equivalent
Display help for the current editor window SHIFT+F1 No equivalent
Help on “How Do I” No equivalent CTRL+F1
Books Online Contents No equivalent CTRL+ALT+F1
Books Online Index No equivalent CTRL+ALT+F2
Help Search No equivalent CTRL+ALT+F3
Dynamic Help No equivalent CTRL+ALT+F4
Help Favorites No equivalent CTRL+ALT+F

Back to top

Customize Menus and Shortcut Keys

Creating a Keyboard Accelerator for a Stored Procedure

To create a keyboard accelerator for a stored procedure On the Tools menu, click Options.

On the Keyboard page, select an unused keyboard combination in the Shortcut list.

In the Stored Procedure box, type the stored procedure name, and then click OK.
Adding a New Item to the Menu
To add a new item to the menu On the Tools menu, click Options.

In the Customize dialog box, on the Commands tab, click New Menu.

On the Commands box, drag New Menu to the menu bar and drop it where you want the new menu to appear.

On the menu, right-click New Menu, and in the Name box, type a name for the new menu.

In the Customize dialog box, select category such as File, and select a command such as Open File. Drag the command to the new menu. As you point to the new menu, the menu will expand. Drop the command onto the expanded menu.

In the Customize dialog box, click Close.
Changing a Toolbar Buttons Accelerator Key Using the Keyboard

To assign or change a toolbar button’s keyboard accelerator (using the keyboard) Press ALT+R to display the Rearrange Commands dialog box.

In the Rearrange Commands dialog box, use the arrow keys to select Toolbar.

Tab to the Toolbar list, and use the arrow keys to select the toolbar that contains the button you want to change, and then press ENTER.

Tab to the Controls list, and use the arrow keys to select the button you want to change.

Press ALT+M, to select Modify Selection.

Tab to the Name box on the shortcut menu, type a name for the toolbar button with an ampersand (&) before the letter that you want as the keyboard accelerator.

Press ENTER.

Tab to the Close button, and then press ENTER.
Opening the Keyboard Accelerator Dialog Box Using the Mouse

To access the dialog box for assigning or changing a keyboard accelerator (using the mouse) On the Tools menu, click Customize.

Make sure the toolbar you want to change is visible.

In the Customize dialog box, click the Toolbars tab.

Select the check box for the toolbar you want to display.

In the Customize dialog box, click the Commands tab.
Changing a Toolbar Buttons Accelerator Key Using the Mouse 
To assign or change a toolbar button’s keyboard accelerator (using the mouse)  Click the button on the toolbar.

In the Customize dialog box, on the Commands tab, click Modify Selection.

In the Name box on the shortcut menu, type a name for the toolbar button with an ampersand (&) before the letter that you want as the keyboard accelerator.

Press ENTER.

In the Customize dialog box, click Close.

Back to top

SQL server 2012 Service pack 1 upgrade bug issue there

1: SQL service did not turn up after the Service pack Upgrade and received the message at Event viewer “service specific error code 3417”

Workaround: To resolve the issue we have to repair the sql server service.

*    Launch the SQL Server 2012 Setup program (setup.exe) from the distribution media.
*    In the left navigation area, click Maintenance, and then click Repair.
*    Setup support rule and file routines run to ensure that your system has prerequisites installed and that the computer passes Setup validation rules. Click OK or Install to continue.
*    On the Select Instance page, select the instance to repair, and then click Next.
*    The repair rules will run to validate the operation. To continue, click Next.
*    From the Ready to Repair page, click Repair. The Complete page indicates that the operation is finished.

Note: The important note is the sql server is appeared along with all existing user databases so safely we can go for the existing environment. Earlier we have to attach the user database again after rebuild the system databases but in 2012 no need to worry for the same. As a safety   you can copy the data file and log files for user database and msdb database. The repair command rebuilds the system databases and the above option applicable if you don’t have the master database backup. Once the repair has been done successfully afterwards you have to start the sql server service manually from configuration manager.

2: The patch installer has failed to update the shared features. To determine the reason for failure, review the log files.

To resolve the above issue we have to install the Visual studio service pack. It is a known bug in SQL 2012 SP1 and it forces the need for a complete install of Visual Studio 2010.  The version that is installed I don’t think will work.

Note: SQL cannot be updated until Visual Studio is installed, and SP1 applied to VS2010.  Then we can update SQL to SP1. Reference link here

Back to top

 

SQL Server 2014 has several new features.

sql2014_now

SQL Server 2014 has several enforce new features that can definitely justify an upgrade. There is a no-doubt, the most notable new feature is the new In-Memory OLTP engine, which promises some big performance improvements for OLTP applications. The past couple of SQL Server releases have had a strong focus on business intelligence (BI), which makes the new In-Memory OLTP engine an especially for relational database professionals.

 New In-Memory OLTP Engine

The most important new feature in SQL Server 2014 is the In-Memory OLTP engine (formerly code-named Hekaton)-(I already mention in last blog). By moving select tables and stored procedures into memory, you can drastically reduce I/O and improve performance of your OLTP applications. Microsoft states that some applications can expect up to a 20x performance improvement. Edgenet, an early adopter, saw a 7x performance increase in its online and retail supply application.

The In-Memory OLTP engine is designed for high concurrency and uses a new optimistic concurrency control mechanism to eliminate locking delays. The In-Memory OLTP tables are copied into memory and made durable by transaction log writes to disk. An all-new lock-free engine processes the transactions for memory-resident tables. Stored procedure performance is improved by compiling the stored procedures into native code DLLs. Standard T-SQL stored procedures are interpreted, which adds overhead to the execution process. Compiling the stored procedures to native Win64 code makes them directly executable, thereby maximizing their performance and minimizing execution time.

You evaluate how the In-Memory OLTP engine will improve your database performance, Microsoft includes the new Analysis, Migrate, and Report (AMR) tool. Like its name suggests, the AMR tool analyzes your database and helps you identify the tables and stored procedures that would benefit from moving them into memory. It lists the expected performance improvements as well as any incompatibilities that need to be addressed. In addition, the AMR tool can help you perform the actual migration of tables to the new memory-optimized format.

The In-Memory OLTP engine works with commodity server hardware, but it has a number of limitations. For instance, not all of the data types are supported. Some of the data types that aren’t supported for memory-optimized tables include geography, hierarchyid, image, text, ntext, varchar(max), and xml. In addition, several database features can’t be used with the new In-Memory OLTP capability. Database mirroring, snapshots, computed columns, triggers, clustered indexes, identity columns, FILESTREAM storage, and FOREIGN KEY, CHECK, and UNIQUE constraints aren’t supported.

The In-Memory OLTP engine is supported on Windows Server 2012 R2, Windows Server 2012, and Windows Server 2008 R2 SP2. In addition, you need to be using the SQL Server 2014 Enterprise, Developer, or Evaluation edition. Notably, In-Memory OLTP won’t be supported on the SQL Server 2014 Standard edition.

Windows Server 2012 Integration

SQL Server 2014 provides improved integration with Windows Server 2012 R2 and Windows Server 2012. SQL Server 2014 will have the ability to scale up to 640 logical processors and 4TB of memory in a physical environment. It can scale up to 64 virtual processors and 1TB of memory when running on a virtual machine (VM).

SQL Server 2014 also provides a new solid state disk (SSD) integration capability that enables you to use SSD storage to expand SQL Server 2014’s buffer pool. The new buffer pool enhancements can help increase performance in systems that have maxed out their memory capability by using high-speed nonvolatile RAM (NVRAM) in the SSD drives as an extension to SQL Server 2014’s standard buffer pool. The new buffer pool extensions can provide the best performance gains for read-heavy OLTP workloads.

SQL Server 2014’s Resource Governor provides a new capability to manage application storage I/O utilization. First introduced with SQL Server 2008, the Resource Governor originally enabled you to limit the amount of CPU and memory that a given workload can consume. SQL Server 2014 extends the reach of the Resources Governor so that you can now manage storage I/O usage as well. The SQL Server 2014 Resource Governor can limit the physical I/Os issued for user threads in a given resource pool, allowing you to have more predictable application performance.

SQL Server 2014 also integrates with several new and improved features in Windows Server 2012 R2 and Windows Server 2012, i.e. SQL Server 2014 supports the OSs’ new Storage Spaces feature. With Storage Spaces, you can create pools of tiered storage to improve application availability and performance. In addition, SQL Server 2014 can take advantage of the OSs’ Server Message Block (SMB) 3.0 enhancements to achieve high-performance database storage on Windows Server 2012 R2 and Windows Server 2012 file shares. Many enhancements were made to SMB 3.0, with the most notable being SMB Transparent Failover and SMB Direct. The new SMB Transparent Failover feature provides highly reliable SMB storage that’s fully supported for applications like SQL Server and Hyper-V. With the new SMB Direct feature, you can leverage the NIC’s Remote Direct Memory Access (RDMA) feature to provide access speeds for SMB file shares nearing the access speed for local resources.
Enhancements to AlwaysOn Availability Groups – HA

SQL Server 2014’s AlwaysOn Availability Groups has been enhanced with support for additional secondary replicas and Windows Azure integration. First introduced with SQL Server 2012, AlwaysOn Availability Groups boosted SQL Server availability by providing the ability to protect multiple databases with up to four secondary replicas. In SQL Server 2014, Microsoft has enhanced AlwaysOn integration by expanding the maximum number of secondary replicas from four to eight. Readable secondary replicas are now available for read-only workloads, even when the primary replica is unavailable. SQL Server 2014 also provides Windows Azure AlwaysOn integration. This new integration feature enables you to create asynchronous availability group replicas in Windows Azure for disaster recovery. In the event of a local database outage, you can run your SQL Server databases from Windows Azure VMs. The new Windows Azure AlwaysOn availability options are fully integrated into SQL Server Management Studio (SSMS).

Back to top

 

 

Microsoft SQL Server 2012 White Papers

A Case Study on Building a Highly Scalable Enterprise Application that uses Massively Parallel Processing (MPP) to Deliver High Performance and Scalability on SSIS-SQL Server 2012
AlwaysOn Solution Guide: Offloading Read-Only Workloads to Secondary Replicas
AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups
AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups
An Analysis Services Case Study: Using Tabular Models in a Large-scale Commercial Solution
Benefits of Dynamic Rolling Twelve Month Dimension in Tabular Models
Choosing a Tabular or Multidimensional Modeling Experience in SQL Server 2012 Analysis Services
Cleanse and Match Master Data by Using EIM
Cross-cluster Migration of AlwaysOn Availability Groups for Operating System Upgrades
Data Access Tracing in SQL Server 2012
Exporting Master Data from Master Data Services
Extending SSIS Capabilities by Creating Custom SSIS Components for SharePoint
Extracting and Loading SharePoint Data in SQL Server Integration Services
Fast Track Data Warehouse Reference Guide for SQL Server 2012
Hardware Sizing a Tabular Solution (SQL Server Analysis Services)
How to Choose the Right Reporting and Analysis Tools to Suit Your Style
How to Cluster SQL Server Analysis Services
Installing Master Data Services in an AlwaysOn Environment
Introducing the BI Semantic Model in Microsoft® SQL Server® 2012
Leveraging a Hadoop cluster from SQL Server Integration Services (SSIS)
Master Data Services (MDS) Operations Guide
Microsoft BI Authentication and Identity Delegation
Microsoft IT: A case study on Business Continuity and Disaster Recovery – SQL Server 2012
Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery
Migrating Content Between Report Servers
Migration Guide: Migrating to AlwaysOn Availability Groups from Prior Deployments Combining Database Mirroring and Log Shipping, Part 1
Migration Guide: Migrating to SQL Server 2012 Failover Clustering and Availability Groups from Prior Clustering and Mirroring Deployments, Part 1
Optimized Bulk Loading of Data into Oracle
Patching SQL Server Failover Cluster Instances with Cluster-Aware Updating (CAU)
Performance Evaluation of XtremIO Flash Array in a SQL 2012 BI Environment
Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services
Plan Caching and Recompilation in SQL Server 2012
Planning Disaster Recovery for Microsoft SQL Server Reporting Services in SharePoint Integrated Mode
Power View Infrastructure Configuration and Installation: Step-by-Step and Scripts
Resource Governor in SQL Server 2012
Securing the Tabular BI Semantic Model
SQL Server 2012 Parallel Data Warehouse – A Breakthrough Platform for Next-Generation Data Warehousing and Big Data Solutions
SQL Server 2012 Upgrade Technical Guide
SQL Server Analysis Services to Hive
SSIS for Azure and Hybrid Data Movement
SSIS Operational and Tuning Guide
Statistical Analysis in Excel and SQL Server Analysis Services: An Example using Boxplot
Using AlwaysOn Availability Groups for High Availability and Disaster Recovery of Data Quality Services
Using DirectQuery in the Tabular BI Semantic Model

 

Back to top

One thought on “SQL 2012

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s