A Fine Slice Of SQL Server
Gilbert Quevauvilliers fixes an issue:
How to stop/fix Mirroring to a database in Microsoft Fabric
I recently had a challenge where the mirroring in Fabric stopped working because there was no activity.
For me to start this again when I tried to connect to a table it said the database was already being used to mirror as shown below.
Read on for a quick fix that Gilbert found.
Comments closedYohei Kawajiri describes three techniques for performing a SQL Server migration:
It is possible to configure a SQL Server Always On availability group with a primary replica running on SQL Server 2017 and a secondary replica running on SQL Server 2022, but there are important considerations and limitations to keep in mind:
- Backward Compatibility: SQL Server supports having replicas on different versions, but the primary replica must be on an older version than or equal to the secondary replicas. Therefore, having SQL Server 2017 as the primary and SQL Server 2022 as the secondary is valid.
- Database Upgrade Path: When you decide to upgrade the primary replica to a newer version, you need to follow a specific upgrade path to ensure minimal downtime and data integrity.
Building an availability group? Yeah, makes a lot of sense. Performing log shipping? Sure, I could see that working. Database mirroring? I did not expect to read that one, mostly because it’s been deprecated for a decade.
Comments closedReza Rad hogs the photocopier:
Microsoft Fabric offers an end-to-end SaaS analytics solution; however, the world is using all kinds of data sources in its implementation. Mirroring is a new functionality in Fabric that allows customers to keep their data wherever they are, but then they can use Fabric analytics solutions with the same speed and performance as if their data were in Fabric. Best of all, this won’t cost extra. If you wonder what it is and how it works, read this article.
Click through for the video and article.
Comments closedDennes Torres holds up a mirror:
You need to read data from production to build a single source of truth. If you create pipelines reading directly from production, you will create additional load over the production environment. The mirror allows you to do much of the production reporting from the mirror, leaving the production environment to serve other users. Keep in mind, production report, but not analytics report.
Mirroring a production database to Fabric is one method to ensure the load over production will be as low as possible and the data will be transferred fabric to complete the transformations from this point.
Only this? What about avoiding pipeline creation? Not really, you still need to create pipelines, as I will explain ahead.
Click through for the demo and explanation. This is an important thing for people to note: mirroring doesn’t eliminate ELT. You still have the data lake process to work through, as your transactional system does not and should not look like your reporting system.
Comments closedKevin Chant gives it the ol’ college try:
In this post I cover some initial testing of Mirroring Azure Cosmos DB Databases in Microsoft Fabric that I performed.
I wanted to do this post for various reasons. Including the fact that it was announced during the Microsoft Fabric Community Conference that Mirroring is now in Public Preview.
Which means that you can now mirror data from Azure SQL Database, Azure Cosmos DB and Snowflake into your own Microsoft Fabric tenant. Even trial tenants.
Kevin takes us through the process and gives it a try, sharing with us the results of some testing, including a test insertion of 100 million rows.
Comments closedFirst things first. Before I show you how to leverage this feature in Microsoft Fabric, let’s first explain the feature itself.
But, before we explain the feature itself, we need to go one step back and examine the key logic behind the Microsoft Fabric workloads, so that you understand the full context of the Mirroring importance.
Take that context and then you get an idea of how mirroring becomes so important for the Microsoft Fabric experience.
Comments closedGilbert Quevuavilliers holds up a mirror:
Creating a Mirrored Azure SQL Database in Fabric
This week they announced Announcing the Public Preview of Database Mirroring in Microsoft Fabric | Microsoft Power BI Blog | Microsoft Power BI
I decided to see how easy it was to create a mirrored database in Fabric and below are my findings (PS it is AMAZING)
Click through for the demo. Though it does look like Gilbert has mirrored the contents of the blog post a few times as well, at least as of the time of my post here.
Comments closedBob Pusateri points out something important about database mirroring:
A few weeks ago I had a reminder of one of the finer points of the requirements for mirroring: mirrored servers need to be running not only the same version of SQL Server, but the same edition as well.
I hadn’t thought of this in a while, but it makes sense. Asynchronous Database Mirroring (also known as “High-Performance Mode”) is only available in Enterprise Edition, while Standard Edition only supports “High Safety Mode”, which is synchronous. If the primary server in a mirroring topology was Enterprise Edition, but the mirror server was Standard Edition, how would that work?
Also check Glenn Berry’s comment for a trick which stretches mirroring’s capabilities for upgrades.
Comments closedThere is an awesome set of PowerShell cmdlets out there written by MVP Chrissy LeMaire. This method is my personal choice. It works great and is easy to automate. You can run it with SQLAgent or you can just use Scheduled Tasks in the OS. The scheduled tasks method is a little cleaner, but you don’t get to see it in SQL Server. Also if you are on a cluster and running Windows 2012 you can cluster the task scheduler as an added benefit.
Chrissy wrote this with the intent of making migrations easier, and she succeeded. In fact, I made it a point to thank her at MVP Summit last year because it made my life insanely easier. The advantage here is that you can automate a lot more than than just logins. In fact you can migrate and automate pretty much anything at the server level. Here is the link that I guarantee you are going to bookmark followed by a video demo where I show how to install and automate the syncing of logins using both the SQLAgent method and the Scheduled Tasks method.
DBATools would be my preference in this situation as well, but click through to see four other methods, as well as code.
Comments closedRobert Davis explains that log shipping can be better than mirroring for database migrations:
This topic has come up several times recently, so I feel the need to blog on it. As the person who wrote the book on Database Mirroring, it will probably come as a surprise to many of you that I believe that log shipping is a much better tool for database migrations than database mirroring.
I’m not just talking about the fact that database mirroring is deprecated (since SQL Server 2012) and log shipping is not. Both are still in SQL Server to this day. Because database mirroring is deprecated, it is no longer receiving bug fixes (except maybe critical security bugs) and no work is being done to make sure that it works with new features in current and future versions. Log shipping is still receiving both of these things. I will lay out the real reasons below.
Robert makes two compelling arguments in favor of log shipping.
Comments closed