Press "Enter" to skip to content

Day: January 12, 2023

Saving Time in Power BI

Allison Kennedy shares a few tips:

I myself still refer back to that blog post whenever I start a new project where there isn’t a date table already in the data source. Below are a few of my other favorite resources that I visited most frequently in 2022.

Click through for the list of posts and the helpful tips they contain therein.

Comments closed

Replication from 2000 to 2012

Deepthi Gogrui pulls a fast one:

The scenario that I faced was little challenging. We had SQL Server 2012 production server replicating data to a Server 2000 which is used for reporting purposes. Subscriber SQL Server 2000 used by the reporting team were not ready to upgrade the Server as they need to rewrite their entire application as it was using vb6 code. They need a strategy where the data can still be replicated without upgrading the Server.

As I researched, I found that it is not compatible version but planned to test the replication to see if somehow it works. I tested the replication between SQL Server 2012 as a publisher and SQL Server 2000 as subscriber. I was able to setup the transactional replication between the servers for the database but found during the initial initialization snapshot, the ANSI_PADDING setting in the snapshot generated .sch files caused the issue while the distribution job runs. 

Read on for the solution. This turned out to work despite Microsoft’s official guidance that they only support replication between SQL Server instances within two versions of each other.

Comments closed

Defending Less-than-Ideal Practices

Deborah Melkin has a confession and a defense:

I feel like this is where I should say something like, “Hi, my name is Deborah and I’ve used nolock in production.” I would also have to confess to doing things like using correlated sub queries, not using a foreign key, implemented complicated triggers, etc. I often talk about how the first real SQL script I wrote had cursors running over temp tables in SQL Server 6.5, which I’m fairly certain was one of the first thing I read you were NOT supposed to do. And oh, hello there, denomalized table and dynamic SQL! I’m sure I’ve done more things than this too. These are just the ones I can remember doing, or at least I’m willing to admit in public.

With some of these, the answer is “that’s the best alternative I had at the time.” With correlated sub-queries, I wouldn’t even consider that a bad thing. Granted, I personally prefer a combination of common table expressions and the APPLY operator but that’s usually not for performance reasons.

Comments closed

Backing up SQL Server with Multiple Solutions

Chad Callihan tells us a story:

Many years ago, a friend was using a backup/recovery tool for managing their backups. This particular tool on its own wasn’t necessarily bad. But in this case, it didn’t work well. In fact, it barely worked at all. Backups were slow to complete and restores were even slower. Attempting to restore even one database could take 10-15 minutes just to navigate a GUI and start the restore process.

It…was…very…slow…

Read on for the rest of the story. Most of the time, when I see two products used for backups, I typically see a bunch of redundant backups, with both products taking full backups.

Comments closed

Start and Stop for Managed Instances

Arun Sirpal pushes the big red button:

We all would like to save money when operating in the cloud, Microsoft has released a stop / start concept for SQL Managed Instances – preview mode!

At the time of writing, the Managed Instance needs to be built from the November wave where you will see the functionality in the overview section and it has to be in the General purpose tier. If you have managed links or failover groups then you cant use this feature.

There is a kicker, however, which makes this a less-than-pleasant option. Arun has more detail.

Comments closed

Moving Azure SQL MI between Subscriptions

Sabrin Alsahsah migrates a SQL managed instance:

We have recently received a few service requests from our customers to know how to move an Azure SQL-managed instance between subscriptions. In this blog article, we will be discussing supportability, methods, and solutions that can achieve this goal, and you can decide the best for you depending on your business requirements and environment setup.

Currently, at this time there is no direct way to move a managed instance itself, but we can have a workaround by moving the Azure SQL managed databases across Azure SQL managed instances that are hosted\located in different subscriptions.

Click through for three workaround options.

Comments closed