Since a communication failure within an AG is usually a “hair on fire” kind of event, a quick fix may be desired. The quickest fix I’ve found is to change the port on which the AG endpoint is listening. While the Microsoft help pages on this contain enough information to put together the right script eventually, the first time this happened to me I really would have liked a blog post specific to this issue that gave me the right script to use.
In keeping with this T-SQL Tuesday’s theme, below is my SQL Server present. Please note that I used 5023 as an example but your choice can be flexible depending on what else is consuming ports on your machine. Hopefully this helps somebody (or me if I manage to travel back in time and encounter this same issue)
In an ideal world, you’d want a bit more control over what’s running on various ports to prevent this scenario, but that’s probably wishful thinking.
I’m happy to announce Always Encrypted in Azure SQL Database is now generally available!
Always Encrypted is a feature designed to ensure sensitive data and its corresponding encryption keys are never revealed in plaintext to the database system. With Always Encrypted enabled, a SQL client driver encrypts and decrypts sensitive data inside client applications or application servers, by using keys stored in a trusted key store, such as Azure Key Vault or Windows Certificate Store on a client machine. As a result, even database administrators, other high privilege users, or attackers gaining illegal access to Azure SQL Database, cannot access the data.
To be honest, I’d much rather try Always Encrypted against an Azure SQL Database instance than an on-premise instance, mostly because if I hose Azure SQL Database that badly or the company decides that Always Encrypted isn’t a good fit, I can grab the data and dump the instance. It’s a little harder to do that with physical hardware or even an on-prem VM.
Ultimately, you should always choose performance first, and then – most certainly – intuitiveness second (because some poor soul might need to maintain your query). But personally, I find these quantifiers quite elegant for three reasons:
They express the quantification right where it belongs. With the comparison operator. Compare this with the solution using LIMIT, which may be far away, visually, from the greater-than operator. Quantifiers are much more concise, even than when using MAX() (in my opinion)
They’re very set oriented. I like thinking in terms of sets when I work with SQL. Whenever I can omit the
ORDER BYclause, I will. If only to avoid potentially slow operations (in case the database doesn’t optimise this, and a full
O(N log N)sort operation is invoked)
Quantified comparison predicates work on rows too, not just on single values.
I’ve known about these, but could probably count on one hand the number of times I’ve ever used one.
Now, inaccurate estimates won’t always be a problem, but it can cause issues with inefficient plan choices at the two extremes. A single plan might not be optimal when the chosen range will yield a very small or very large percentage of the table or index, and this can get very hard for SQL Server to predict when the data distribution is uneven. Joseph Sack outlined the more typical things bad estimates can affect in his post, “Ten Common Threats to Execution Plan Quality:”
Read the whole thing.
Azure Automation is essentially a hosted PowerShell script execution service. It seems to be aimed primarily at managing Azure resources, particularly via Desired State Configurations.
It is, however, a general PowerShell powerhouse, with scheduling capabilities and a bunch of useful features for the safe storage of credentials etc. This makes it an excellent tool if you’re looking to do something with PowerShell on a regular basis and need to interact with Azure.
Read the whole thing.
How many times you need to stream reading from a file and writing into another well here it is…Here we are using special character char(30) and char(31) as delimited so a quick replace using regex did it. It’s Simple,Quick, and Dirty 🙂
The last ten lines are the important ones. You’d use this stream read+write pattern when dealing with files larger than your total amount of available RAM.
Actually I’m talking about .NET Core. That Docker image is built on Debian Jessie, and it installs Hadoop and .NET Core. You can run compiled .NET Core assemblies just by running
dotnet /path/to/my/assembly.dll. The same image can run as Hadoop master and worker nodes, so all workers have .NET Core installed and can run .NET Core apps.
Which means you can run .NET Core apps as mappers or reducers in Hadoop jobs, thanks to theHadoop Streaming interface. Hadoop Streaming lets you shell out to any program the host can execute, to run the tasks for your job. Your code integrates with Hadoop at a basic level – through the standard input and output streams.
Elton has some good courses on other Hadoop-and-.NET topics (like his HDInsight deep dive course). It looks like running containerized Hadoop + .NET Core is getting closer, but still has room for improvement.
What does happen in Azure is Service disruption. Sometimes those are due to some hardware failures, but most of the time, they are a software problem: an upgrade in Azure software (Microsoft’s software) gone wrong. They happen occasionally, are typically short lived but if business continuity is a hard requirement, those are the ones you should protect your solution against.
In this article I’ll cover resilience for virtual machines. I’ll assume that you are usingAzure Traffic Manager or other DNS service to fail over the traffic from a primary to a secondary region.
He covers “stateless” VMs (like web servers or app servers) as well as “stateful” VMs (like database servers) and explains disaster recovery options for each.
This command enables you to take a copy of the schema and the statistics for an existing database into a new database. According to the documentation, DBCC CLONEDATABASE
Creates a new destination database that uses the same file layout as the source but with default file sizes as the model database.
Creates an internal snapshot of the source database.
Copies the system metadata from the source to the destination database.
Copies all schema for all objects from the source to the destination database.
Copies statistics for all indexes from the source to the destination database.
I knew there were ways of scripting out statistics, but DBCC CLONEDATABASE looks like a new and interesting beast.
You may recall something called the POODLE attack that revealed a vulnerability in SSL 3.0 and TLS 1.0. This particular server had SSL 3.0, TLS 1.0, and TLS 1.1 disabled in the registry. Also note that TLS 1.2 was NOT disabled. The server was running Windows 2012 R2. These protocols were disabled to prevent the possibility of a POODLE attack. If you are wondering how to disable these protocols on your servers then look at Microsoft Security Advisory 3009008. To disable them for the whole OS scroll down to the Suggested Actions section and look under the heading “Disable SSL 3.0 in Windows For Server Software”.
I also want to note that the PCI Security Standards Council pushed back the date for getting off of SSL and TLS 1.0 to June 30th, 2018. In addition to that, it should also be noted that Microsoft’s Schannel implementation of TLS 1.0 is patched against all known vulnerabilities.
The root cause is interesting: it’s because Database Mail requires .NET Framework 3.5. Ryan has more details, including a fix, so read on.