A Fine Slice Of SQL Server
The Big Data in Real World team needs an alias:
An alias as the name suggests is an alias or another name to the index in Elasticsearch. It is quite useful when you want to refer to an index by another name. So instead of performing an reindex to rename or cloning an index you can create an alias to the index.
Click through for the script to create an alias, how you might use one, and the right way to delete one without removing the underlying article.
Comments closedMigrating databases to a new database server is a big job. You need to have the right tools to make this process easy for everyone. Cosmos DB had a Data Migration Tool to move data from a bunch of sources. It was a great, free tool with an easy user interface. You didn’t have to be a developer to use this tool. Here is a screenshot of what it used to look like.
Unfortunately, the tool got old and did not get updated with upcoming SDK changes. It worked only in Windows environments too.
Click through for Hasan’s thoughts. I had a behind-the-scenes look at everything getting put together, in that I was supposed to help, got busy, and slinked away as really sharp people like Carey Payette and John Bowen drove the project to completion.
Comments closedShane O’Neill takes this T-SQL Tuesday to heart:
I got swept up in that wave for a while in the same way I got swept up in the other trends of my time, like Pokemon, Sodoku, and Angry Birds.
Eventually, I stopped when I found a PowerShell script by Kieran Walsh ( github | twitter ) where you could narrow down to the correct answer by putting in the results of your guess each round.
This hack led to me realising how much time I was spending on Wordle and that I should stop, much like I did with Pokemon, Sodoku, and Angry Birds.
So, what better thing to do than to try and recreate that PowerShell script in T-SQL
And given Shane’s time restriction, I’d say the end result is a good one.
Comments closedOlivier Van Steenlandt hits the big red button:
I developed my package in Visual Studio 2019 and deployed my changes to my SSIS Catalog (SSISDB in my case). As soon as my SSIS Package was deployed I started the SSIS Package execution.
After a couple of minutes, I realised I made a mistake. I didn’t want to lose too much time, so I looked for a way to stop the SSIS Package Execution.
Since I was not executing the package from a SQL Server Agent Job, I couldn’t just stop the job. So I needed to find another way.
Click through for a way to do this via SQL Server Management Studio.
Comments closedSandeep Arora has a checklist for us:
We have broken our Snowflake Migration Checklist into nine phases to help plan and execute an end-to-end migration of the existing traditional data platform to Snowflake. These phases will help align migration resources and efforts; however, this doesn’t necessarily mean that all steps should be executed sequentially. Some phases, like “Train Users,” can be executed parallel to other phases.
At a high level, the process isn’t Snowflake-specific—really, 6 of the 9 steps are generic supporting steps which would apply to any major project. This makes the checklist not only a good starting point for a Snowflake migration, but also any major migration project.
Comments closedJohn McCormack tries out some stuff:
I don’t often go down code based rabbit holes, its just not what I do day to day but a while back, someone asked on twitter’s #sqlhelp hashtag if there was a character length limit to the STUFF function. The documentation didn’t have an answer. For that reason only, I decided to take a look.
Click through to see what John learned along the way.
Comments closedFelipe Binotto helps us save a bit of money in Azure:
Cost governance is an essential aspect of managing any cloud infrastructure. Azure Policy is a powerful tool that can help implement cost governance measures within your Azure environment. With Azure Policy, you can define and enforce rules to control costs, monitor usage, and optimize your resources.
These policies can be used to prevent the creation of resources that are not compliant with cost-saving measures or to apply tags to resources that identify them as cost-related resources. You can also use policies to track resource usage and generate alerts when certain thresholds are reached, allowing you to take proactive measures to optimize your resources and control costs.
Throughout this article I will provide some examples of Azure Policies you can use for cost optimization.
There’s some solid advice in here. Most of it boils down to knowing what you have running so things don’t slip between the cracks.
Comments closedReza Rad continues a series on data security in Power BI with two posts. The first covers row-level security:
There are multiple levels in which you can secure the data in a Power BI Dataset. Row Level Security is about applying security on a data row level. For example, sales managers in the united states should only see data for the United States, not Europe. The sales Manager in Europe won’t be able to see sales in Australia or the United States. And someone from the board of directors can see everything. The reason was that Row Level Security wasn’t part of the Power BI model. Now in the new version of Power BI Desktop, the security configuration is part of the model and will be deployed with the model.
The second looks at dynamic row-level security:
The most important question is, why dynamic row-level security? To answer this question, you must consider the limitation of static row-level security. Static row-level security is simple to implement. However, if you have thousands of roles, then it would be a nightmare to maintain. For example, if you want to create a payroll Power BI report in a company with ten thousand users, you want every user to have his/her role. Dynamic row-level security is the answer for such scenarios.
Check out both posts for more info.
Comments closedRobert Cain continues a series on book archival:
This is part of my ongoing series on my ArcaneBooks project. The goal is to provide a module to retrieve book data via provided web APIs. In the SEE ALSO section later in this post I’ll provide links to previous posts which cover the background of the project, as well as how to use the OpenLibrary APIs to get data based on the ISBN.
In this post I will provide an overview of using the Library of Congress API to get data based on the LCCN, short for Library of Congress Control Number.
This has been an interesting series to watch, as it’s a practical application of non-work use of a series of practical development skills.
Comments closedDaniel Hutmacher uses a temp disk for a temp database:
Almost all Azure virtual machine sizes come with a temporary disk. The temporary disk is a locally attached SSD drive that comes with a couple of desirable features if you’re installing a SQL Server on your VM:
- Because it is locally attached, it has lower latency than regular disks.
- IO and storage are not billed like regular storage.
As the name implies, the temporary disk is not persistent, meaning that it will be wiped if you shut down your VM or if the VM moves to another VM host (as part of maintenance or troubleshooting). For that reason, we never want to put anything on the temporary disk that we need to keep.
I’d say this was a lot more popular several years ago, back when spinning disk was the default for Azure storage. There can still be benefits from doing this, though if you’re using Premium storage with high IOPS, the biggest remaining benefit is around latency.
Comments closed