Hey friends! After a one-year hiatus I am BACK at the PASS Summit and ready to blog the day 1 keynote I will update this post throughout the morning so refresh every so often to see the changes. You can also follow along on Twitter – check out the #PASSsummit hashtag.
Reminder: My session is today at 1:30 in 6B, Query Store and Automatic Tuning in SQL Server, I hope to see you there!
Today’s keynote is headlined by Rohan Kumar (who I just got to meet thank you Mark Souza) and he’s stated that it will be a lot fun – you can preview what’s coming here. Rohan is the General Manager Database Systems Engineering for Microsoft, and there are a fair number of demos coming our way.
PASS President Adam Jorgensen starts off the day – this is the 19th PASS Summit. Holy cow. The PASS keynote is being streamed live via PASStv if you’re not available to be here in person. If you are at the Summit this week and you have any [problem with your SQL Server implementation that you need answered, go to the Microsoft Clinic. It is on the 4th floor near the Community Zone, and there are numerous Microsoft Engineers available to help. It’s an amazing resource at this conference.
Adam takes a moment to thank the individuals that volunteer for PASS – the organization is primarily run by volunteers, and that includes the PASS Board. The Board will have an open meeting on Friday at 2PM which anyone can attend. I f you have feedback or want to better understand how things work, definitely attend. Outgoing PASS Board members are Jen Stirrup and Chirs Woodruff. New elected members are John Martin, Diego Nogare, and Chris Yates. Adam takes a moment to thank outgoing Past President Tom LaRock and Exec Board member Denise McInerney as their time on the Board comes to a close.
Please take time to meet our sponsors in the Exhibit Hall. The support of our sponsors makes *so* many things possible not just at Summit, but throughout the year.
And Rohan takes the stage…
SQL Server 2017
Data, AI, and Cloud are three disruptive technology trends…and we need to figure out how to better migrate data to the cloud (I’m asking: how do we make it easier?).
At it’s core, the modern data estate enables simplicity. It takes in any type of data, and allows a hybrid setup between on-premises and the cloud. Rohan asks how many people believe they can move their data/solution to the cloud? About 1% of the attendees raise their hand. He then asks how many people think that deploying to the cloud or on-prem is what’s needed in the future? The majority of people raise their hands.
SQL Server 2017 was released October 2, 2017, and SQL Server 2016 was released April 1, 2016…that’s a very fast release cycle for Microsoft, and that’s been possible because of the cloud-first approach, which translates to an increased cadence of SP and CU releases. Reminder: in SQL Server 2017 there’s a shift to CU releases every month, and no more SPs. Glenn blogged about this in September. Rohan brings Bob Ward and Conor Cunningham on stage for the first demo. They’re wearing Cowboys jerseys. *sigh* If you see Bob this week ask him how the Rangers did this year…
Bob and Conor step through a demo showing the performance benefit of a new HPE DL580 Gen 10, using persistent scalable memory NVDIMMs – a query that takes 15 seconds on SSDs takes about 2 seconds on the HP storage. And it’s cheaper? I’m deferring to Glenn for the hardware details!!
Bob introduces a “typical” parameter sniffing issue – and then shows how to use Automatic Plan Correction (which relies on Query Store under the covers)…which I’ll be showing today in my session as well!
New features in SQL Server 2017:
- Advanced Machine Learning with R and Python
- Support for graph data and queries
- Native T-SQL scoring
- Adaptive Query Processing and Automatic Plan Correction
There is much more available in 2017, as noted in the release notes.
Tobias Ternstrom and Mihaela Blendea take the stage to talk about containers running SQL Server. Mihaela shows the build definition, which starts a container based on the SQL Server build. On top of that, restore production database to it and run any additional scripts (e.g. obfuscate and remove some data), then push out the images. Tobias starts typing in a command line window…this I love. He knows what he’s doing, but he’s always kind of winging it. Tobais gives a sneak peak of a tool that shows up as being named Carbon, but Rohan introduces it as Microsoft SQL Operations Studio. It works on Windows, Linux, and Mac to connect to a SQL Server database. So at some point SSMS will be deprecated? Yeah…just like Profiler 😉
Rohan comes back and talks a bit more about the cloud-first approach. Azure SQL Database is updated regularly, and on a monthly basis new CUs are being pushed out (CU1 for SQL Server 2017 has ALREADY been released). Multiple terabytes (yes TERABYTES) of telemetry data are captured every day from Azure implementations. This feedback goes right into making the product better (how else do you think they’re able to release builds and CUs faster?).
New deployment option in Azure: Managed Instances. It’s currently in private preview, but you get an entire SQL Server instance with PaaS benefits. This allows for much more of a lift and shift migration with minimal code changes. Microsoft is also working on a database migration service – this will not be easy and may not work for every solution, but it’s a step in the direction of making that process better and more reliable.
Working with Large Data/BI Solutions
The next data is showing performance and scale with Azure SQL Database hosted by Danielle Dean, a Principal Data Scientist at Microsoft. Reading in a lot of data – ingesting patient vitals into Azure database (1.4 million rows/sec via columnstore and in-memory). Azure Machine Learning Workbench is then used to take an existing model and put it into Azure SQL Database. Switching to SSMS (it’s not dead yet folks!!) you can query that model (it “looks” like a table), and use a stored procedure to predict against the model.
Scott Currie, the creator of Biml, on stage to talk about using the new Azure Data Factory with Biml. I’ll admit, this isn’t a technology I know, so I”m just listening at this point
Azure SQL Data Warehouse Designed from ground up to separate out storage and compute so that you can scale each independently. This design is very flexible and powerful, and provides significant ability to scale (up to 60 nodes currently), and it’s secure. Also launched in early in October: Azure SQL Data Warehouse Compute-Optimized Tier. This was a result of feedback from customers who had some DW queries that were running REALLY slow in Azure. The solution caches column segments (data) locally, and this cache survives failures, which then provides high performance for DW queries. Julie Strauss, a Principal Group Program Manager comes on stage to demo this.
Why are these behavioral analytic queries so compute-intensive? It’s a combination of the data that’s needed and the complexity of the query. Two kinds of analysis – funnel and cohort. Both use telemetry from customer interactions/purchases from web site clicks. The sophistication of the query is taking the vast about of data (100TB) and then fold it many times to create the different cohorts – the query takes about 6 seconds to read through that 100TB of data. I’d like to know how this is done…
PowerBI quick demo against data with 100+ million rows. Model built from Visual Studio sourcing data from Azure SQL Data Warehouse – very easy to deploy the model and then generate different visuals in PowerBI (clicky clicky drop was the “official” term used…I’m not kidding). Ability to also scale in Azure very quickly so only using resources really need (and thus only pay for what need and use).
Ok, there was one more demo but I’ll admit, I’m fading.
Rohan is wrapping up the keynote and talks about community and all of us working together and lifting each other up. Rohan gives a shout out to members of the community that have really given a lot back to others. He also mentioned Denny Cherry, a member of the community who had surgery a couple weeks ago. I had a recent update from a colleague that Denny is doing well – please send good thoughts his way!
And that’s a wrap. Off for a day of learning – have a great one friends!