Robert Sheldon looks at ways to connect to Azure SQL Data Warehouse:
Unlike SSMS, Microsoft does support connecting to SQL Data Warehouse from Visual Studio, via the database engine features in SSDT. When you get into the Visual Studio/SSDT environment, open SQL Server Object Explorer, which is similar to Object Explorer in SSMS. From there, click the Add SQL Server button.
When the Connect dialog box appears, provide the server name, select SQL Server Authentication, and then specify the login name and password, as shown in the following figure.
It is a bit surprising that you can’t easily connect via SSMS 2014. Maybe that’s changed with SSMS 2016?
Julie Koesmarno has a Q&A on Azure SQL Database:
Q: Is there going to be down time when I scale up/down? What’s going to happen to my existing connections?
Extracted from Change the service tier and performance level (pricing tier) of a SQL database:
Note that changing the service tier and/or performance level of a database creates a replica of the original database at the new performance level, and then switches connections over to the replica.No data is lost during this process but during the brief moment when we switch over to the replica, connections to the database are disabled, so some transactions in flight may be rolled back. This window varies, but is on average under 4 seconds, and in more than 99% of cases is less than 30 seconds. Very infrequently, especially if there are large numbers of transactions in flight at the moment connections are disabled, this window may be longer.
The duration of the entire scale-up process depends on both the size and service tier of the database before and after the change. For example, a 250 GB database that is changing to, from, or within a Standard service tier, should complete within 6 hours. For a database of the same size that is changing performance levels within the Premium service tier, it should complete within 3 hours.
Video by Joe Idziorek on Service Tiers and how to scale up and down using Azure Portal is available here.
Read the whole thing. There are some great questions and answers in this set.
Forrester has named Microsoft a leader in the Hadoop cloud solutions space:
This week, we’re excited that Forrester recognized Microsoft Azure as a leader in their Big Data Hadoop Cloud Solutions. Apache Hadoop as a technology has become popular amongst organizations to unlock insights from data of all size, shape, and speed. Hadoop power solutions to help businesses improve their performance, educators to better connect with the needs of their students, medical professionals to improve the quality of their care, or researchers to accelerate new advancements in science.
As an example, Ultra Tendency uses Hadoop to achieve something not possible before – visualize more than 27 million distinct sensor readings to give Japanese citizens accurate, up-to-date information about the radiation contamination from the Fukushima nuclear plant meltdown. More and more organizations are also deploying Hadoop in the cloud with 47% of Forrester’s respondents to a 2015 survey increasing their cloud deployments either by 5-10% (37%) or more than 10% (10%).1 This makes sense because the cloud allows you to scale elastically on demand to handle the processing of any amount of data.
AWS and IBM also have very good solutions, and Google is trying to get a stronger foothold on the cloud game.
James Serra talks about “cool storage” in Azure Blob Storage:
The access tiers available for blob storage accounts are “hot” and “cold”. In general, hot data is classified as data that is accessed very frequently and needs to be highly durable and available. On the other hand, cool data is data that is infrequently accessed and long-lived. Cool data can tolerate a slightly lower availability, but still requires high durability and similar time to access and throughput characteristics as hot data. For cool data, slightly lower availability SLA and higher access costs are acceptable tradeoffs for much lower storage costs. Azure Blob storage now addresses this need for differentiated storage tiers for data with different access patterns and pricing model. So you can now choose between Cool and Hot access tiers to store your less frequently accessed cool data at a lower storage cost, and store more frequently accessed hot data at a lower access cost. The Access Tier attribute of hot or cold is set at an account level and applies to all objects in that account. So if you want to have both a hot access tier and a cold access tier, you will need two accounts. If there is a change in the usage pattern of your data, you can also switch between these access tiers at any time.
It looks like there shouldn’t be a performance difference between the two; it’s more of a cost difference in which you might be able to save money by choosing your tier wisely.
Walking through this, we just need to create a secure string for our password and then use the Set-AzureRmSqlServer cmdlet and pass the secure string to -SqlAdministratorPassword argument. Easy as that and we don’t even need to know what the previous password was. With this in mind, I also want to call out that you can only change the password and not the admin login name. While this is not such a big deal, be aware that once you have an admin login name, you are stuck with it.
Mike promises that his next blog post won’t take a month to publish. Here’s hoping he’s right.
John White combines Microsoft Flow with Power BI to generate real-time reports:
I may blog about that solution in the future, but with the Future of SharePoint event rapidly coming up, my BI Focal fellow collaborator, Jason Himmelstein convinced me that there was something more interesting that we could do with this. How about near real time monitoring of Twitter conversations for the event? All of the pieces were in place.
We rolled up our sleeves, and in relatively short order, had a solution. Jason has written about the experience on his SharePoint Longhorn blog, and he has included the videos that we put together, so I can be a little less detailed in this post.
Today I learned about Microsoft Flow. Looks a lot like Zapier.
Brian Mitchell shows how to re-calculate prior days in Azure Data Lake using partitioning:
The question is what is the right time period to use? The answer is it depends on the size of your partitions. Generally, for managed tables in U-SQL, you want to target about 1 GB per partition. So, if you are bringing in say 800 mb per day then daily partitions are about right. If instead you are bringing in 20 GB per day, you should look at hourly partitions of the data.
In this post, I’d like to take a look at two common scenarios that people run into. The first is full re-compute of partitions data and the second is a partial re-compute of a partition. The examples I will be using are based off of the U-SQL Ambulance Demo’s on Github and will be added to the solution for ease of your consumption.
The ability to reprocess data is vital in any ETL or ELT process.
Jen Stirrup brings news on DocumentDB updates:
Let’s have a look at the biggest news: DocumentDB now has protocol support for MongoDB. From the industry perspective, this is great news. MongoDB is one of the most easily-recognised NoSQLs databases. Now that DocumentDB can be a great supporting act for MongoDB, it means that architects have a broader range of tools to support business needs in the enterprise data architecture, whilst increasing business capability using the Azure cloud. How does it work? Well, or MongoDB at the wire protocol level, which means that the existing MongoDB drivers will function against DocumentDB. For IT departments, it means that enterprises can persist data in DocumentDB behind the scenes. With it, it brings the reliability, stability and resilience of the Azure cloud. It also means that these technologies are accessible for small to medium enterprises in a way that they can afford, backed up with the stability and support from Microsoft Azure that they may find difficult to service on their own.
DocumentDB has a long way to go before it catches up to MongoDB, but these are improvements that close the gap a little bit.
Melissa Coates shows how to create a test environment for Azure Data Catalog:
Azure Data Catalog is a Software as a Service (SaaS) offering in Azure, part of the Cortana Intelligence Suite, for registering metadata about data sources. Check this post for an overview of Azure Data Catalog key features. (I’m a big fan of what Azure Data Catalog is trying to accomplish.)
There are a couple of particulars about Azure Data Catalog which make it a bit more difficult to set up a Demo/Test/Learning type of environment, including:
You are required to sign into Azure Data Catalog with an organizational account. Signing in with a Microsoft account (formerly known as a Live account) won’t work for Azure Data Catalog authentication, even if that’s what you normally use for Azure.
One Azure Data Catalog may be created per organization. Note this is *not* per Azure subscription – if your account has access to multiple subscriptions, it’s still one catalog per organization.
This method seems, shall we say, overly complicated. Here’s hoping that the Azure Data Catalog team find a way to improve this experience.
James Serra explains Azure SQL Database pricing:
DTU’s are explained at here. To help, there is a Azure SQL Database DTU Calculator. This calculator will help you determine the number of DTUs being used for your existing on-prem SQL Server database(s) as well as a recommendation of the minimum performance level and service tier that you need before you migrate to Azure SQL Database. It does this by using performance monitor counters.
After you use a SQL Database for a while, you can use a pricing tier recommendation tool to determine the best service tier to switch to. It does this by assessing historical resource usage for a SQL database.
For further information, check out this interesting article from a few months ago on V12 performance by Chris Bailiss.
Kevin Feasel
2016-05-11
Cloud, Warehousing