This is going to be a super quick post because I only want to share a short query that I’ve developed for myself to be able to see what schema-level permissions have been assigned to users on an Azure SQL Server. If you didn’t see my previous post about my least favorite things about Azure, you should check that out because it includes other queries I’ve been using to identify what permissions have been granted on a server or database.
The Query
My query I use for getting a list of all schema-level permissions on a database uses the sys.database_principals, sys.database_permissions, and sys.schemas views.
SELECT DISTINCT pr.principal_id, pr.[name], pr.[type_desc],
pr.[authentication_type_desc], pe.[state_desc], pe.[permission_name], pe.class_desc, s.[name]
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.schemas as s
on s.schema_id = pe.major_id
ORDER BY pr.name;
As you can see, the query is very short but it provides the most useful and relevant information for schema-level permissions on a database. It provides just enough information without being overly complicated or giving you more than you need. This query tells me what users have been given what type of permissions for every schema in my database.
If you connect to a SQL Server instance using SSMS and you’re trying to find what version of of SQL Server it is, you might be left scratching your head since it doesn’t seem to be in the server properties anywhere. Or at least there isn’t a value that lines up with the version numbers you’re used to hearing (2016, 2019, 2022, etc.). This will be a quick and easy post explaining how you can find the normal version name for a SQL Server you’re connected to.
The first thing you need to do is connect to your server instance using SQL Server Management Studio (SSMS). After you connect, you should see the server listed in the Object Explorer, which is where we will be getting the version number from. To the right of the server name in paratheses, you should see something like “(SQL Server 16.0.1000.6 – myusername)”.
That is technically the most correct version number for the SQL Server software you are running, but it’s not the one that most people think of when they think of SQL Server versions. To get to that version, you can look at the very useful table included on this blog post from SQL Server Builds.
For the example screenshot above, the server is really SQL Server 2022 because the “Release To Manufacturing” number of “16.0.100.6” corresponds to that version of SQL Server, as can be seen in the table included in that blog.
You can also find the same version number by right-clicking on the server in the Object Explorer list, then selecting “Properties” which will bring up a dialog where you can see the version and other settings for the server.
Note: For even more ways that you can find the version number of any SQL Server, there is this very useful blog post from SQL Server Builds again that I found super informative.
Summary
Now you should know how you can find the normal SQL Server version number for your instance based on the “Release to Manufacturing” number you will find to the right of the server name in the Object Explorer of SSMS. I wish they would put “SQL Server 2022” somewhere in the Properties of the server so we didn’t have to do this lookup, but for now we aren’t that lucky.
Note: Curious about what “Release to Manufacturing” really means? I found a great explanation on Wikipedia that clarified that term for me.
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.
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!
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.
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.
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!
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.
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.
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.
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!
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.
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.
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.
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.
