Running a Command Line SQL Compare Comparison

Posted on November 8, 2023 by way0utwest

Recently a customer was looking to automate some of their SQL Compare checks, but they wanted to do this in a dynamic way, since they needed to do this at scale. Their idea was to not have a SQL Compare project, but build a mapping at the command line.

This post addresses a part of their issue. This will look at how to set up a basic SQL Compare command line.

This is a part of a series of posts on SQL Compare on my blog. You can read other posts I’ve written by clicking the link.

Adding Parameters to SQL Compare.

To build up a comparison from the CLI, let’s start with adding parameters slowly. First, let’s just connect. To do that, I’ll run SQLCompare.exe with a couple parameters. We need server and database parameters.

The command line I’ll use will connect to my local machine (Aristotle) and then two databases: dlm_1_dev and dlm_2_integration. The code I’ll run is:

sqlcompare /Server1:"Aristotle" /db1:dlm_1_dev /Server2:"Aristotle" /db2:dlm_2_integration

This uses the /Server1 and /Server2 parameters, which can be shortcutted to s1 and s2. The structure is a colon and the value. For the database, we can use database1 or db1, and database2 or db2. I’ve shown both the long and short items above.

When I run this, I see results, which scroll off the screen for these two items:

2023-10-19 13_39_43-cmd

If I look at a couple named instances, then I need to add in the instance name inside the quotes with a \ that will designate the appropriate instance. This is my command for named instances:

sqlcompare /Server1:"Aristotle\SQL2022" /db1:compare1 /Server2:"Aristotle\SQL2017" /db2:compare2

As you can see below, this gives me a few differences, with the indicators on the right about in which databases the changes are located.

2023-10-19 14_23_15-cmd

This post shows the basics of working with the command line. I’ll look at more advanced options in future posts.

SQL Compare is a fantastic product for simplifying work and it does so much more than this. Give it a try if you own it or download an evaluation today.

Posted in Blog | Tagged , , | 4 Comments

Does Management Care About the Database?

Posted on November 8, 2023 by way0utwest

Many of the services that businesses use are taken for granted. In an office, management expects electricity, water, and heat just work. If they don’t, then often a crisis is declared and funds are spent to rectify the situation. I’ve been in businesses where any of those systems fail and usually, it’s very disruptive. I think these days a computer network is often seen as the same type of utility service where we take it for granted, but if it doesn’t work, it’s a crisis.

Is the database seen as a service or is it more important than that? I think many software applications are different, and often seen as critical to business success, but I sometimes see both developers and management treating the database as a utility service. They just expect it to work, view it as a filing cabinet, and want to ignore it whenever possible.

I think this is one reason that developers are often searching out new platforms, either cloud or NoSQL, because they want to keep the storage aspect of data simple and not spend time or effort managing data. Or they want to experiment with something they think is easier to use. Many developers like the idea of a schema-on-read, and sometimes use that to argue against another relational database and instead choose another platform.

I think this is short-sighted, however, as the app now has the technical debt of supporting multiple schemas, and many of the databases using this format then suffer from one of two constraints. Either they store duplicate data for performance reasons, essentially de-normalizing their data storage, or they require some sort of ETL to ensure the data can be queried for analytics. Often schema-on-read isn’t efficient when querying across documents/keys/nodes/etc.

There might be good news for many of us database professionals as an article talks about the boardroom starting to think about the database. To be fair, this is a sponsored article from Redgate, but it is based on a lot of research and feedback from customers that show how management is starting to treat the database as a strategic asset and not a common utility. Many executives are starting to realize that their data is full of value if it can be analyzed, and that the performance of these systems is important. We both need well-designed databases and well-written code, and we need to ensure that database development keeps up with today’s application changes.

More and more of the customers I work with are finding their executives see more value in ensuring the database gets the resources to ensure it performs well, including better training for developers and better processes. It’s good to see more and more companies embracing modern DevOps development in both application software and database software.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Posted in Editorial | Tagged , | 2 Comments

T-SQL Tuesday #168 – Mature Window Functions

Posted on November 7, 2023 by way0utwest

tsqltuesdayIt’s time for T-SQL Tuesday and I’m hosting this month. I usually do one a year, just because I can and being responsible for a month keeps me engaged in the party.

This month my invitation is on Window functions and is described below.

If you’d like to host T-SQL Tuesday, let me know. I have lots of openings in 2024 and I’m looking for someone with a blog, some creativity, and an idea for a technical topic that you’d like to see other people write about.

Mature Window Functions

We’ve had window functions in SQL Server for a decade now, since SQL Server 2012.

This month I’m asking you to write on how window functions have made your life easier. A few ideas for you:

  • What problems have you solved with a window function? Bonus points for lead/lag/first_value/last_value
  • Have you used the SQL Server 2022 enhancements in any queries?
  • How has performance improved for you with a window function
  • Draw a picture of a window with a spatial function – more extra points

Give us some specifics, with real world problems. Obfuscate the data, at least if you have my name in your dev system, but help others understand how they might solve a complex aggregate using a Window function. The more specific examples, the more others might get help from one of the posts.

The Rules

Not many rules, but a few of them.

  • Post between 00:00:00 and 23:59:59 on 2023-11-14
  • Include the logo above in your post
  • Link that logo to this post
  • Leave me a trackback or comment on this post (double check if you have this automated)
  • Post you URL on Twitter, LinkedIn, etc. with the hashtag #tsql2sday
  • Have fun
Posted in Blog | Tagged , , | 16 Comments

Creating a Self Referencing FK in a CREATE Statement–#SQLNewBlogger

Posted on November 6, 2023 by way0utwest

I had written about a FK in a CREATE TABLE statement recently, but the second half of this was that after the original question, the person asked if this would also work for a self-referencing FK. It does, and I wrote this to show that.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Creating the FK

The last post showed how to create the FK, but this works within a table as well. Let’s say I want to have an Employee table that links back one employee to another, who is their manager. That type of structure looks like this:

CREATE TABLE [dbo].[Employee](
     [EmpID] [INT] NOT NULL,
     [EmpName] [VARCHAR](20) NULL,
     [MgrID] [INT] NULL,
  CONSTRAINT [EmployeePK] PRIMARY KEY CLUSTERED 
(
     [EmpID] ASC
)
) ON [PRIMARY]
GO

I can add a link that makes MgrID a FK reference by altering the code like this:

CREATE TABLE [dbo].[Employee](
     [EmpID] [INT] NOT NULL,
     [EmpName] [VARCHAR](20) NULL,
     [MgrID] [INT] NULL,
  CONSTRAINT [EmployeePK] PRIMARY KEY CLUSTERED 
(
     [EmpID] ASC
),
CONSTRAINT FK_MgrID_EmpID FOREIGN KEY (MgrID) REFERENCES dbo.Employee (EmpID)
) 
GO

Easy.

SQL New Blogger

This is a post that took me less than 10 minutes to write. I changed the code from the previous post and wrote this right after the other one. The search and replace was the longest code part, and then the writing was quick, 5 minutes.

This is a core skill for a DBA or developer. Write your own post to show how and why to build a self referencing FK for some scenario that you work with in your job, or in a project.

Posted in Blog | Tagged , , | 1 Comment