Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here:
Cookie Policy
Every day, more IT organizations decide to move their SQL Server databases to Azure. In fact, over a million on-premises SQL Server databases have been moved to Azure. There’s an interesting blog about how Microsoft is faster and cheaper than its competitors that’s worth a read. To assist with your move to Azure Microsoft offers a number of migration tools and services to make this move as smooth as possible which I think attributes to their success. Two of those options are below with some informational links.
If you’re migrating a number of large SQL Server instances, Azure Database Migration Service is the best way to migrate databases to Azure at scale.
You can also automate the database migrations using the Azure Database Migration Service PowerShell commands.
Azure Database Migration Service (DMS) is a fully managed service for migrating multiple database sources to Azure data platforms at scale. It supports SQL Server 2005 through SQL Server 2019, as well as a variety of other source-target pairs. Azure DMS enables a seamless migration with minimal downtime or effort.
Let’s see how it works.
Working with Azure Database Migration Service
A typical database migration using Azure Database Migration Service consists of the following steps:
Perform a migration assessment using Data Migration Assistant (DMA). Note any migration blockers that DMA reports and perform recommended remediations.
If you need to assess the whole data estate and find the relative readiness of the databases migrating to Azure SQL, follow the steps provided here.
Once you find the Azure SQL target, use Azure Database Migration Service to find the optimal Azure SQL target SKU that meets your performance needs.
Create your target databases.
Create an instance of Azure Database Migration Service.
Create a migration project specifying the source databases, target databases, and the tables to migrate.
Start the full load. Note: Select Online mode to include transaction log backups.
Pick the subsequent validation.
Perform a manual cutover of your production environment to the new Azure database.
Offline vs. Online Migration
The amount of downtime required to move to the cloud is always an important aspect in the decision to move to the cloud or not. Azure Database Migration Service allows you to do either an offline or online migration. The difference is the amount of downtime. With an offline migration, downtime begins at the same time that the migration starts. With an online migration, downtime is limited to the time required to cut over to the new environment at the end of the migration.
Microsoft recommends testing an offline migration. If you can’t tolerate the downtime, then switch to an online migration. It’s important to note, an Online migration requires an instance from the Premium pricing tier. However, they make it easy for you by offering it free for the first six months.
Summary
Azure Database Migration Service gives you a much better idea of what to expect when migrating large database instances at scale. It migrates your databases with ease using Microsoft’s best practices. Even better is that Microsoft continually invests in migrations by working to improve reliability and performance, as well as adding source/target pairs. I can easily see why they continue to lead in cloud technologies.
In celebration of Women’s History Month starting next week, I was asked to write this blog about my experiences as a woman in the SQL Community, and it really got me thinking. At first, I thought I should be very politically correct and only talk about the great stuff and hype up all wonderful experiences I’ve had but then I thought that wouldn’t give a true picture. So, I am writing this as openly and honestly as I can. Here it goes.
The Ugly
I am going to start with the negatives to get those out of the way and draw attention to things that are still happening not only to me but to other women in this community. I don’t want this to be a gripe piece, since that’s not what I do. It is intended to be an honest accounting of my experience. Your experiences will vary, and I am not speaking for anyone else, but know I am not the only woman to have these experiences. I will address the bad and then move on to the fantastic things that this community has to offer for women and what I have been able to achieve because of the opportunities this community has afforded to me.
These are the things that suck about being a woman in the SQL Community that have had an effect on me. I am just going list them without detail, again I am just calling these out so others are aware these things happen.
Seeing Diversity and Inclusion Panels with no women
Having men assume I am there with my husband
Being hit on by random “community” guys in person, on twitter, on linked in, on my website
Being stalked by other male attendees
Being physically assaulted by male attendees (being touched without permission)
Someone assuming I was only selected to speak BECAUSE they needed women
Not being chosen for something BECAUSE I was a woman
Several Implying I was given Microsoft MVP award only BECAUSE I was a woman
Needing a male to repeat what I said so my ideas/solution etc. would be heard
Having a male think they need to speak for me
Having people not accept that men and women in the community can’t be good friends and nothing more
Some assuming I got my current job only because they needed a woman on staff, not because of merit
The Good
Thankfully, unlike other communities, the SQL Server community is very welcoming to women. The benefits of being a member of it FAR outweighs the negatives. This community has helped me achieve so much. I am grateful for all the community as afford me and I give back as much as I can because of it.
Here are some of the opportunities and experiences I’ve gotten, not just because I am a woman but because of being an active part of the SQL community.
Building a network of smart, strong, technical women that you can rely on for questions, support and feedback. Knowing they’ve been there done that too.
Getting a chance to sit on WIT panels regularly with topics dealing with gender issues or career advice
Running a user group
Being a Regional Mentor
Running a SQL Saturday
Being a speaker at conferences and SQL Saturdays
Ability to mentor younger women
Being amplified as an expert in my field (this is HUGE as a woman)
Being a role model as a successful woman to my daughters by being part of this community and giving back
Being an advocate for other women
Writing and being published blogger
Seeing more and more men attend WIT panels and speak up
Seeing women on panels because they deserve to be there, not because they needed a woman
Being ask directly to speak somewhere because of your knowledge, not because of my gender
Seeing the community come together and speak up when issues with regards to diversity and inclusion come to light.
Having a Board of Directors that takes action when there is a violation to the Anti-Harassment policy
Being turned to for advice on policies
Becoming a Microsoft MVP because of the work I do in this community
The feeling you get after speaking when someone says they learned something
The unwavering support you get from the SQL Family when times are tough
The knowledge there are other women in this community that have been there, done that, and you are not alone.
The “you got this” push you get from the SQL Family that pushes you to venture out of your comfort zone
I could all the great ways this community has shaped my life and my career. I even got approached for my last job in part just by my involvement in this community. There is so much to be gained by being a part of it regardless of your gender. We all know there are so many challenges with being a woman in tech. We talk about these all the time. What makes the SQL Community different for me is that we UNDERSTAND that, and we work to continually improve upon it. Having been part of this community for almost a decade, I’ve seen so much change in this for the better. As Rie and I always say, we are grateful for those who have run the gauntlet before us. We are standing on the shoulders of those women who came before us and fought the good fight. We are blessed to continue to do it for the others that will follow. Thank you, SQL Community, for helping me achieve what I hope will help other women as they rise.
I am very excited and fortunate to be chosen to speak at both SQLBits and DataGrillen this year. These two conferences are incredible community run events and if you can attend either of them, I would highly recommend it. Here’s a little bit about each event and information on how you can register. If you can attend be sure to check out my sessions.
SQLBits
SQLBits the largest SQL Server conference in Europe, being held in London England March 31st– April 4th. It is a conference for leading data professionals with over 200 sessions from speakers all over the world.
One of the biggest challenges to successful implementation of data encryption has been the back and forth between the application and the database. You have to overcome the obstacle of the application decrypting the data it needs. Microsoft tried to simplify this process when it introduced Always Encrypted (AE) into SQL Server 2016 and Azure SQL Database. In this demo intense session, you will learn about what Always Encrypted is, how it works, and the implications for your environment. By the end you will know how to now easily encrypt columns of data and just as importantly how to unencrypt. You will also learn about the current limitations of the feature and what your options are to work around them.
DataGrillen is a SOLD OUT 100% free training event with the biggest and brightest of the data platform world. Held in Lingen Germany each year it is the must attend event in the region.
Join Me – Thursday May 28th 16:30 Room Handschuh
Mastering Tempdb
Have you experienced performance problems caused by contention in TempDB? Have you ever wondered why your TempDB is suddenly 3 TB? In this session, you will learn about all the various components of SQL Server that use TempDB. Whether it be AlwaysOn Availability Groups, Read Committed Snapshot version stores, spills, or simply temporary tables, learn about how to identify what SQL Server or your applications are doing in TempDB. Once you understand all the ways SQL Server uses this critical resource, and how to proper configure it, you’ll be better prepared for your workloads whether it be an Azure VM, a physical server, or a container.
Be sure to check out my fellow Denny Cherry and Associates sessions at both events. Click the links for the full schedules.
Did you know that a native STRING_SPLIT function built into SQL Server was added into SQL Server 2016? As a consultant I see so much code that call out to a scalar function that are used to split out string delimited variables into a usable list. For those that use this method I suggest you look at this function. STRING_SPLIT is a table valued function that returns a single column of your string values split out by the delimiter. This is an unusual bit of T-SQL, in that compatibility level 130 or higher is required for its use (Microsoft didn’t want to induce breaking changes into existing user code). Using this method is far more efficient and can be executed without calling a scalar function.
The Syntax
STRING_SPLIT ( string , separator)
How to use it
SELECT value AS 'Flavor' FROM STRING_SPLIT('Chocolate,Vanilla,Strawberry', ',');
Results
The Plan
Here is what the plan looks like. It’s very straight forward and simple.
Custom Function
Now here is a home-grown version you may find in some environments. You can see its much less efficient.
/****** Object: UserDefinedFunction [dbo].[fnSplit] Script Date: 2/11/2020 6:26:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnSplit](
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
The Results
Results are the same. But note your field name will be the same every time, with the new function you get a little more flexibility in this.
The Plan
It’s a little more complicated then the straight forward one we saw above including a sequence.
There are many different ways to write a split function to make things work, however, now that SQL Server has given us one, I highly encourage you to take a look at it. When performance tuning be sure to take a look at what you’ve always done in your code and look for ways to improve it such as this. You can learn more and see more examples here on docs.microsoft.com.
Register for our webcast featuring Kevin Kline and Denny Cherry where they discuss keeping data and applications available even during production outages.
Watch our webcast which was broadcast on April 1st, or April 2nd (depending on which part of the world you are in) as Kevin Kline and Denny Cherry talk about the Top 5 Considerations When Moving Databases to Azure.
As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.
And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.
