I am passionate about using Microsoft Technology to maximise business benefit. Specializing in the Data Platform – SQL server, Azure SQL DB, Azure SQL DW, elastic pools, managed instances etc.
So any questions/ feedback – please Get In Touch
Copyright © in accordance with the Copyright, Designs and Patents Act 1988. All rights reserved. You are free to use any of the content here for personal use but need permission to use it anywhere or by any means (electronic, mechanical, photocopying, recording or otherwise).
It was just a matter of time until I started combining my cloud experience with “different” flavours of SQL Server. I haven’t used Linux since my university days (Oracle – errgh) but recently some Friends of mine ( using LAMP stack ) asked me couple of questions about SQL Server Linux.
There are many quality resources regarding SQL Server 2019, from eBooks to videos about the newest features and how one would implement these within your business. I recommend the following book, not because I was co-author, more so of the fact that the other authors have brilliant chapters giving quality high level information (with enough detail) to understand SQL Server 2019.
I mean, who better than Buck Woody to talk about Big Data Clusters???!
Moving to public cloud such as Azure, AWS or even private cloud services you need to be tracking costs and seeing if you are “effective”. What is the best way of doing this with Microsoft Azure?
Unfortunately, this is not an on-premises SQL Server install because I do not have the right operating system available for SQL Server 2019, which includes the below:
- Windows 2016 +
- Red Hat 7.3-7.6
- SUSE v12 SP2+
- Ubuntu 1.8
So, I am going to use my clicking skills and spin a Machine up in Azure utilizing the market place, so I can get an image of SQL Server installed on the VM already.
Ok, so Azure SQL doesn’t really have its own error log based somewhere on a machine within \\MSSQL\Log directory but the closest thing you will get is a system catalog view called sys.event_log which is very useful. It will get you information about all sort of event types such as:
- Successful connections
- Failed connections
- Throttling issues
- Blocked by firewall attempts
- Connection termination
The full message when connecting to your Azure SQL Database is:
Reason: An instance-specific error occurred while establishing a connection to SQL Server. The public data endpoint on this server is not accessible. To connect to this server, use the Private Endpoint from inside your virtual network. (.Net SqlClient Data Provider)
I always wanted a way to schedule commands within Azure SQL Database. Personally, for me, the go to standard is the functionality of SQL Server Agent. Obviously, this is not possible out of the box but I have been using an on-premises SQL Server instance (within a specific vnet that is mapped to the logical Azure SQL server) with a linked server connection setup (with dedicated logins) to Azure SQL Database to run some code at a specific time to scale up (and down) my database dependent on peak hours.
Having worked with Azure SQL Database and its many flavours for couple of years now I am confident in building deploying, whether manual or templates. Being in Azure you can take the same mind set to build non-Microsoft database tech such as PostgreSQL, MySQL etc.
Let’s work though one, it’s that easy.
For some reason I have friends / colleagues telling me that when scaling (up and down for this example) that no downtime occurs. Well, not only does Microsoft documentation say differently, I will show it. So let’s test it out. Before the practical test, this is the official stance. “There is a switch over period where connectivity is lost to the database for a short amount of time, which can be mitigated using retry logic”.
Wouldn’t it be nice if we could access one main dashboard / report that pulls in information from many tools such as security centre, SQL advisor and cost management tools? Well you can, thus giving you a focal point to implement best practices, called Azure Advisor. This is not specifically for Azure SQL Database, you can leverage this for most resources within Azure. Later in this blog post you see that I will use it as a focal point for my database infrastructure in Azure.
In the Azure portal under services look for “advisor”