While this industry is full of self-taught individuals who have spent time learning new technologies and tools, it’s somewhat amazing that training continues to be an issue. Many organizations limit their training budgets and time allocated to employees while continuing to expand the number of technologies and platforms they use. This is one reason why I think changing database platforms to avoid licensing costs is unlikely to save you money anytime soon. Retraining staff and developing competence takes time. Perhaps this is also one reason why many companies look to the cloud, thinking that they can reduce the amount of upskilling needed by their staff if the cloud vendor manages the systems.
That might be true, but there is a lot of knowledge is still needed about how the underlying technologies work, especially if employees are to efficiently write code or make configuration changes in the system. The cloud can be more cost-effective, but it can easily be less cost-effective. Knowledgeable employees can make a difference.
I do see plenty of organizations subscribe to online learning, but often I find these courses good for basics, but not necessarily helpful for specific situations and just-in-time knowledge requirements for projects. There more custom training, or at least, group support, is needed to ensure that your staff is learning efficiently. Far too many people can’t handle open-ended training from an online platform and complete more than one course.
Training isn’t enough, as there also needs to be some practical application of new skills in your environment. Practicing katas as a group, coaching each other, and applying skills to actual projects while measuring results is important. It takes some commitment from both employees and organizations to get better. Far too often, I also see staff unmotivated to learn and grow, for a variety of reasons, but that’s a different problem that we have to work to solve together.
The world of technology in business is growing increasingly complex. We find more and more organizations adding technologies, especially new database platforms, and we lack skilled people to work with them. That’s a constant challenge, but one that creates opportunities for you if you find ways to improve yourself.
I had a customer question whether Flyway Desktop (FWD) would cause problems if developers were adding columns into the middle of tables. It’s a valid concern, and this post shows that FWD doesn’t cause you issues, even if your developers do silly things.
Unless they want to do silly things.
I’ve been working with Flyway Desktop for work more and more as we transition from older SSMS plugins to the standalone tool. This series looks at some tips I’ve gotten along the way.
The Scenario
Imagine that you have a table with a few columns, like this one.
CREATE TABLE Product
( ProductID INT NOT NULL CONSTRAINT ProductPK PRIMARY KEY
, ProductName VARCHAR(50)
, ProductDesc VARCHAR(1000)
, ProductSize CHAR(1)
, ProductWeight INT
, ProductColor VARCHAR(20)
, StatusID int
)
GO
This table has the same structure in dev and prod, and I need to add a new column. We need a quantity per package as we have new products where there are multiple items in a box, so there is a need to add ProductQtyPerUnit to the table.
I decide that this needs to be before StatusID since it’s related to the other product description items, and I want them to be together. This is a good concept when designing entities, but it’s not worth doing when we have millions of rows in this table in production.
In the SSMS designer, I do this. I right click my table, click Design, the right click before StatusID and select Insert Column:
I then design my new column. Things look good.
Most developers would just save this change. However, if I were to click the Generate Change Script button, I’d see this (I leave out the SET stuff at the top).
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Product
(
ProductID int NOT NULL,
ProductName varchar(50) NULL,
ProductDesc varchar(1000) NULL,
ProductSize char(1) NULL,
ProductWeight int NULL,
ProductColor varchar(20) NULL,
ProductQtyPerUnit smallint NULL,
StatusID int NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Product SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.Product)
EXEC('INSERT INTO dbo.Tmp_Product (ProductID, ProductName, ProductDesc, ProductSize, ProductWeight, ProductColor, StatusID)
SELECT ProductID, ProductName, ProductDesc, ProductSize, ProductWeight, ProductColor, StatusID FROM dbo.Product WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Product
GO
EXECUTE sp_rename N'dbo.Tmp_Product', N'Product', 'OBJECT'
GO
ALTER TABLE dbo.Product ADD CONSTRAINT
ProductPK PRIMARY KEY CLUSTERED
(
ProductID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
This script essentially creates a new table, copies over data, then drops the old table before a rename. On a large table, this could acquire a number of locks and potentially cause errors or disruptions for clients. If I want deployments at any time, without causing downtime, this isn’t the script I want to run.
Flyway and Column Changes
If I do this in dev, assuming I don’t have millions of rows of data, I might not notice this. What about detecting this change in Flyway? Let’s see.
I have a Flyway project open in Flyway desktop and I’ll refresh the changes. As you can see, we detect this new column. As you can see, we detect the change, showing the insertion of the column into the middle of the table.
I can save this and then generate a migration script for this change. When I do this, I see this script. Notice that this script is unlike the SSMS script and just adds a column to the table.
This is the same behavior in SQL Compare. By default, we don’t want to rebuild tables and move data. We want to just add the new change to the system.
This is controlled by the Force Column Order option, which is off by default. We can see this when I look at the comparison options for the project.
I can check this and then re-generate the migration script. When I do that, we see this script. This one
The entire script is here:
PRINT N'Dropping constraints from [dbo].[Product]'
GO
ALTER TABLE [dbo].[Product] DROP CONSTRAINT [ProductPK]
GO
PRINT N'Rebuilding [dbo].[Product]'
GO
CREATE TABLE [dbo].[RG_Recovery_1_Product]
(
[ProductID] [int] NOT NULL,
[ProductName] [varchar] (50) NULL,
[ProductDesc] [varchar] (1000) NULL,
[ProductSize] [char] (1) NULL,
[ProductWeight] [int] NULL,
[ProductColor] [varchar] (20) NULL,
[ProductQtyPerUnit] [smallint] NULL,
[StatusID] [int] NULL
)
GO
INSERT INTO [dbo].[RG_Recovery_1_Product]([ProductID], [ProductName], [ProductDesc], [ProductSize], [ProductWeight], [ProductColor], [StatusID]) SELECT [ProductID], [ProductName], [ProductDesc], [ProductSize], [ProductWeight], [ProductColor], [StatusID] FROM [dbo].[Product]
GO
DROP TABLE [dbo].[Product]
GO
EXEC sp_rename N'[dbo].[RG_Recovery_1_Product]', N'Product', N'OBJECT'
GO
PRINT N'Creating primary key [ProductPK] on [dbo].[Product]'
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [ProductPK] PRIMARY KEY CLUSTERED ([ProductID])
GO
By default, Flyway isn’t going to try and rebuild your tables if developers add columns into the middle of a table. This is the recommended and preferred way of dealing with these changes. If your developers complain, then discuss the fact that we don’t need to worry about the physical order of columns in a table. If you want columns returned in a different order, do that in a query (and don’t use SELECT *).
If you really need tables rebuilt, you can check the option, but you shouldn’t do that.
Try Flyway Enterprise out today. If you haven’t worked with Flyway Desktop, download it today. There is a free version that organizes migrations and paid versions with many more features.
If you use Flyway Community, download Flyway Desktop and get a GUI for your migration scripts.
malotype– n. a certain person who embodies all the things you like the least about yourself – a seeming caricature of your worst tendencies – which leave you feeling repulsed and fascinated in equal measure, having stumbled on a role model of exactly the kind of person you never want to be.
One of my hobbies is coaching volleyball for kids, teenagers really. This year I’ve taken on 13 year olds, and helping out with an 18 year old team as their coach is out for a few weeks. It’s been an interesting dichotomy, and I’ve now helped coach all ages from 13-18.
In the eight years I’ve been coaching, I’ve worked with, and watched a number of coaches. In that time, I’ve seen a few that are polar opposites of who I try to be. One in particular that was very good at understanding the game, knew all the rules, and recognized things to exploit in opponents.
However, this person was not good with players, in my opinion. Harsh, unapproachable, didn’t listen to concerns, didn’t provide support, belittled (my opinion) players, and demanded too much.
I can be loud, and I’ve been short with players at times. I try not to do that often, and I try hard to support them to help them grow, accepting their fallibilities while pushing them to grow and change. A number of players have said they would never want to be near that coach again and one quit a time.
The title of our keynote session at the Redgate Summit in Atlanta is Navigating the Database Landscape, and I’ll be delivering part of the talk, along with Grant Fritchey and Kathi Kellenberger today, Mar 13. This is based on the State of Database Landscape Survey results, as well as our experience working with customers and implementing DevOps solutions over the last decade. The talk was mostly written by others, but as I rehearsed the session, I found myself wondering about how I’d approach my job if we returned to being a DBA or developer.
When working in technology today, there are many challenges outside of actually learning about any of the particular products, languages, platforms, etc. We have the politics of working with others, ongoing work, emergency requests outside of channels, random questions asked by others, code reviews, and probably a few other things I’m forgetting, all outside of learning any new skills. While I consider myself a lifelong learner, I know that finding time (and energy) to acquire the basics of any new technology is challenging.
At the same time, while working in any size estate, it seems that someone always wants to add a new tool, platform, language, service, or database to the environment. It’s great we have choices, but it seems like sometimes every technologist wants to just use something new rather than work within the areas we have experience. Early in my career, it was rare to find more than 1 or 2 database platforms in a company. Now we have lots, often seemingly just added because one person watched a talk or video and thinks it would solve all our problems in this particular instance.
Working in an enterprise of any size likely means there are multiple database platforms in use. While you might only be in charge of 1 or 2 today, who knows when someone will call you as the “database expert” and expect you to configure Redis or troubleshoot ElasticSearch. I’ve had friends in this position, and I’ve had people come ask me to figure out MySQL, DB2, BTrieve, Lucerne, and more in my career. It’s a challenge, and it’s also stressful because I want to do a good job, even if I don’t know what I’m doing. Fortunately Internet search, contacts among friends, and more have helped me usually solve an issue.
The modern database landscape is likely to be more complex than ever, and with the advent of cloud services, we find there are lots of options that anyone in an organization might choose to use in production, and then ask you to support them. Our jobs are increasingly complex, both from the depth of things we need to know about a database to the breadth of different products and services that might enter our realm of responsibility. Navigating all this is a challenge, but if you rise to the occasion, there can be a lot of rewards.
Voice of the DBA 