Tag Archives: SQL
SSMS Quick Install from PowerShell
Waned to share PowerShell snippet to download SQL Management Studio (SSMS) with one liner and the AKA MS redirect service. Cheers 
Code
iwr https://aka.ms/ssmsfullsetup -outfile ssms.exe; .\ssms.exe
Screenshot
Reference
VIDEO – Mini SQL Query
Review and demo of a great portable app named “Mini SQL Query” I use to verify SQL connectivity and run queries. Faster than full SQL Management Studio (SSMS). Portable app ZIP to download and double click EXE to run.
Cheers! 
Video
References
Poster – SharePoint List – When to Use What
The below visual captures ideas on SharePoint table data storage and when to use what. Rows and columns of data are common. Choosing the best hosting technology provides ease of support, quick time to market, performance, and scale for data growth.
SQL Server has good use cases, but comes with trade-offs including higher upfront setup effort and fewer end user controls (audit, search, version history). Can improve performance and reporting if those trade-offs are correctly addressed.
Select right tool for the job. Times for both.
Cheers! 
Download PDF
Download VSDX
VIDEO – code JS todo CRUD on Breeze & SQL Express (part 2 of 2)
Our next step in this series is cloud hosting. Each of the local source components has a Microsoft cloud destination equivalent. Here is the mapping:
- SQL Express > SQL Azure
- WebAPI > Azure Web App
- Angular SPA > Office 365 SharePoint Content Editor
In the video below I walk through all of these steps to migrate the local “todo” CRUD application created earlier to be fully hosted in Microsoft’s cloud. From there, we have a fully operational business application running on Office 365 with all custom code hosted in Azure.
Enjoy! 
Video
VIDEO – code JS todo CRUD on Breeze & SQL Express (part 2 of 2) from Jeff Jones on Vimeo.
VIDEO – Cloud migration of JS todo CRUD to Azure & Office 365 (part 2 of 2)
Live walk through of hosting “Todo List” CRUD application on the Microsoft cloud. Continued from the first video where we coded a full local application, this video migrates the application front/back end to the cloud. HTTPS Breeze WebAPI endpoint hosted on Azure for the backend with HTTPS Office 365 SharePoint Content Editor Web Part for the frontend GUI end user experience.
Custom HTTP header was added for extra security as an application “client secret” which is required for the WebAPI middle tier to respond to HTTP traffic. Missing HTTP header will respond with “null” and not execute any Dot Net code.
- Azure https://portal.azure.com
- Office 365 https://portal.office.com/
Enjoy! 
Diagram
Video
References
- https://breeze.github.io/doc-net/webapi-controller.html
- http://stackoverflow.com/questions/13616445/setting-ajax-headers-in-breeze
- install-package Breeze.WebApi2.EF6
- install-package Newtonsoft.Json
- install-package Microsoft.AspNet.WebApi.Odata
- install-Package Microsoft.AspNet.WebApi.Cors
VIDEO – code JS todo CRUD on Breeze & SQL Express (part 1 of 2)
Live coding walk through of a locally hosted “Todo List” CRUD application with Breeze WebAPI 2.2 middle tier and SQL Express relational database storage. This baseline allows developers to be more productive locally with fast feedback loops. Iterate code changes, reload test, and repeat. This technology stack can create sophist acted business application with input validation, advanced data schema, workflows, and more bringing in the latest web innovations from NPM and NodeJS.
Custom HTTP header was added for extra security as an application “client secret” which is required for the WebAPI middle tier to respond to HTTP traffic. Missing HTTP header will respond with “null” and not execute any Dot Net code.
In this video I used several components and want to share links for each:
- AngularJS https://angularjs.org/
- Visual Studio Code https://code.visualstudio.com/
- BreezeJS http://www.getbreezenow.com/breezejs
- WebAPI https://www.asp.net/web-api
- SQL Express https://www.microsoft.com/en-us/sql-server/sql-server-editions-express
In a future video, I’ll show how this application can then be deployed to Office 365 and SQL Azure to leverage Microsoft’s cloud hosting.
Enjoy! 
Diagram
Video
Live code JS todo CRUD on Breeze & SQL Express from Jeff Jones on Vimeo.
References
- https://breeze.github.io/doc-net/webapi-controller.html
- http://stackoverflow.com/questions/13616445/setting-ajax-headers-in-breeze
- install-package Breeze.WebApi2.EF6
- install-package Newtonsoft.Json
- install-package Microsoft.AspNet.WebApi.Odata
- install-Package Microsoft.AspNet.WebApi.Cors
NEW – Microsoft Technical Guides
Last week many great technical guides were released on the MS Download Center. They describe the business benefits of new technology and technical steps to implement. Below are links with description and download for each (ZIP/PDF/PPT/DOC).
Fantastic visuals here quickly communicate the benefits of Azure, Dev Ops, and modern IT design.
Check it out. Enjoy! 
Guides
- Hybrid IaaS technical guides – These documents provide a technical overview of Hybrid Infrastructure as a Service solutions.
- Analytics technical guides – This zipped folder provides technical guidance for analytics on Azure.
- Application dev/test business documentation – This zipped folder contains documents that provide an overview of application dev/test offerings for partners.
- IoT technical documentation – This document provides technical guidance on deploying cloud services for IoT.
- Power BI technical guides – This zipped folder provides technical guidance for deploying Power BI, creating dashboards, and other technical details of managed services.
- Application dev/test technical guides – These documents provide a technical overview of application development and testing.
- SQL Backup to Azure GTM materials – This zipped folder contains campaigns, sample service offers, and templates for SQL backup to Azure.
- Analytics business documentation – This zipped folder includes an overview of Analytics on Azure, including the Analytics vision and data platform scenarios.
- Azure SQL DB technical documentation – This zipped folder contains best practices and deployment documentation for Azure SQL database.
- SQL in Azure VMs technical documentation – This zipped folder contains best practices and deployment documentation for SQL running in Azure VMs.
- Backup and Disaster Recovery business documentation – This zipped folder contains documents that provides an overview of backup and disaster recovery offerings for partners.
- Getting started on CSP for Azure – This FAQ style document is a “Getting Started guide” for Microsoft Azure services in the CSP program.
- SQL Backup to Azure business documentation – This presentation provides guidance on SQL Backup to Azure – business discussions.
- SQL Backup to Azure technical documentation – Technical deployment kit with deployment guidance of SQL backup to Azure.
- Azure SQL DB business documentation – This zipped folder contains presentations that provide guidance on Azure SQL database – business discussions.
- DevOps on Azure business documentation – This zipped folder provides an overview of DevOps on Azure.
- Overview of CSP in Azure – This presentation provides an overview of the Azure in CSP program.
- Disaster Recovery for SQL technical documentation – This zipped folder contains technical documentation for disaster recovery for SQL, along with a SQL 2014 hybrid kit.
- Disaster Recovery for SQL business documentation – This zipped folder contains presentations that provide guidance on disaster recovery for SQL – business discussions.
- Power BI business documentation – This presentation provides guidance on incorporating Power BI into your managed services practice through CSP.
- SQL in Azure VMs business documentation – This zipped folder contains presentations that provide guidance on SQL in Azure VMs – business discussions.
- How partners can make money with Azure IaaS – This presentation provides an overview of how partners can make money with Azure IaaS.
Thumbnails
Screenshots
Download PPT (200MB)
SQL Server 2012 Service Pack 2
Today I noticed a bunch of new Microsoft Downloads posted on that RSS feed for various components within SQL Server 2012. Service Pack 2 is now out and each component has a new download available. These can be helpful for anyone working on Business Intelligence projects with SharePoint 2013. Links below. Enjoy! 
SQL Server 2012 SP2 = 11.00.5058.0
Microsoft® SQL Server® 2012 Service Pack 2 (SP2)
Microsoft® SQL Server® 2012 SP2 Report Builder
Microsoft® SQL Server® 2012 SP2 Reporting Services Add-in for Microsoft® SharePoint®
Microsoft® SQL Server® 2012 SP2 PowerPivot® for Microsoft SharePoint 2013®
Microsoft® SQL Server® 2012 Service Pack 2 (SP2) Master Data Services Add-in For Microsoft® Excel®
Microsoft® SQL Server® 2012 SP2 PowerPivot for Microsoft Excel® 2010
Microsoft® SQL Server® 2012 SP2 Semantic Language Statistics
SQL Azure – How to connect SQL Management Studio and import Northwind database
Taking my cloud education further … I wanted to explore the SQL Server relational database offering. The first trick was to create a new “server” from the Azure portal. After that I opened the web based SQL Azure portal to confirm it was online and healthy.
I was surprised by how easy it was to create a connection. Simply create a firewall exclusion to your current IP address … and boom … you can open the SQL Azure with SQL Management Studio locally. Pretty cool! 
- Azure Management Portal https://manage.windowsazure.com/
The Azure Management Portal is proving very capable. It even has a handy popup dialog with connection strings for convenient copy/paste. Here is the process I used to import sample Northwind data:
- Open both instances with SQL Management Studio
- Right click local tables to build “CREATE TABLE” statements. Copy/paste those from local to Azure and execute. This creates the schema. There are some limitations of Azure so you may have to double check your TSQL syntax (http://www.tewari.info/2009/09/10/sql-azure-notes/)
- Use the SQL Import wizard to migrate data. For Azure be sure to select “.Net Framework Data Provider for SqlServer” destination.
- To resolve red errors, ignore [ntext] or [image] data types during migration. SQL Azure does NOT support [ntext] or [image] so I just removed those columns. Not sure of the best way to migrate this type of data yet. [ntext] should probably cast to VARCHAR(8000) and truncate anything longer. [image] should probably be saved to the filesystem (JPG/PNG/etc.) and then a URL string pointer to the image file kept in SQL.
- Click next and finish. Congratulations! You now now have sample data in SQL Azure.
