A Fine Slice Of SQL Server
Hiten Bhavsar works around the lack of SQL Agent:
As we know, it’s crucial that you run Database maintenance regularly in order to keep your database performance up with the latest statistics and healthy indexes, here we provide another way to schedule this job using Azure Data Factory; this can be done on a scheduled time interval weekly/bi-weekly/monthly.
Click through for the process and try not to think too hard about this secretly being maintenance plans all over again.
Comments closedDavid Alcock backs out of a problem:
This error isn’t to do with my login as such, it’s still there with sysadmin role membership so I don’t have to do anything too drastic like restarting SQL Server with the -m or -f startup parameters and recreate it. The error message is telling me that my logins default database cannot be opened, which is more than likely because I’ve deleted it.
Click through to see how David got out of this issue. This is a big part of why I highly prefer not to change the default database from master for logins..
Comments closedNiko Neugebauer has a pair of connectivity tests for us. First up is failover group connectivity:
When you set up a failover group between primary and secondary SQL Managed Instances in two different regions, each instance is isolated using an independent virtual network. Replication traffic needs to be allowed between these VNets.
To allow this kind of traffic, one of the prerequisites is:
– “You need to set up your Network Security Groups (NSG) such that ports 5022 and the range 11000-11999 are open inbound and outbound for connections from the subnet of the other managed instance. This is to allow replication traffic between the instances.”
Click through for a SQL Agent job script which helps with the test. Meanwhile, you can also test TCP connectivity from a managed instance:
In this post we shall focus on helping you determining the TCP connectivity from SQL Managed Instance against a given endpoint and port of your choice.
If you are interested in other posts on how-to discover different aspects of SQL MI – please visit the http://aka.ms/sqlmi-howto, which serves as a placeholder for the series.
There are scenarios where it would be nice to be able to test if a SQL Managed Instance can reach some “external” endpoints, like Azure Storage as an example.
Check out both posts.
Comments closedRonen Ariely has a step-by-step guide for us:
1. Open the portal
2. Open the cloud shell
Note: instead of steps 1 and 2 you can use PowerShell and connect your subscription
3. Create resource group named MyNewRS”located in east US
az group create –name MyNewRS –location eastus
Click through for the full set of instructions.
Comments closedReiss McSporran walks us through using Azure Backup to manage virtual machine backups:
Backups are configured for each VM individually, selecting their own retention policies and routines. They can however utilise the same storage and vaults.
Click through for a step-by-step guide on setting up a backup, using a backup to create a new VM, and replacing an existing VM with a backup.
Comments closedChad Callihan gives us a few high-level thoughts on page life expectancy and the buffer pool:
The buffer pool in SQL Server is an area in memory for caching data. Once data is read from disk, it can be kept in the buffer pool and SQL Server can check here for data when it needs to be found in the future. If requested data is not found in the buffer pool, a hard page fault can occur, meaning data needs retrieved from disk. It’s possible that data is found somewhere else still in memory which is called a soft page fault.
Click through for Chad’s thoughts on what a good page life expectancy looks like. My minor addition is that the number isn’t as important as the shape of the curve: if you have a fairly stable PLE above some arbitrary threshold (well above 300 seconds!), you’re probably in good shape. If your PLE sawtooths, your server’s RAM Pez dispenser needs refilled.
Comments closedGuy Glantser has a script to track buffer pool size by table:
Sometimes we need to troubleshoot memory pressure issues in SQL Server or in Azure SQL. One of the things that can help in these cases is to view the contents of the buffer pool.
I wrote a simple script that displays the contents of the buffer pool in terms of tables in the current database. For each table, it presents the total table space and the space consumed by the table in the buffer pool. The script is based mainly on the sys.dm_os_buffer_descriptors dynamic management view.
Click through for the script.
Comments closedThe Hadoop in Real World team shows how to move tables between databases in Hive:
Let’s see how to move a Hive table from one database to another in action.
We are currently inside a database named hirw. Let’s list the table under hirw.
We got one table named employee under database hirw. Let’s create a new database named hirw_2
Click through to see how.
Comments closedJeff Hill shows off another feature of dbatools:
If you’ve been responsible for an instance of SQL Server for any length of time you have probably dealt with
sp_configureto change configuration settings at the server level. I have been using SQL Server since v6.5 and it was a thing then too. This is not a post about what the settings are or what they should be set to. There are plenty of resources out there for both. This is about how to see and set these options.
Click through for the process and what you can do with it.
Comments closedChris Taylor puts on the lab coat and safety goggles:
tldr: Over the years I’ve read a lot of blog posts and watched a lot of videos where they mention that you should have your tempdb files all the same size. What I haven’t seen much of (if any) is what performance impact you actually see if they are not configured optimally. This blog post aims to address that
It is not too long, so do read.
Comments closed