Category: SQL Server (page 2 of 3)

Photo by Luca Bravo on Unsplash
October 11, 2024 / 0 Comments

A Week in the Life- 10/7 – 10/10

Have you ever wondered what the normal work tasks of a database developer/integration engineer looks like? If you have, then this is the post for you. This is a new series of posts where I simply give an overview of what I accomplished each week, giving insight into what life as a database developer looks like for those who might be curious. I also want to do these reviews for my own records and edification, because it’s always good to keep track of the things you accomplish at your job. This post is going to review the week of October 7 – October 10.

What’s in this post

More SQL Server Setup

The week started with a request from one of my teammates to set up SQL Server 2022 on another Windows Server virtual machine as part of my ongoing project I am a part of. I have somehow made myself the SQL Server 2022 installation guru, so I’m not surprised that I was requested to complete this task.

Setting up the SQL Server itself was as easy as it could be, easier than the last few I did since the application using the server had less configuration requirements. But after installing the server and making sure the firewall rules were in place (as I learned two weeks ago), I then learned even more setup that I had missed previously that needed to be done for the server. The new thing I had missed was getting Entra ID authentication to work with the server, so one of my teammates showed me how to do that so that the new server was now setup perfectly.

Production Upgrade for an Application

My biggest feat of the week was having to do the production upgrade for the legal application project I’m on completely by myself. I was on this project with one of my teammates, but he had to be out this week for personal matters so I was left as the sole developer working on the production upgrade we had been building up to for months. Although I was nervous about being the only one responsible if something went wrong, I stepped into the challenge and completed the upgrade. I did run into a few issues during the upgrade process, but I was able to work them out by the end of the day by working with the vendor of the application, so I have to say the upgrade went pretty dang well given the circumstances. Pat on the back to me for surviving my first solo production upgrade.

Preparing for the Next Application Upgrade

I couldn’t celebrate this week’s successful application upgrade for too long, because I already had to be starting on the steps for the next phase of this application’s upgrade process. Thankfully, this week’s work for that next phase was only to take backups of two databases and provide that to the application vendor, so I wasn’t overly burdened with work for this part.

Writing Documentation about the Application

After completing that production upgrade, facing a few issues along the way, I knew that I needed to write everything down that I knew about the application and what went wrong with the upgrade, or else me and my team might forget the important but small details. None of us think that this type of work is truly something that we as database developers should be doing, we’re not application developers, but we have been told that we need to support this application from front to back so that is what we’re going to do. And since the territory is unfamiliar to everyone on my team, I know good documentation will be essential to continuing to support this application in the future.

Met with Colleague to Review Conference

There are only two women on my team: me and one other woman. We both attended the Women & Leadership conference two weeks ago so wanted to catch up with each other to review our learnings and takeaways so that we can present those ideas to our manager. This conversation was really lovely. When working in a field that is 98% men like I am, it’s always a breath of fresh air to be able to sit down, relax, and catch up with other women dealing similar tasks and issues. Our scheduled half-hour meeting turned into an hour because we were having a good time and brainstorming good ideas to present to our manager. We left with a list of items we wanted to cover with him and a plan for how to get some of his time to present our list.

Presented new Database Architecture to Division IT Team

I need to be forward and say that I was not the one who presented the database architecture to the other team, but I was included in the meeting since my teammate who normally works with this other team is going to be out of office for two months at the end of the year and I need to be apprised of the work he normally does.

This meeting with a business division IT team (not the corporate IT team that I’m on) turned out to be a fantastic relearning opportunity for me to see the database architecture possibilities in Azure. The backstory is that this other team had requested we create them a new database they could use for reporting purposes to not bog down their main application database. My teammate who normally works with them came up with several different new architecture possibilites that would fulfill the request, and ran those possibilities by the team to see which they would be most comfortable with. I had technically learned this same architecture information shortly after I started at the company, but that time is honestly a blur so I appreciated getting to see this information re-explained. I took lots of notes in this meeting and now feel more confident about making similar presentations and architecture decisions in the future when that will eventually be required of me.

Summary

This was a shorter week for me because I took Friday off, but I still accomplished a lot of big projects. I am looking forward to having some calmer weeks in the future where not everything is a “big stressful thing”, but I do appreciate that this week taught me a lot and helped me grow as a developer. I am slowly easing into the point of my career where I can start to be a leader instead of a follower, so I am taking each and every learning opportunity as a gift helping me to grow into that future leader.

Do you have any questions about what a database developer does day-to-day that I haven’t answered yet? Let me know in the comments below!

AI-generated image showing a server rack in the middle of a server room
October 1, 2024 / 0 Comments

My Least Favorite Thing About Azure Databases

Now that I have been working in the Azure cloud environment for a few months with my new job, I have come to the realization that there is one thing related to Azure SQL Server instances that annoys me more than anything else in the Azure environment. Obviously every cloud environment is going to have its pros and cons, so this is only one small problem from the whole ecosystem. Azure has a lot of nice things to work with, so this complaint is very superficial, but I know others at my company have been confused by this issue so thought I would share the annoying difference here for educational purposes.

On-Prem SQL Server Permissions

The one thing that has been driving me crazy about Azure SQL Server instances recently is that there is not an easy way to quickly get an overview of the permissions a user/account/group has on the server and database.

In on-prem/normal versions of SQL Server, you can open the “Security” section of the server or database then right-click on the user or group you want to review the permissions for, and then you’ll get a GUI that quickly shows all the server- or database-level permissions that user has. The following screenshot shows the Server Roles for a given login on my local test server, so you can quickly know what permissions the login has.

And then if you go to the “User Mapping” page of the Login properties, you can see any databases the login has access to as well as the permissions that user has on the database.

I love this interface that I’m pretty sure has been around for decades. It’s so much easier to view and quickly change the permissions for a login/database user that new new system in Azure-based servers.

Azure SQL Server Permissions

In comparison, you can’t easily view or determine the server- or database-level permissions any login or user has on an Azure-based SQL Server. You can’t even view any general properties of the user like you can for on-prem installations. For example, here is a screenshot of what you see on an Azure SQL Server when you right-click a Login from the Object Explorer:

If you do the same thing on the on-prem SQL Server, you can see that there’s an option to view the properties of the user, which will get you to the menus where you can see server- and database-level permissions, along with other properties as well.

If you can’t get a nice GUI representation of those login permissions, how are you supposed to know what permissions anyone has on your server or database? A SQL query.

While that kind of makes sense for a platform where you do most of your work with SQL, it does mean you need to do more work to write or track down a copy of the appropriate SQL query to get the necessary data, instead of to right-clicking on an object and have its information displayed to you immediately. The SQL query you need to get permissions information is also tedious to me because you can only do it for a single database at a time instead of being able to quickly view the permissions for every database on the server in one place. For example, if you have 3 databases on your server, you need to run the query individually for each of those databases. You can’t even utilize the “USE [database]” statement to quickly switch between the databases and get all the results in a single result pane (if you want to read more about that, I have another upcoming post to discuss that topic).

This is the query I use to review database permissions:

SELECT DISTINCT pr.principal_id, pr.[name], pr.[type_desc],
    pr.[authentication_type_desc], pe.[state_desc], pe.[permission_name]
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id
ORDER BY pr.name;

If you want to view the permissions at the server level, run that query on the master database. Then to view the permissions specific to a single database, you will need to switch to that database (using the database dropdown from the toolbar above the query window, or updating your full server connection to specify the other database).

To view the role assignments for users in your database, you can use the following query, which utilizes the views sys.database_permissions, sys.database_principals, and sys.database_role_members to gather all the useful information related to role assignments.

SELECT u.[name] AS [UserName],
       r.[name] AS RoleName 
FROM sys.database_principals u
     JOIN sys.database_role_members drm ON u.principal_id = drm.member_principal_id
     JOIN sys.database_principals r ON  drm.role_principal_id = r.principal_id
WHERE u.[name] = N'TestUser';

Summary

While it’s not hard to get all role assignments and assigned permissions for logins and database users in Azure SQL Server instances, it isn’t as easy as it used to be with on-prem servers. I don’t think the new method of getting the permissions gets the information you need in as useful a way as the old method of using the GUI. I personally don’t prefer having to scroll through a table of information about each user and deciphering the details of their permissions, but I will continue to deal with it since that’s the only option for Azure SQL Servers currently.

Photo by Luca Bravo on Unsplash
September 27, 2024 / 0 Comments

A Week in the Life- 9/23 – 9/27

Have you ever wondered what the normal work tasks of a database developer/integration engineer looks like? If you have, then this is the post for you. This is a new series of posts where I simply give an overview of what I accomplished each week, giving insight into what life as a database developer looks like for those who might be curious. I also want to do these reviews for my own records and edification, because it’s always good to keep track of the things you accomplish at your job. This post is going to review the week of September 23 – September 27, 2024.

What’s in this post

Meeting with a Software Vendor

One part of my job is interacting with various software vendors that we buy products from. I’m currently on a longer-term project where we are doing a piece-by-piece upgrades to one application that our legal department uses, so this week I met with the vendor’s development team to get an overview of the next step of this upgrade process.

One of the joys of working with vendors is sometimes you go into these meetings thinking the purpose is completely different than what it ends up being, then you have to rethink everything you planned for the meeting on the fly. That was how this meeting went. I wasn’t expecting that I would be the one taking lead on driving the topic by asking questions we needed answers to on our side before we could move forward with the next step of the upgrade. I thought the meeting was going to be the vendor giving an overview of the product for my own learning, not me driving the conversation. As someone newer to the company, this change was a little scary, but I handled it well and got good information for our team from the vendor.

Updating Security Certificates

This part of technology is something I’m still not very familiar with but had to dive into this week. My team had an ETL that broke because it could no longer communicate with our customer’s software due to an outdated security certificate. To fix this broken ETL, I had to locate the correct updated certificate and put that on our ETL server and remove the old one. After making such a small change, things were back to working as they normally do. This problem was a good learning opportunity and not nearly as difficult as I was expecting it would be.

Emergency Database Refresh

I got to experience my first emergency refresh request in our Oracle database environment this week because something went wrong with one of our production databases and the app development team wanted to refresh the database into the lowers as quickly as possible to start troubleshooting. I think fate decided we should have a good day, because our Oracle cloning process into our test environments went off without any issues and finished in about an hour, which is almost a record low. We’ve ran into numerous issues in recent months with the pluggable database cloning process in Oracle, so we are very thankful that one of those problems didn’t arise when we needed this emergency refresh.

Completing Database Permissions Requests

Another piece of Oracle work I got to experience for the first time this week was updating user permissions. We received a request from one of our application developers that they needed a service account to be granted the same level of permissions that his own account had (in test) so that he could complete a task. Although the SQL needed to complete a permissions change in Oracle looks a little different from what I’m used to in SQL Server, it overall was very similar to making permissions changes with T-SQL so was easy to complete.

Attending A Women’s Leadership Conference

My favorite part of this week was getting to spend Tuesday and Wednesday at the Women & Leadership Conference by the Andrus Center at Boise State. This was my first time attending this conference, and I was able to go with the handful of other women in the IT department.

As would be expected for this type of conference, the sessions focused on building soft skills in women instead of hard technical skills like previous work conferences I’ve attended. I listened to a range of women speakers, all of whom are in various leadership roles in different fields and states across the country. I wouldn’t say I loved every session I attended, but most of them were interesting and I learned a lot of tips for managing in the work environment as a woman that I hadn’t thought of before. In the near future, I will be writing a recap with more of my learnings from the conference, if you’re interested in hearing more.

Troubleshooting a Virtual Machine (VM)/SQL Server Connection Issue

This issue was not something I had ever considered before, because I’ve never had to think about the networking setup for VMs that I’ve worked with. Normally, someone else sets up the VM and makes sure it has all the networking and firewall rules needed before we get access to the VM. However, one of my project teammates and I found out that it’s not always the case.

I am working on a different application upgrade project from the one I mentioned above, and I was in charge of setting up those SQL Servers last week. This week, the main application developer started his portion of setting up the VMs we put the SQL Servers on, and he found that he was unable to access the SQL Server from his local computer version of SQL Server Management Studio (SSMS). When he messaged me about this connection issue, I had no idea where to start troubleshooting it since I had never had to think about how SSMS connects to the SQL Server on the VM before.

I had to work with our networking team to figure out the issue, which ended up being that the local firewall on the VM was not setup to allow ingress from the two ports that SSMS/SQL Server requires for connections. The networking team wrote two commands to allow that ingress, ran the commands on all 3 servers we setup last week, and then we were able to easily connect to the new SQL Server instances from our local SSMS apps instead of having to login to the VMs directly.

Finishing and Presenting a Python Data Analysis Script

One of the things I was most pleased and excited about this week was finally getting to demo a complicated Python script I wrote to optimize customer orders based on their previous order history, given a list of input parameters from the customer. I have spent months working on this script, going back and forth with the business users about what should be included, adding new features when requested, and even totally reworking the algorithm when their requests got more advanced. My demo of the script had an audience of the business users as well as members of my own team, including my manager.

The demo itself went really well, the script worked exactly as I wanted it to, running in under 5 seconds to optimize the customer data and provide a recommendation for what should be stocked to fulfill those orders, which is a massive improvement from the current process that takes a week to calculate the best possible solutions. What did not go as I expected was hearing from the business users that they are unsure if the solution provided is accurate enough, so they’re afraid to move forward with my script unless I did several things that would likely lead to several more months of rework. At the end of the meeting, we had decided as a team that instead of implementing this solution I worked hard on for months, that isntead we would put their current process onto a newer and beefier server in hopes that it would run faster than the current week.

No developer wants to spend months working on something just to be told that it won’t be used due to reasons that are out of the developer’s control, but that seems to be the situation I am in now. I am staying positive about it though, because it was a great Python development learning opportunity for me when I came into the company, and I have faith that with time, the business users will come around to using the faster and more modern solution when they see that the results I produced are very close to what they already get with the current solution. I might need to do a little more tweaking to get my algorithm’s results into an acceptable range compared to the current process, but I am hoping it won’t require a full rework to do so.

Summary

Sometimes I have weeks at work where I feel like I haven’t accomplished all that much. This week was one of those. But now that I have typed out everything I did like this, I am seeing that I do a LOT of work while I’m at work, and I am proud of everything I learned and accomplished this week, even if every single work item did not go as planned.

Being a database developer or data integration engineer comes with a lot of variation in work, which you can probably see by looking at this week’s and last week’s summaries. There is always something new to learn and work on, so I’m excited I’ve had another interesting week of work and I look forward to next week being interesting as well. (Although I’m technically on vacation next week, so really I mean the following week.)

Do you have any questions about what a database developer does day-to-day that I haven’t answered yet? Let me know in the comments below!

Screenshot of the SQL Server installer dialog with the option to create a new instance or update an existing one highlighted with a red box.
September 24, 2024 / 0 Comments

SQL Server 2022 – Getting the Installer Link to Work

I recently went through a whole day long ordeal trying to figure out how to get the ISO/installer for SQL Server 2022 so I could install it on a few VMs I made for a software upgrade, which I wrote about in a post last week. After spending way too much time just getting the installer downloaded, I then ran into another issue where when I clicked on the link to do a new installation of SQL Server 2022, the link did absolutely nothing. The solution for this problem ended up being simple, but it had me scratching my head and bothering my teammates to try running the installer as well before I figured out what needed to be done.

What’s in this post

The Problem

When you run the setup.exe or installer for SQL Server 2022, and select the option for “New SQL Server standalone installation or add features to an existing installation”, the software may do nothing when you click that link. It may have a spinning cursor for a small moment, but then nothing happens at all, no matter how long you wait.

The Solution

I found the answer to this issue on StackExchange. When I first downloaded and ran the installer, the standard Windows dialog came up asking if I wanted to trust the publisher of the program, since it was Unknown. I clicked yes. But despite that apparently, my computer had the file set to blocked, so when I clicked the link to start a new installation, the installer was blocked and nothing ran. To fix this problem, do the following.

Go to wherever you have the ISO file saved, for me this was the Downloads folder. Right-click on the file and select Properties. Then on that General/main page of that dialog, at the bottom there will be a checkbox that says “Unblock” with a message about security if you unblock the file. Check the box then click “OK”.

If you were experiencing the same issue that I was, you should now be able to reopen the installer file and click the link to start a new installation, and move on with your day.

Summary

There seems to be a bug with the installer for SQL Server 2022 where clicking the option to start a new installation seems to do nothing. This bug can be resolved by “Unblocking” the installer file through the Properties dialog of the file.

Photo by Luca Bravo on Unsplash
September 20, 2024 / 0 Comments

A Week in the Life- 9/16 – 9/20

Have you ever wondered what the normal work tasks of a database developer/integration engineer looks like? If you have, then this is the post for you. I want to start a new series of posts where I simply give an overview of what I accomplished each week, giving insight into what life as a database developer looks like for those who might be curious. I also want to do these reviews for my own records and edification, because it’s always good to keep track of the things you accomplish at your job. This post is going to review the week of September 16 – September 20, 2024.

What’s in this post

SQL Server Administration

This week was a bit of a throwback for me because one of my main tasks was to get SQL Server 2022 and one particular database up and running on three different virtual machines (VMs) for an application upgrade project I am working on. I have not created a new SQL Server, created a database backup, or restored a database backup in years. At my previous job, there was a dedicated team of DBAs that did that sort of work, and I was not on that team. But my current role is more of a jack-of-all-database-skills role, so it was time to blow the dust off my server creation skills.

The first step, of course, was to install SQL Server 2022 on each of the Windows VMs, following some guidance from the application vendor. I have never installed any SQL Server with so many custom configurations, so that was an interesting new thing to learn and do this week. After taking too much time to install SQL Server on each of the three Windows servers, I also installed SQL Server Management Studio (SSMS) 20, the main app used to interact with SQL Servers and their databases, on the VMs as well. That was the easiest part of this whole thing.

Before I could restore a copy of our production database onto these new servers, I needed to copy all existing/needed logins from the current production server to each of the new test servers so that the database restoration wouldn’t fail due to a user not having a corresponding login. I also did not want to have to manually repair all those database users when the SID values they were created with didn’t match the SIDs of the logins if I created all the new logins from scratch. This left me wondering how I could script out all the existing production logins, including their passwords (we have a lot of SQL auth logins on this server), so I didn’t have to do any of it manually. I found this very helpful Microsoft article that includes scripts for creating two stored procedures that you can then run to generate the perfect CREATE scripts for every login on a server. I would highly recommend using these scripts if you need to migrate/copy a database to a new server.

The final step that I needed to accomplish was to backup and restore a copy of the current production database to each of these new test servers so the app developers can do their side of the upgrade testing. I had to meet with one of the app developers so we could do our portions of the backup of production at the same time, since that is important on the app side to keep all data in the system aligned. Creating a backup was just as easy as I remembered.

Unfortunately, after I had the 97 GB compressed database backup file created, I then had to figure out how I was going to copy that file from the current production VM to the new test VMs, because we do not have any of these VMs networked together. My first idea was to simply do a copy/paste action from one VM to another, but that was projecting it was going to take 17 hours for one copy, and it kept failing after a few minutes with an unspecified error anyway (I’m guessing network issues). I could not think of any better way of getting the file from one server to another, and Google wasn’t being helpful for my specific situation, so I asked my main mentor coworker what he would do, and he suggested a brilliant idea: upload the backup file to one of our storage accounts in Azure by logging in on the source database, then after it’s uploaded, login to the storage account on the destination servers and download the file. This was the best idea for this situation (and only works because we do not limit internet access on our Windows server VMs, which I had at previous roles, so this method might not work for you). The upload of the 97 GB file took about 25 minutes. The download onto each of the destination servers was about 15 minutes. Much faster than the projected 17 hours of copying over our VPN directly from one to the other!

Ongoing Learning

As a part of any career, one should always be striving to learn new things and grow in their role. My current organization is fantastic at helping me to do this. Not only on an official level with conferences and online trainings, but also unofficially with everyone being willing to share what they know about our systems at any given time.

This week, I got to attend a casual training led by one of our Oracle app developers to tell us everything he knows about the Oracle Grid infrastructure and Automatic Storage Management (ASM), since those topics came up while we were troubleshooting an Oracle issue a few weeks ago, and I knew nothing about them. I also attended a more formal training with our Microsoft representatives to start wrapping up training we’ve been doing for a couple months on Azure AI and Machine Learning tools. Both of these training sessions were informative and useful and make me feel better prepared to handle upcoming projects and issues.

Python Development

Since I started at Boise Cascade, I’ve been doing a lot of development in Python, and am currently working on a project to accurately calculate optimal solutions for fullfilling customers requested wood orders. This project has been a big struggle, not due to technical issues, but due to changing requirements from the business customers, which means I’ve had to write two totally different solutions for this same problem over the past couple of months. However, the struggle is finally almost over because I have mostly wrapped up my rewrite of the solver using a new algorithm that a colleage wrote the basis of for me. (Yay for more experienced developers helping us solve difficult problems!) I am very excited about coming to a close on this program and will hopefully demo it to the business users next week to get final feedback and get it moved to prod in the very near future. The users are also very excited since this new program solves a problem in less than 5 seconds when the previous/current solution takes a literal WEEK to do the same thing. I have found it really interesting to write this program, but I also know I will find new projects equally as interesting when I finally get to move on.

Preparing for New Projects

Once I finally wrap up my wood order optimizer, I will get to move on to rewriting a different solver currently used by someone in the company who would like it automated away. To get up to speed on what the user is requesting, I met with one of my teammates this morning to get context on the solution that is currently in place and what the user wants the new solution to do differently. Basically, I needed to figure out what the heck this project was even supposed to be about. In my prep meeting, I learned that I will definitely need to meet with the user to discuss what they expect and need from the solution since they’re the one currently doing the solving manually, but I at least now have a general sense of what is being requested. I am looking forward to getting started on this project once I have the time.

Summary

That is my whole week in review! While database development usually does include a lot of writing SQL, developing ETLs to move data around, and doing other things directly with databases, that does not mean the work will always only consistent of those types of tasks. It all depends on the company you work for and how your specific role is defined. I am very thankful that my current role includes those types of tasks but also adds other work like writing data integration scripts in Python so that I can stretch my skills and knowledge.

Is there something specific you were hoping I would cover about my time at work that I didn’t cover in this post? Let me know in the comments below!

Photo by Scott Rodgerson on Unsplash
September 17, 2024 / 2 Comments

How to Download SQL Server 2022

You would think that figuring out how to download the installer for SQL Server 2022 Standard or Enterprise editions would be a simple task, much like the process for how you download any other installer or ISO for any other version of SQL Server. But with 2022, Microsoft seems to have changed their method for getting the software to people, making it a lot more locked down than other versions are. I’m not sure why this change was made, and if you have some insight please feel free to share it in a comment, but it was made and I have finally figured out how to get that installer with this new system. It took my colleagues and I a whole work day to figure this out. We had to work with our Microsoft account managers to get to the bottom of it, and it seems a little crazy to me that it would be that unintuitive to find the ISO for this software.

What’s in this post

Background

In my company’s current architecture, we create SQL Server virtual machines (VMs) by creating standard Windows servers through the Azure portal, and then one of the database developers will logon to the VM and install SQL Server through the ISO/installer, instead of creating a SQL Server VM from a provided image in Azure. In the past, when we needed to install SQL Server onto a VM, we would google something like “SQL Server 2019 download” and click on the link that takes you to the Microsoft downloads page, and then get the ISO from there. When we tried to do the same for SQL Server 2022, we were taken to a downloads page that only allowed you to download Developer or Express editions, and if we wanted to do Standard or Enterprise, we could only get an evaluation version after providing our information to start a free trial.

We obviously did not want a short trial version of the software, we wanted the full Standard edition, but could not find a full installer/ISO for that version. For a few months, we gave up and installed 2019 instead of 2022 whenever we needed to create a new server, but we reached a point where we are required to use 2022 for some applications, so I set out to find out how to get the Standard edition of SQL Server.

The Proper Method

There are two different ways that we found we could get a Standard or Enterprise edition of SQL Server 2022, and I’ll start with the method that I think they’re intending people to use. If your organization uses SQL Server in Azure already, you will likely need to go through this same method to get the installer for 2022.

I unfortunately can’t get any screenshots of this method of the process because I’m not an elevated user in our organization, but there is a way for your Azure tenant owner (and a few other select individuals) to get the latest ISOs and installers for every piece of Microsoft software your company has licenses for. I believe it is at admin.microsoft.com, but you can talk to your account representative to find out for sure. You can then navigate to “Billing” and then “Your Products” and then “Volume Licensing” where you can view all your licenses and get the downloads for them.

This is how we resolved the installer issue in our organization; we had our tenant owner download the ISO from this location and save the file in a shared location. Going forward, whenever we need an installer for any Microsoft product that we use, we will be required to have that one person download the files and send it to us (not a super convenient method if you’re a developer that doesn’t have full access to your entire Azure tenant).

Once you have the ISO, you can run it as you usually would to install any version of SQL Server, and your license key will be prefilled into the installer so you don’t need to worry about specifying that at any time during the installation process.

The Workaround Method

Before we had talked to our Microsoft account managers, this is the route we were going to use to be able to install SQL Server 2022 on our VMs. Go to the SQL Server 2022 page on the Microsoft website (https://www.microsoft.com/en-us/sql-server/sql-server-downloads) then download the “Developer Edition”.

After you download that version, run the installer that you downloaded. When it first opens, click the option that says “Download Media”:

On the next page, click “Download”, which will download the ISO for SQL Server:

After the ISO file has downloaded, double-click on the file in File Explorer, which will mount a “DVD” to your system, which will open another directory. In that directory, double-click on “setup.exe” to run the installer for SQL Server.

When the installer window opens, click on the “Installation” page from the left-hand pane, then click on the first link that says “New SQL Server standalone installation or add features to an existing installation”.

If you have an issue where clicking that link doesn’t open anything else, seems to do nothing, check out my other post about how to resolve that issue.

Click “Next” multiple times to move through the initial installation steps for SQL Server 2022. Review the information on each page before moving past it to ensure the settings are what you want them to be. When you come to the following page, keep the radio button for “Perform New Installation” selected then click “Next” again.

The “Edition” page is the one we’ve been needing to get to. On this page, you can change the selection from “Specify a free edition” to either one of the other two options to install Standard or Enterprise editions.

If you select the option for “Use Pay-As-You-Go billing through Microsoft Azure”, there is a dropdown menu that will let you specify Standard or Enterprise editions. Note that going with this option would then later require you to specify Azure Subscription information to bill the server usage to.

If you select the option for “Enter the product key”, you can then give a license key for SQL Server 2022 if you already have one. Review the two optional checkboxes under the license key box to select any options that apply to you. It looks like if you don’t specify a license key after choosing this option, you can still move forward with the installation, but there must be some point in the future of using the software where you’ll be required to provide a license key to keep using it.

Note: If you go through the “proper method” I listed above, the license key should be prefilled in the third option when you get to this page with the properly downloaded ISO file. For our situation, we also need to make sure the checkbox below the license key box “I have a SQL Server license with Software Assurance or SQL Software Subscription” is checked, since we use Software Assurance for our licensing.

After you’ve made your selections for the “Edition” page of the installer, you are ready to move through the rest of the installation process, specifying whatever options required for your own version of SQL Server.

Summary

There are two different methods I have found for getting the installer for SQL Server 2022 Standard edition, and neither of them are necessarily intuitive. Based on your needs, you can choose either the “proper” or the “workaround” methods. I hope this tutorial has helped you download the software without having to spend a day figuring it out like my coworkers and I had to.

Image showing white coffee mug and open notebook sitting on white table
April 16, 2024 / 0 Comments

Two Useful Keyboard Shortcuts for SSMS

Welcome to another coffee break post where I quickly write up something on my mind that can be written and read in less time than a coffee break takes.

This morning I was doing my normal work when I had a realization that I should share something I find super useful and use frequently use in SSMS that a lot of developers seem to not know about. They are small actions but they make your life easier when doing a lot of query editing in SSMS.

How to Minimize the Results Window

I have told many developers about this keyboard shortcut and they all appreciated it. I’m sure most people that frequently work in SSMS would like to be able to minimize and maximize the results window as needed in order to give themselves more screen real estate to work with while coding but still be able to see their query results as needed. But there is no minimize button for the results window of SSMS.

The only way that I know of to minimize and then maximize the results window in SSMS is to do CTRL + R. I use this keyboard shortcut every day at work while writing queries or updating existing queries.

How to Refresh the Intellisense

I have also had to tell many developers about updating the intellisense suggestions of SSMS since it will often trip them up if they don’t know how it works. First, you should understand that the intellisense offered by SSMS is only accurate as of the time you opened your query window or changed the connection for the query window (usually). If you’ve been working in the same query window for a while and have made DDL changes to any tables, functions, stored procedures, etc., intellisense is likely out of date and could tell you that a table or column you’re trying to reference doesn’t exist when you know it does.

If you ever run into this situation where it’s telling you something doesn’t exist but you know it does, use CTRL + SHIFT + R and the intellisense suggestions/corrections will be updated.

Bonus shortcut for Red-Gate SQL Prompt

Similar to the intellisense built in to SSMS, if you are using the SQL Prompt tool from Red-Gate, you can run into the same issue with the tool not recognizing that objects or columns exist when you know that they do. If you run into that issue and would like to update the suggestions list for SQL Prompt, use CTRL + SHIFT + D.

Image showing white coffee mug and open notebook sitting on white table
March 26, 2024 / 1 Comment

Updatable Views

Welcome to a coffee break post where I quickly write up something on my mind that can be written and read in less time than a coffee break takes.

Recently, while exploring possible options for converting our existing ETLs to working with Postgres, I found myself asking the question “can you update a table using a view that has been created on that table?” and the answer is Yes for both Postgres and SQL Server, which I learned after doing some research.

In Postgres, there is a concept of an updatable view, which is essentially a very simple view, that usually doesn’t include a join to a second (or third or more) table. There are other qualifications for what makes a view updatable, but it is generally the fact that the query creating the view is very simple. If a view meets these criteria, you can in fact update the base table using a query to the view.

For example, if you have a table called employee, then you create a view on top of that table which selects most but not all the columns from the base table, with the view being called v_employee, you can then run this query and it will update the data in employee.

UPDATE v_employee
SET full_name = 'George Smith'
WHERE emp_id = 15;

This concept is intriguing to me, and could also be really useful in updating our ETLs more quickly after we convert to Postgres, so I thought I would share this fun little fact with everyone.

Sources

March 19, 2024 / 0 Comments

Postgres vs. SQL Server, Part 3

If you haven’t seen it already, I had two previous posts listing differences I have found between Postgres and SQL Server while learning to develop in the Postgres environment. As I am continuing to learn, I am finding even more differences that I think are interesting, and they are going to be shared below.

What’s in this post

Creating a temporary table

The difference between SQL Server and Postgres for creating a temp table is small, but significant. Mostly because my company uses temp tables frequently in our stored procedure and ETL code, so as we migrate off of SQL Server, we’re going to have to do a lot of updating to move temp table statements to Postgres. The syntax for SQL Server is to use a hashtag/pound/number symbol in front of the table name, or to use a double-pound symbol to create a global temporary table, like “CREATE TABLE #MyTable” or “CREATE TABLE ##MyGlobalTempTable“. In Postgres, the syntax requires that you use the TEMP or TEMPORARY keyword to make the temp table, like “CREATE TEMP[ORARY] TABLE my_table“.

You also have to use the TEMP or TEMPORARY keyword when creating a temp table in Postgres by doing a SELECT INTO statement, such as SELECT column1, column2 INTO TEMP TABLE my_table. In SQL Server, that same command would look like SELECT column1, column2 INTO #MyTable.

General differences between temp tables in each engine

Not only is the way you create temporary tables different between Postgres and SQL Server, but how the tables are actually stored on the database is completely different between the two as well.

In SQL Server, any temporary objects are stored in a separate database called tempdb. When creating a temporary table, you can specify whether it’s localized to your current session only or whether it is a global temporary table that can be accessed from different sessions or query windows, as I mentioned above. To do the first, you specify the table name as #MyTable, then the do the latter, you specify the table name as ##MyTable.

In Postgres, temporary tables are not stored in a separate database. Instead, they are put into their own system schemas whose names are based on numerical values (ex. pg_temp_1, pg_temp_2, etc.), so it’s harder to see the specific location where your temp table is being stored. You also are generally unable to access a temp table in one session from a different session, so there is no concept of a global temp table in Postgres.

One similarity of temp tables between the two engines, though, is that they are dropped/deleted in both as soon as the session in which they were created ends.

Getting the current UTC time

In SQL Server, there are two built-in functions that allow you to get the current UTC time, GETUTCDATE() and SYSUTCDATETIME(). The former returns the time in type DATETIME, and the latter returns the time in type DATETIME2 (more precise).

Postgres also has multiple functions and methods of getting the current UTC time, but they look much different from each other than the SQL Server options look. While there are more than just these two options, the most common ways to get the UTC time are the following:

  • SELECT NOW() AT TIME ZONE 'utc';
  • SELECT CURRENT_TIMESTAMP AT TIME ZONE 'utc'

Although the two options look different, they will return the same value. I’ve had some issues trying to use one versus the other in different scenarios in the SQL code I’ve written for Postgres, so it seems like there may be times where it’s better to use one over the other, but I’m just not able to differentiate those scenarios right now. There are also other options for getting the current UTC time, but I won’t cover those here.

Updating a table with a JOIN

One of the biggest syntax differences between T-SQL and pgSQL that has really gotten me confused so far is the formatting of an UPDATE statement that includes a JOIN to a different table. To me, the syntax of T-SQL makes a lot more logical sense and the pgSQL syntax doesn’t seem as intuitive. The T-SQL syntax is as follows:

UPDATE p
SET p.MyColumn = s.MyColumn
FROM person AS p
INNER JOIN staging AS s
	ON s.MyColumn2 = p.MyColumn2

Then the syntax for pgSQL is:

UPDATE person
SET p.my_column = s.my_column
FROM staging
WHERE staging.my_column2 = person.my_column2

To me, the pgSQL syntax doesn’t scream “joining to a different table”; it really looks to me like a simple update statement without a join. Yet the join is there. Also, it doesn’t seem to let you use aliases for the query, which I also dislike since I use aliases for every query I write. I wish I could change the standard for the pgSQL syntax, but obviously that isn’t within my power so I’m going to have to learn to live with it.

Making a column nullable

Unlike the pgSQL syntax for updating a table using a join to a different table, I actually like the syntax of making a column nullable in pgSQL more than I like the T-SQL syntax. The pgSQL syntax seems much more clear and straightforward for this action. The syntax in Postgres for making a NOT NULL column nullable is as follows: ALTER TABLE my_table ALTER COLUMN my_column DROP NOT NULL;

The T-SQL syntax for the same action is less clear, since you are basically redefining the column like you would if you were adding it, instead of specifying what you are changing. If you had a column that was set to NOT NULL in a table, you would write out the whole definition of the column again but write NULL instead of NOT NULL. For example: ALTER TABLE Mytable ALTER COLUMN MyColumn INT NULL;

I’m not fond of that T-SQL syntax because you can’t tell at a glance what is changing with the column definition unless you already knew what it was before running the statement. Since that same statement would also be the same syntax for changing the data type for the column if the column was already set to be nullable.

Data type for automatically updated binary numbers

In SQL Server, there is a data type called TIMESTAMP or ROWVERSION that is an automatically updating binary value that is added to a record when that record is updated or inserted. (The TIMESTAMP synonym for the ROWVERSION data type has been deprecated.) My company currently uses this data type heavily to help identify changed data quickly for our ETLs. As we are looking at moving everything into the Postgres world, we have found that Postgres does not have any built-in functionality similar to the ROWVERSION column, at least not at the precision we would need it to be (you can read about system columns here). You of course can store binary data within a table, but there is no data type for columns that will automatically increment a binary value when data is changed with the same precision as SQL Server’s ROWVERSION.

There do seem to be some ways people have replicated that functionality using custom triggers/functions that we are considering, but I haven’t looked into it that much personally so I cannot yet speak to how well that would work to replicate SQL Server’s ROWVERSION functionality. So we are either going to have to add these custom triggers/functions or find another reliable and fast way to identify changed data between source and target for our ETLs. Soon, I am hoping to do a blog post about the possible Change Data Capture (CDC) options for Postgres that we are considering.

Conclusion

There are so many weird quirks and differences between SQL Server and Postgres that I am having to learn as I grow in the Postgres development space. While I have already listed so many of them across several posts now, I am sure there will be many more to come as I keep learning. And I will continue to compile a list of the differences and share them here on my blog as I find them.

Sources

  • https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-temporary-table/
  • https://www.postgresql.org/docs/current/datatype-datetime.html
  • https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-update-join/
  • https://www.postgresql.org/docs/7.3/sql-altertable.html
Photo by Alex Shute on Unsplash
March 5, 2024 / 0 Comments

Getting SQL Prompt to Prompt on RDS Servers

This may seem like a ridiculous thing to need to write about, making the Red-Gate tool SQL Prompt generate prompts like it should, but I have been having a weird issue with it over the past couple months and have finally learned the solution. So of course I thought I should share it!

What is SQL Prompt?

SQL Prompt is a tool made by Red-Gate that works as a much cleaner, nicer, and more useful autocomplete feature for SQL Server Management Studio (SSMS). It is a plugin you install to SSMS that then seems to magically work to help you write queries faster. Not only does this tool autocomplete databases, schemas, tables, and column names for you in your queries, but it also provides a lot of other useful tools like a Snippets Manager, which allows you to use default and custom snippets to write code faster (e.g. writing “sf” then pressing Tab will type out “SELECT * FROM” for you so all you need to type is the table name you want to select from).

Every developer in my organization uses this tool heavily in our day-to-day operations while writing any SQL scripts because it makes writing queries so much faster. So when my SQL Prompt seemed to stop working after an update, I was getting really frustrated because it meant I had to write all of my SQL queries manually again. And when all you do all day is write SQL, that adds up to a significant hindrance to your work speed.

My Problem

The problem I was having with SQL Prompt was that when I connected to any of our RDS database instances, the tool would no longer do any prompting of schemas, tables, or columns which was making my coding life so much harder. Oddly, the snippets manager portion of the tool was still working fine, so at least I wasn’t needing to type out the queries I normally use the snippets shortcuts for. Also oddly, Prompt would work perfectly fine connecting to other databases that weren’t on RDS instances, it was only happening for RDS databases.

I dealt with this issue for months on our production server, since I figured it was due to the security settings or something else I wouldn’t be able to fix, and I don’t access prod servers very frequently, so when I did use them and the Prompt wasn’t working, it wasn’t as bothersome. But after I had to completely reset my developer computer and reinstalled SQL Prompt, I started having this same issue for our lower environment databases, that I work with every day, so Prompt not prompting was suddenly a big deal.

The Solution

I created a support ticket with Red-Gate since I’ve always had good luck with their support services. This time wasn’t any different. Within a couple hours of creating the ticket, I had an email from a support rep asking me if I had tried checking the “Trust Server Certificate” check box on the Connection Properties tab while connecting to the RDS servers. No, I had not done that because I did not know that was an option before that day.

I disconnected from the RDS server then reconnected, making sure to check that box before clicking “Connect”, and now I had SQL Prompt back up and working, providing prompts of schemas, tables, and columns just like I want it to. Yay!

I also logged onto our prod server to see if doing the same thing there would fix that issue, and it fixed Prompt there as well. I am so excited to be able to not type out every detail of every SQL query again!

I love quick fixes like this.

« Older posts Newer posts »

Categories