SQL Server Data Tools for Visual Studio 2013

There are several options for SQL Server Data Tools depending on the version of Visual Studio as described here.

For this post we are using a Toshiba Laptop running Windows 7 (64-bit) Ultimate having a Visual Studio Express 2013 for Web which is one of the requirements in the following list:

• Visual Studio 2013 Professional,
• Ultimate,
• Premium,
• Express for Web, or
• Express for Windows Desktop

You can download the tool here:
https://msdn.microsoft.com/en-us/dn864412

You will need approximately 1.8GB of free space at for the full install point because it includes all possible components that might be required.

You have an option for the language to choose from:

SSDT_01
Choose language and download (English here).

You double click the downloaded program SSDTSetup.exe to begin installation.

SSDT_02

Accept the license terms to proceed.
The download and installation begins immediately.

SSDT_03
It may take a while to download and install.

SSDT_04

After installation you can find Visual Studio 2013 Data Tools as shown.

SSDT_05

A number of items will be added to your Control Panel Programs as shown.

SSDT_06
You can verify it in Visual Studio 2013 Express for web also as shown.

SSDT_07

How do you create SQL Server Projects using the SSDT?
Hints for creating projects:
Launch VS 2013 Web (for example)
Create New Project choosing SQL Server Project Template
Associate a SQL Server Database for the project by directly connecting to SQL Server; choosing to run a script or via a data-tier application

In the next post we look at using SQL Server Data Tools in Visual Studio 2013 Express for Web.

There are quite a few versions of SQL Server Data Tools. Which one should be used?

It all depends on what version of Visual Studio you have. However, note that, you can install stand alone versions of SSDT.

This table shows the versions suitable for Visual studio 2012; Visual Studio 2013; and Visual Studio 2015.

Note the following:
SSDT can be installed standalone or can be integrated into an existing Visual Studio installation. For the preview release, SSDT is supported with Visual Studio 2015 and Visual Studio 2013. If you already have the Professional or higher edition of Visual Studio 2015 or Visual Studio 2013 and did not opt out of installing SQL Server Data Tools during setup, then your machine has an existing installation of SSDT

Download links for the RTM versions:
SSDT 2015 for Visual Studio 2015:
https://msdn.microsoft.com/en-us/mt186501
SSDT 2013 for Visual Studio 2013:
https://msdn.microsoft.com/en-us/dn864412
SSDT 2012 for Visual Studio 2012:
https://msdn.microsoft.com/en-us/jj650015
Well how are they going to function on Windows 10?
If you do not have
Visual Studio 2012/2013/2015  Professional Edition or above you probably will get just a shell with only SSDT related items and nothing else (like VB, C#, etc.)


 

Overview of what you can learn from Database and Analytics sessions at Visual Studio Live!

The field of Data, big and small,l made a great leap forward from mere file based data to relatiional database systems and remained there for a long time and it is still very much used. However, the advent of Internet and social media created new needs not addressed by RDBMS and gave rise to NoSQL databases. On the other hand the enormous amount of data brought in by media made it look for other tools bringing Big Data, now a byword. The sessions treat the enormous devveloping field in this fascinating area.

If you do manage to attend you can get some new information and brush up some old stuff. It should be interesting to track the developments.

These are the session details in the Database and Analytics area.

SQL Server of course is center stage. Topics under SQL Server 2014 covers the following:

• SQL Server Data Tools (SSDT)
• Reporting Services for Developers
• Memory-Optimized Tables and Columnstore Indexes in SQL Server 2014
• Microsoft Azure SQL Database

TH03 Implementing Data Warehouse Patterns - Attendees Choose
10/01/2015   8:00am - 9:15am
You will learn:
•Techniques for Populating Fact Tables and Dimension tables using T-SQL MERGE statements
•Special considerations for managing Type 2 Slowly Changing dimensions
•Tips for implementing many of the patterns found in the Kimball methodology
 You will also get to understand the following:
Type 2 changing dimensions
Confirmed dimensions
Dimension outriggers
Role-playing relationhsips
Many-tomany bridge table relationships
Factless Fatc tables
etc.

TH07: Power BI 2.0: Analytics in the Cloud and Excel
10/01/2015   9:30am - 10:45am
Get to speed quickly on what Power BI has done since version 1 and what's new in Version
Try to read up on my posts in blogs and you might have already learnt a lot!
TH11: Busy Develoepr's Guide to NoSQL
10/01/2015   11:00am - 12:15pm

You will learn:
•About NoSQLs
•What NoSQLs offer that the RDBMS don't
•When and how (and when not) to use them

TH15  Big Data and Hadoop with Azure HDInsight

10/01/2015   1:30pm - 2:45pm

 

• Learn what Hadoop is, how it works, what to do to get it running on the Azure cloud and how to integrate it.
• Learn how to integrate it with SQL Server BI, Excel and third party tools

TH19  Predictive Analytics and Azure Machine Learning
10/01/2015   3:00pm - 4:15pm

You will learn:
•Learn the fundamentals of predictive analytics, including which algorithms work best for specific scenarios
•Learn how to build Azure Machine Learning experiments and models
•Get exposed to the R programming language and see how to integrate R code into Azure ML experiments

W19  SQL Server Reporting Services - Attendees Choose Topics
[This presentation takes some of the more complicated topics and offers them as choices that attendees can pick for the session.]
You will learn:
•About advanced SSRS features "in action", as implemented in actual SSRS client applications
•The implementation of mass-automated report delivery of data driven subscriptions in SSRS (and also see .NET code used in SSIS scripts for customized delivery of reports)
•A general understanding of Microsoft's current and future offerings for SSRS in the Cloud

W15  Transact-SQL for Application Developers - Attendees Choose Topics
09/30/2015 3:00pm - 4:15pm
[Attendees choose from 25 possible topics - plus you'll walk away with all 25 code samples.]
You will learn:
•About the new language enhancements in SQL 2012
•Where sub-queries are necessary
•To gain some appreciation of performance optimization

W11  Real World SQL Server Data Tools
09/30/2015   1:30pm - 2:45pm
You will learn:
•Techniques for importing existing legacy SQL Server schemas with old, dead code. Basically, how do you take the kind of messy, not-well-maintained databases that I see at customer sites and bring it into SSDT without losing your sanity and giving up?
•About real enterprise application databases that often have references across database boundaries, and how to identify and variable-ize these database objects and deal with cross-database and cross-SSDT project references.
•How to handle incremental automated deployment scenarios from the command line or TFS Build and manage 'lookup' data as part of these deployment scenarios.

M01  Workshop: Big Data, Analytics and NoSQL: Everything You Wanted to Learn But Were Afraid to Ask
09/28/2015  9:00am - 6:00pm
"Who has time to learn about Big Data, Analytics and NoSQL? "
Perhaps you can learn a little bit of all the following:
Big Data
NoSQL
HBase
Cassandra
MongoDB
DynamoDB
DocumentDB
HDInsight

Read more here:
https://vslive.com/Events/New-York-2015/Tracks/Database-and-Analytics.aspx

SQL Server 2016 CTP 2.3 is ready to download

SQL Server 2016 CTP 2.3 is available since September 2, 2015. Several features have been enhanced. Looks like integration of R Language with SQL Server 2016 is built-in"

The following are the stated enhancements in this preview:

• Row Level Security support for in-memory OLTP tables
• A core engine scalability improvement, dynamically partitioning thread safe memory objects by NUMA or CPU, which enables higher scalability
• In-memory data warehouse (column store) performance optimizations
• Many-to-many derived hierarchies in Master Data Services
• Several performance improvements to SQL Server Analysis Services, including DAX query performance
• Enhancements to Reporting Services, including an updated Report Builder with a modern theme and report rendering for modern browsers

These are additional enhancements besides the above:
Improvements to the SQL Server Integration Service, Query Execution, and DBCC CHECKDB.

Technical Datasheet is  here (download PDF).

Download the evaluation edition here.

Visualizing Data using Power BI from RESTful Web Services

RESTful Services are much simpler than SOAP based services using WSDL. SQL Server Database can be exposed as a resource through a RESTful service and using RESTful API you can get data formatted in a standard way for different kinds of applications.

Some of the database examples that can be used for working with RESTful web services may be found here:

Northwind Service:
services.odata.org/northwind/northwind.svc
AdventureWorks Service:
http://www.odata.org/odata-services/ and AdventureWorks OData Service here,

http://blogs.msdn.com/b/derrick_vanarnams_blog/archive/2012/09/06/announcing-the-adventureworks-odata-feed-sample.aspx, or

http://services.odata.org/AdventureWorksV3/AdventureWorks.svc

You can easily connect to OData data feed from the above services using PowerBI as detailed here:
http://hodentekmsss.blogspot.com/2015/08/managing-relationships-in-power-bi.html
Once you are connected you can easily visualize data which is Page1 of the visualization as shown here:
.

V_Odata01.png

Publishing the report:
You Publish this report to PowerBI.com site. Before publishing you may have to Save the changes you have made.

V_Odata02.png

In order to publish you need a Power BI account.

V_Odata03.png

For Microsoft Data Management Gateway read here:

http://www.microsoft.com/en-us/download/details.aspx?id=39717

Creating data visualization
The following describes a step-by-step procedure to create a visualization report such as the one you saw earlier.

Step 1: Click New Page in the ribbon to add a new page to your report.
The new page becomes Page 1 while the previous becomes Page 2

Step 2: Click ProductName under Fields. The ProductName Column gets added to the Page as shown. In Visualizations, ProductName (all) is added.

V_Odata04.png

Under Visualizations click on ProductName (all) under Visual Level Filters. This opens up all the product names as shown.

V_Odata05.png

You can filter the product name by choosing a couple of them (or as many as required for your report) as shown (here a dozen or so chosen).

V_Odata06.png

Step 3:

Click Order_Details under Fields and choose Quantity and Price (by placing check marks) as shown. These gets added to the visualization page.

V_Odata07.png

Step 4: Click on Clustered Column chart and the chart is created in the page as shown.

V_Odata08.png

Step 5: You can make changes to the chart by clicking on the Format icon right next to chart.

V_Odata09.png

Step6: For example you can make changes to the chart title by expanding the configurable items of the Title as shown.

V_Odata10.png

In Page 2 all the products were chosen and there was a vertical scrollbar to review a product and in the present page only few are chosen.

It is very easy to create visualizations and modify them quite easily.

Step 7: Click on Multi-row card under visualizations and the page visualization changes accordingly as shown.

V_Odata10.png

SQL Server Backend for WebApps

We live in a data-centric world.

Data in your store changes with time and you may have to handle data that need to scale. It follows that the applications that you build should scale. Also the developers who work for you may be more proficient in one programming language than the other. Azure Web Apps using SQL and NoSQL database backends are most suited, as scaling is built into Azure. Further Azure Web apps can be written in a number of languages that include: 

• .NET
• Java
• PHP
• Node.js
• Python

Most developers can handle two or more from the above list.

Infrastructure management is highly automated and scaling is no problem as Auto scaling capability helps you to go up or down depending on your traffic.

Web Apps with backend are really crucial for your business and your backend can be SQL Database (relational) or NoSQL (non-relational). Microsoft can handle both types, relational with SQL Azure database and NoSQL with DocumentDB (service that helps you to store, index and query JSON-based data. Review references for DocumentDB on Hodentek blog at the conclusion of this post.

You are not sure but would like to try it out for free. Here is the link (https://azure.microsoft.com/en-us/documentation/scenarios/web-app/) where you can get started.

Free trial may be available only for new users. I have been using Azure since it's beta and I have tried to get the free service but I have not been successful. This may be different now.

References:
Format SQL Response in JSON Format
Moving data to DocumentDB
Data store for IOT
Getting to know DocumentDB-2
Getting to know DocumentDB-1
Signing up for Azure DocumentDB
DocumentDB is announced
 

JDBC Type 4.2 SQL Server Driver availability is announced

Microsoft has announced the availability of JDBC Driver for SQL Server that implements full compliance with JDBC Specifications 4.1 and 4.1. It supports the JDK 1.8 and the Transport Layer Security (TLS) for security over the Internet.  The JDBC 4.2 driver also supports the SQL Server's Always on feature.

Download link for JDBC 4.2 Driver is here (http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774)

 

JDBC is an important component of Microsoft Data Connectivty scheme:

JDBCMicrosoft.png\
The source for this information and image is here .

Review here for the usage of this driver while connecting to data source:

New SQL Database ODBC driver from devart

Here are some quick facts about this driver;

Direct connection requiring just TCP / IP and there is no need for SQL Azure Client software. This mode of access implies better performance, reliability and improved deployment process. Packaging of your software lighter.

The current version supports many platforms, Windows, Linux, Mac OS X for both architectures 32bit and 64bit.

Common ODBC interfaces for Data Types and ODBC API functions are supported.

The author has not received any compensation from devart.


More info and download details here.