Tutorials for SQL Server 2016
MS SQL Server 2012 HotfiX’s use syntax i.e. Select @@version
|SQL Server 2012 Service Pack 2||11.0.5058.0|
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.
|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|
|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 theTools, Options, Keyboard, Query Shortcuts dialog||CTRL+3||CTRL+3|
|Run the stored procedure configured for this shortcut in theTools, Options, Keyboard, Query Shortcuts dialog||CTRL+4||CTRL+4|
|Run the stored procedure configured for this shortcut in theTools, Options, Keyboard, Query Shortcuts dialog||CTRL+5||CTRL+5|
|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|
|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|
|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 theBreakpoints 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 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|
|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|
Data import from SharePoint 2013 Online Office 365 using SSIS
It’s all about cloud, cloud now-a-days, every1 are going towards the cloud.
In this post I will be explaining on how to extract/Import data from SharePoint Lists which is hosted on SharePoint Online of Office 365 using SSIS. There is no direct way to connect to the SharePoint Online using SSIS.
First connecting to MS SharePoint Online using SSIS, system need the below components installed before stating.
Microsoft OData Source for Microsoft SQL Server 2012 Download here
SharePoint Server 2013 Client Components SDK Download here
Microsoft OData Source for Microsoft SQL Server 2012 is the SSIS component which we use as source connection to the SharePoint Online Lists and SharePoint Server 2013 Client Components SDK allows us to connect to Sharepoint Online.
Download and Install both the components,
* Open the Visual Studio and create a new Integration Services Package
* Drag and Drop “Data Flow Task”
* In the “Data Flow Task” Drag and Drop “OData Source”
* Right-Click on “OData Source” and Click Edit
* Enter the “Connection Manager Name” and “Service Document Location” For Service Document Location, append “/_vti_bin/listdata.svc” to the SharePoint site
* For Office 365, we need to use “User this user name and password”
Click “All” and change the “Microsoft Online Services Authentication” to True
* Click “Test Connection” its green then go head or error fix it and test again
* Click “OK”
* Then Choose “Collection” in “User Collection or resource path”
* In the “Collection” choose the list you want to import
* When you click on “Preview” you will be able to see the data from the list
* Now add the destination connection and run the package to import data from the SharePoint 2013 Online list.
Let me know if you have any further question and your comments will be learning point.
Microsoft Certified Solutions Associate (MCSA)
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
SQL Server 2014 has several new features.
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