If you read the Microsoft White Paper on SQL Server 2019, there’s a gem buried on page 17. It mentions a trace flag, which some of you might appreciate.
Here’s a little repro:
As you can see, the plee I made has actually been heard. I don’t know if they listened to me, or if the collective complaints over the year grew to the point that this got fixed.
In any case, thank you, Microsoft. This is a very nice enhancement.
Today I hosted a webinar with Gene Kim (@RealGeneKim) and we had a fantastic discussion. I was slightly star struck since I’ve been reading his work and quoting him for years in talks about Database DevOps. It feels like I got to work with someone really famous, and I’m hoping I didn’t appear too nervous on the webinar.
In any case, we had a great discussion, and I think you can still register to watch the recording. If not, we should have this on our Redgate YouTube page soon. We discussed the State of DevOps report, and specifically how the findings relate to databases. It was a good discussion, but when we talked testing, we both had some links to ways that we could build better software.
In my case, I referenced this talk, Practical Refactoring, which I think is great. A bit is the technical approach, but mostly I find the philosophy and freedom that comes with having tests in place to be invaluable.
This is based on a real project that these consultants worked on. The code is mocked, so don’t get caught up in the actual methods and structure, but think about how you could apply the ideas to your own work. How can you make the code better in a few minutes.
When Microsoft announced SQL Operations Studio last year, I wasn’t thrilled. The move to a VS Code shell was less of a concern to me than having a better SSMS toolset. Actually, with VS moving to MacOS, I was hoping we’d get a slimmer, closer VS version of SSMS that allowed add-ins, extensions, and plugins.
No such luck. We got SQL Operations Studio, which had the unfortunate acronym of SOS. To top it off, I felt this was more of a developer tool, but the name implies a DBA/sysadmin tool. To me this was a tool somewhat lost in its mission.
Now we have Azure Data Studio, the renamed SOS, which is interesting. I know there are more features than SOS and some additional work coming, but I can’t get too excited. Other than people that want native connections to a server from OSX and Linux and write T-SQL, is this that useful?
You can download it and see what you think.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I haven’t had the need to move an object from one schema to another in years. Really since SQL Server 2000. I wrote about deleting a user that owns a schema recently, but that’s often a first step. The next thing I might need to do is actually move objects from that schema to a new one.
I actually ran across this command when I was looking how to move the schema to a new user. There’s actually a parameter for ALTER SCHEMA that will move objects. This is the TRANSFER argument and it works like this.
I need a new schema for the object. In this case, I’ve got a table called SallyDev.Class. I want to move this to a new schema, and I’ll choose dbo for this example. I often have had developers build in their own schema and then I’ll transfer to the dbo schema, which is almost like a merge of code from one branch (SallyDev) to another (dbo).
The format of the command is: ALTER SCHEMA <newschema> TRANSFER <object>
The new schema name is just the name, with brackets if needed. Hint, if you need brackets, rename your schema, please.
The object is the qualified name of the object, with the old schema. In this case, the command I’ll use is:
ALTER SCHEMA dbo TRANSFER SallyDev.Class
Here’s my before look:
When I run the code, it works:
Now my object is moved. Success!
This is a quick view of a specific skill that can be handy. I won’t use this often, but if my team worked in this flow, or we had an issue, this not only shows how to resolve a single item move, but also helps me remember the command. I hadn’t seen this before, so a quick 10 minute blog is useful.
This also gives me ideas for other blogs, like how to automate this for a number of objects.
Voice of the DBA 