A while ago, I wrote an article Automated Script-generation with Powershell and SMO about using SMO to script out a SQL Server database. It has remained surprisingly but agreeably popular. SMO is still there, but now part of the sqlserver module that is included with SSMS and downloadable. Someone recently asked me whether it was … Read more
Each year, Redgate runs a survey to ask the folks at companies, from DBAs and Devs to CIOs, about how they monitor SQL Server. Those insights help us understand how we can make SQL Monitor more valuable to DBAs – or anyone responsible for the “care and feeding” of a SQL Server. The Estate Management … Read more
This is my first blog in a while and I’m sorry it’s not a technical one. You may be aware but I’m one of the SQLBits organisers which means that for the last few months all my spare time has been devoted to SQLBits organisation. We were really pleased with how the event went and … Read more
I am in the middle of a project to enable our corporate databases to work with continuous integration using RedGate SQL Automation (and hopefully get a few blogs/articles out of it too.) One of the things that the tool does is create your database in multiple ways, with multiple names, in order to compare to … Read more
Today’s blog is one of those topics that I just needed to get written down somewhere, and I expect that a few readers may find this interesting and want to do the same thing. In many of the Visual Studio projects we have where I work, there are Powershell and/or batch CMD files that we … Read more
SQL Server 2019 has some new and very interesting functions to identify information about pages. Many operations, such as analyzing current activities, locks or deadlocks, may result in some metadata pointing/blaming one page in the database. However, how to translate this information to get a database and object name? The image below is an example of … Read more
You are never too old to learn an important lesson. Like most programmers, I read programming language documentation for one of two reasons. 1. You have just found out about something new 2. You find out that you were wrong about something you thought you had read the first time, probably 10 years ago. Today’s … Read more
Wow, it has been a while since I last posted, with the holidays, and way too many other projects that I haven’t blogged about just yet because I haven’t found the time. Last June, the SQL Saturday in Chattanooga and SQL Server User Group lost their venue, as well as the leader of the group. … Read more
This article is about using the DOS Batch script facility of the Windows command line, together with SQLCMD to write the contents of each table in a database to the local filesystem. It shows how to use temporary stored procedures to advantage. Just to make it a bit harder, I’m doing it in extended JSON … Read more
SSMS new versions are full of very interesting features. With SQL Server 2019 arriving, it’s normal that our focus is captured by the new features in the database engine, however, SSMS has also a lot to offer. Let’s highlight some very interesting features this tool has to offer on its new versions. Vulnerability Assessment Sometimes … Read more
I’ve often read in forums how people have special utility databases with all their stored procedures and functions for working on the databases on the server. It is great because you don’t want your utilities intruding into the actual databases that you are developing or testing. The problem is that it doesn’t work. Let me … Read more
A few weeks ago I faced this problem: One query on my application was (fortunately in the development environment) was facing a very bad execution time. Since the query was generated by entity framework, I used SQL Profiler to capture the query with all its parameters and execute in SSMS. The query was created using … Read more
SQL naming conventions for tables, and all the associated objects such as indexes, constraints, keys and triggers, are important for teamwork. Poorly-named tables and other objects make it difficult to maintain databases. Table names must follow the rules for SQL Server identifiers, and be less than 128 characters. It is possible to force SQL Server to … Read more
Happy New Year everyone! Hope the holiday season’s passing finds you well rested, well fed, and ready to hit the ground running. As year’s go, it was a relatively great year last year… as far as the SQL community goes in any case. But now that 2019 has started, it is time to get back … Read more
So you have a database with JSON in it. Can you validate it? I don’t mean just to ensure that it is valid JSON, but ensure that the JSON contains values that are legitimate. Are NI values, postcodes or bank codes valid? Can the dates or GUIDs be successfully parsed? Are those integers really integers? … Read more
Note: post revised to take into consideration tips from comments The other day, I was working with a query where someone had put together an expression that had something along the lines of: And I thought to myself… “Self, what will the datatype of this be? Will it fit what the programmer wanted it to … Read more
When I read the list of new features in SQL Server 2019 I became very proud of my crystal ball powers. In July 2017 I published an article about Graph Database feature in SQL Server 2017. In this article, besides showing the improvements and benefits I also highlighted one problem: the lack of graph edge … Read more
Note: This is an update of a blog I posted in 2006 with a lot of additional information (I was less wordy in my 30s apparently). It is just as relevant today. I have a presentation I do occasionally on concurrency entitled “Let Me Finish” that covers many of the different types of concurrency behaviors … Read more
JSON was initially designed for the informal transfer of data that has no schema. It has no concept of a table, or of an array of identical arrays. This means that it must tell you each key for each object, even if the original data object was a table. With the happy minimum of key-value … Read more
I’m a lifelong learner. One of the ways I love to learn is by reading books. While I was making the transition to software development from my original career, I read the book “Code Complete” by Steve McConnell. This was back in the mid-90s before the Agile Manifesto was written or DevOps was a thing. … Read more
Simple Talk Bloggers
Subscribe for more articles
Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.
Compliant Database DevOps
