TechBubbles Microsoft Technology BLOG

CategorySQL Server 2012

For XML Auto Mode Example in SQL Server

For XML clause in Select Query allows you to return the query results in XML format. You can specify Auto, RAW, EXPLICIT and PATH in For XML clause. This post shows an example on Auto Mode. Auto Mode generates the nested XML as a result based on the column name order that you have specified in the select clause. The Auto mode can be used for generating simple XML hierarchies as a result. Define...

Schema Compare for SQL Server in Visual Studio 2013

This post outlines the new Schema Compare and Data Compare features in Visual Studio 2013 which supports SQL Server and Windows Azure databases. Schema Compare provides the capability to analyse and compare database schema along with presenting the results in visual and highly configurable manner.It can also calculate the actions necessary that affects the changes on the source. It supports...

Data Manipulation using Power Query and Power Pivot

Power Query is an excel Add-in which allows you to connect wide variety of data sources. Power Query has an intelligence to find a data set on a webpage. Once you have the data set you may notice that is not in right shape. Using Power Query you can combine data sets using join, merge operations. Power Query generates the script when you are performing the steps in loading and cleansing the data...

Tabular Models and Multidimensional Models in SQL Server 2012

  Tabular Model is a new data model introduced in SQL Server 2012 Analysis services. Tabula Models are in-memory databases which uses analytic engine named (VertiPaq) which delivers fast access to data to client applications like Excel and Power View. This post describes the differences between Tabular Model and Multidimensional model and outlines which model to choose and when over the...

Server Management Tips for SQL Server

Administrating the Server resources such as memory, processes and storage and low level resources such as locks, latches and queues are crucial as these are consumed by transactions. This post outlines some tips on managing server level and database level resources and also explains monitoring user sessions and their activity as well as concurrency structures such latches and locks. Managing...

Creating a Plan Guide in SQL Server 2012

Plan Guide is a very nice feature in SQL Server that allows you to apply hints to your queries without having to change the query type or text in the application. Plan Guides influence query optimization by using the query hints. This post shows you how to create a Plan Guide and apply to a query in SQL Server 2012. Take a look at the below query and it’s execution plan, it basically performs...

Big Data and StreamInsight

StreamInsight in SQL Server is a Complex Event Processing Engine which can manage and mine the data in real-time. A practical application has developed to stream Twitter data and analyze this data with Microsoft Azure and Hadoop. Application can be downloaded from here.  Power Pivot, Power View and Mobile Devices can be used as Front end tools to analyze this data. To get an understanding on...

Encrypting the session with SSL in SQL Server

Network packets are human-readable using a packet sniffer when SQL results passing between client machine and SQL server. Tabular Data Stream(TDS) is the protocol used by SQL server to form packets to be sent through the network.  This post explains the steps to encrypt a session with SSL in SQL Server.
Below is the screenshot of packet sniffer when data flowing from server to client

Login mapping errors on restored database

If you move a database from one server to another using backup\restore or detach/attach option then there is a chance of users become orphaned. Users logins mapping done by the SID and if there is a login present on the destination instance with same name but another SID then the user will not recognize and orphaned. Login mapping problem occurs only with SQL Logins when you move the database to...

Configuring Auditing in SQL Server 2012

The auditing session allows you to record events that occurring at server level or database level in file or in the windows event log. You can easily setup audit in SQL Server 2012 with Server Audit, define an audit and bind a specification that contains events to collect it. Database level auditing is only available in the Enterprise Edition. Follow the below steps to setup SQL Server Audit 1...

TechBubbles Microsoft Technology BLOG

Follow me

Archives

Tag Cloud