Our July 15 meeting will again be online. NOTE: you MUST RSVP to this Meetup (see instructions below) to view the Zoom URL!
This is our annual July meeting in which we feature lightning talks, and discuss our upcoming SQL Saturday!
Want to do a lightning talk for our July monthly meeting? Contact Greg Moore at firstname.lastname@example.org by July 8!
Lightning talks should be around 10 to 15 minutes in length. It can be about any topic that appeals to data professionals, including (but not limited to) T-SQL, performance tuning, BI, tools, and professional development. New and novice speakers are especially welcome and encouraged to apply!
Our online meeting schedule is as follows: 6:00: General chat, discussion, and announcements 6:30: Presentation
Replication is a native SQL Server component used to distribute and aggregate data between SQL Servers and other heterogeneous data sources. In this presentation, Hilary Cotter, covers how to effectively chose and deploy optimal SQL Server replication solutions. He also covers performance tuning, optimization, monitoring and integrating replication into your DR solutions.
Hilary Cotter is an industry veteran and has been a SQL Server MVP for 17 years. He specializes in replication, SQL HA technologies, full-text search, perform acne tuning and SQL Server Service Broker. He has worked for many fortune 500 companies implementing cutting edge replication solutions. He has written and co-authored several books, white papers and authored Microsoft exams. He has answers over 10,000 questions on the Microsoft SQL server forums, some of them correctly.
Our online meeting schedule is as follows: 6:00: General chat, discussion, and announcements 6:30: Presentation
As I wrote earlier, I discovered, much to my chagrin, that I vastly underestimated how much disk space I would need when I purchased my laptop. So I went to Amazon and bought a new 2TB internal SSD for my machine. It’ll take about a week or so to arrive. Until it gets here, I needed to free up some space on my drive.
So, I first went to my Control Panel and looked at uninstalling SQL Server. But when I got there, I saw a number of things under Programs and Features, as you can see below. What, exactly, am I supposed to uninstall?
I went to my old and trusted friend, Google, for advice, and found this article (among others).
The first step is backing up your data. Well, I don’t have any data to back up, so that isn’t an issue at the moment. It likely will be somewhere down the line, but at the moment, I don’t anticipate that to be an issue. That said, if you do have important data that you want to save, make sure that you back it up first!
Next, they tell you to stop the services. I opened Services (hit the Windows key and type “Services”) and look for any SQL-related services. I shut down MSSQLSERVER, SSAS, SSIS, and so on — basically, anything that looked like it was tied to SQL Server. I checked everything listed under “SQL Server.” I also looked under “Microsoft,” but I didn’t immediately see anything related to SQL Server when I did.
I then followed the steps to uninstall. Step 2 tells you to “search for ‘sql’ in the search box,” but what it doesn’t tell you is that you need to do that from Apps and Features, not from Settings. That step is missing from the instructions. I selected Microsoft SQL Server (Version) (Bit) per step 3 — for me, it was Microsoft SQL Server 2019 (64-bit) –and selected Uninstall. I selected the appropriate prompts, and off it went.
I didn’t think to time how long it took to run, but by my estimate, uninstalling SQL Server took about twenty minutes to run. It appeared to uninstall cleanly; I didn’t see any errors. By the time it was finished, I had about 16GB of new space on my drive.
So, that’s my adventure in uninstalling SQL Server to clear space on my drive. When my new SSD arrives, I’ll make sure it gets reinstalled. (And stay tuned for a future article about installing my new SSD on my laptop.)
A while back, I wrote about installing SQL Server 2016 on my laptop. Since then, Microsoft has released SQL Server 2019. Additionally, I bought a new laptop last November; my tired old HP 4430s had served me well for several years, but it was showing its age, so I decided it was time to upgrade. Since we have updated versions of SQL Server, and I have a (still relatively) new laptop on which to install it, not to mention that I have some time with this COVID-19 isolation, I figured this would be a good time to install SQL Server 2019 on my new laptop.
Before we begin, let me start with my laptop specs. I make no claims that these are the recommended specs for SQL Server, but this is what I have.
HP Pavilion x360 Convertible 14m-dh1xxx
Intel Core i5-10210U @ 160 GHz
8 GB RAM
Windows 10 Home Edition (it’s what came installed)
129GB available disk space
I started by going to the SQL Server downloads page and downloaded the freeware version of SQL Server 2019 Developer. There are a number of versions on this page, including (among other things) a trial version of SQL Server 2019 on-premises and SQL Server 2019 on Azure (and, of course, the Express version of SQL Server). For my rather modest needs, which includes practicing SQL Server skills, writing about it from time to time, and having some fun with it, Developer version should suit my needs.
The link downloaded SQL2019-SSEI-Dev.exe to my machine. I ran the file and was greeted by a screen asking for the installation type.
I decided to use the Custom option. The lazy body in me thought about running the Basic installation type, but since I’m documenting this installation, I figured it would defeat part of the purpose.
The next screen asked where to download the media. By default, it goes to C:\SQL2019. Since most everything I download goes to my Downloads folder, I decided to switch it there. I set it to download to a SQL2019 folder within my Downloads folder. It also indicated that I would need 8.9GB free space, with a download size of 1.4GB. My new laptop doesn’t have the disk size that my old one did, but I still have plenty available, so it shouldn’t be a problem. (One thing I should note: my new laptop uses a SSD, as opposed to the traditional storage disk on my old machine.)
I clicked Install, and the install package started to download.
Once the Installer finished downloading, the SQL Server Installation Center appeared.
I ran the System Configuration Checker, and it came up cleanly. I decided to proceed with installation. I clicked the Installation tab and selected New SQL Server stand-alone installation or add features to an existing installation.
On the Product Key page, I selected Developer under free edition. The next few screens were straightforward — the only warning I saw was for my firewall — until it got to the Feature Selection screen. I went ahead and selected all features, which would take up 14GB of disk space. If you’re installing SQL Server on your own machine, you’ll need to decide what features you want to install at this point.
I went with the default instance for the instance configuration.
I selected standalone PolyBase-enabled instance. I’m using this on a standalone configuration, after all.
Since I don’t have Java installed on my machine, I used the Open JRE included with the installation. If you have Java on your machine, you’ll need to determine what instance of Java you want to use.
Under Server Configuration, I used the default service accounts. There’s a note that reads: “Microsoft recommends that you use a separate account for each SQL Server service.” I am not sure about the implications of using the default service accounts; this would be a question for someone who knows more about SQL Server than I do.
Under the Database Engine Configuration, I went with Windows authentication mode. If I was installing this under any configuration other than my own machine and login, I would not go with this option; I would use Mixed Mode and specify a SQL user account. I added myself (clicking Add Current User) as the administrator for this account. Again, this is not something I, personally, would recommend for a large-scale installation, but since I’m the only one who’ll be using this instance, and I have no intention of using this for anything other than demo, practice, and documentation purposes, I went ahead and used Windows Authentication.
I pretty much went with the defaults for the rest of the installation. I did need to consent to install R and Python. I got to the Ready to Install screen, clicked Install, and let it do its thing.
Installation was straightforward and painless. In years and installations past, I’d be writing about the errors that came up and the number of times I’d have to click or press Enter to continue with the installation, but there were no such prompts. I let it go and went off to do other things. I’m not sure how long it took — I’ll guess around twenty minutes, although it seemed longer — but when I looked again, SQL Server was installed on my laptop.
That was as far as I got for this installation. I still need to tinker with post-installation configurations, including SSMS, SSIS, and any tools that I need to actually do something with SQL Server. That’ll likely come later when I have a chance to tinker some more.
The weekend began with the speaker’s dinner. While I had a great time hanging out with all my wonderful SQL friends, we also had a compelling conversation — enough that I wrote about it that night. It reaffirmed just how important professional development is within technical circles, and the importance of my endeavor of presenting professional development topics. Even Matt Cushing sent a tweet mentioning just how important professional development is. It made me feel pretty good knowing just how much I’m contributing to the technical community.
Of course, after dinner, Paresh and I just had to go for ice cream. On this occasion, we hit a Coldstone Creamery around the corner from the speaker’s dinner. For those of you who don’t follow me regularly, it has become tradition for Paresh and me (and anyone who joins us) to go out for ice cream at any SQL Saturday that we both attend. Paresh even started using a Twitter hashtag for it: #SQLIceCream!
There was a lot of talk about the weather leading up to the event. The National Weather Service had predicted heavy lake-effect snow (as much as two feet) leading up to the event. Rochester sits right on the bank of Lake Ontario, and as anyone who lives in upstate New York knows, is prone to lake-effect snow. The weather forecast even prompted the University of Maryland to reschedule a women’s lacrosse game against Syracuse. As it turned out, the talk about bad weather turned out to be exactly that: talk. When I drove out to Rochester on Friday, the amount of snowfall was negligible, and the Thruway was clear sailing all the way from Albany to Rochester. There was a light dusting of snow on the ground when I woke up the next morning, but that was about it. Despite all the hype about the weather, it turned out not to be a factor.
I arrived at RIT a little later than I would’ve liked. Ordinarily, I like to settle into the speaker’s room, maybe get myself some coffee, talk to some people, and relax a bit. But by the time I arrived, sessions were about to start. I barely had enough time to drop my stuff off in the speaker’s room before getting to the first round of sessions.
Of course, the first session I attended was Matt’s. I’ve pretty much attended his session each time I’ve had the chance. For a while, I had attended it every time he presented it, but that streak was broken when he started traveling to other places (such as Austin, TX) that were difficult for me to get to. Last year, Matt (along with another friend, Deborah Melkin) were named IDERA Aces. I won’t get too much into the IDERA Ace program (use the link for more info), but one of the benefits is funding that allows you to travel to speak at events like SQL Saturday. (Congrats, by the way, Matt and Deb!) Matt is encouraging me to sign up for the IDERA Ace program for this year. When applications start coming out later this year, I will definitely look into it!
Speaking of Deborah Melkin, her session was up next in the room next door. I’ve seen her session before, and she does a great job with it. I have to confess, however, that I wasn’t paying all that close attention (sorry, Deb!). The reason: my lightning talk session was up next, and I was going through my slides, making sure everything was ready to go. Last November, I had purchased a new laptop (an HP Pavilion x360), and this was my first time using it for a SQL Saturday presentation. I checked my slides, I tested my presentation clicker, and made sure everything was ready to go.
The next round included my lightning talk. I was scheduled to do my talk, along with two friends: Taiob Ali and Kimberly St. Jacques. The last time I saw Kimberly was at PASS Summit, which wasn’t a good time for her. She had been scheduled to present a lightning talk at Summit, but was unable to do so because she had lost her voice. I’d felt really bad for her! I was glad to see that she had regained her voice and was able to present again!
Going on at the same time was a presentation by Tracy Boggiano, who talked about mental illness in tech. It sounded like a really interesting talk, and I desperately wanted to attend, but it conflicted with my lightning talk session. I told Tracy that I intend to attend her session the next opportunity that I had.
Lunch was the next order of the day. It was either Andy or Matt Slocum — I don’t remember whom — who had a good relationship with a BBQ restaurateur. This same person has catered Rochester SQL Saturday every time I’ve attended, and the food was excellent every time. I highly recommend the pulled pork!
I decided to take the next round of sessions off. As much as I love attending SQL Saturday, one thing that never fails is that it tires me out. After lunch, I felt like I was going to fall asleep, so I retreated back to the speaker’s room to relax. I pretty much just surfed the web and conversed with other speakers in the room.
The last time slot of the day came about, and it was time for me to do my own full-length presentation. I actually had a pretty good-sized audience: I’ll guess around fifteen to twenty people. Even though it was the last session of the day, and everyone (including me) was tired, I made it a point to keep my audience engaged. They seemed to be into my talk, and I like to think that I presented well.
I did attend the after-event party. I enjoy hanging out with these people (they’re called #SQLFamily for a reason), and any chance I get to spend time with them is welcome. I had a long drive home ahead of me; I left the party around 7:45, and pulled into my driveway around 11:30.
All in all, it was yet another awesome SQL Saturday! (Of course, I think they’re all awesome!)
My next scheduled event is SQL Saturday Chicago on March 21, only a few weeks away! This will only be my second time in Chicago (changing planes at O’Hare doesn’t count), and it’ll be my first SQL Saturday where it is not feasible for me to drive. It should make for an interesting trip. See you there!
“Any fool can write code that a computer can understand. Good programmers write code that humans can understand.”
— Martin Fowler
I’m currently working on a project in which I’m trying to deconstruct a database. In doing so, I’ve come across a number of things about it that, in the scope of databases, appall me. Who the hell creates a relational database with no defined primary or foreign key constraints??? And this thing is in a production environment, no less! While that’s a big part of my frustration, that’s another rant for another time. For this article, I want to focus on something else.
A big part of my task — and my frustration — is trying to figure out what the data columns are and how they’re being used in the application. I did come across a table that contains data as to what primary keys are defined (I have no idea why whomever built this thing didn’t actually create the primary keys), and I’m spending a lot of time trying to figure out how these tables relate to each other. As I already mentioned, there are no foreign key relationships defined. So a lot of my time is being spent trying to figure that out.
This is where my frustration — and the purpose of this article — kicks in. Whomever built this structure used names like “DataCounter,” “CrossReferenceCounter,” and so on, to define their “primary keys.” (I put it in quotes, because, like I said, they’re not actually there. And who uses the word “counter” to define them?) What I’m finding is that the corresponding foreign key isn’t exactly the same. For example, while the entity table uses ” DataCounter” for its “primary key,” other tables reference it using ” DataIDCounter.”
This might not seem like a big deal, but when you’re trying to figure out how to map large numbers of data tables and columns, you start questioning whether or not the relationships are correct. And I came across several others whose naming conventions are even worse.
Some of you might be saying, “that’s not a big deal. What’s your problem?” Well, trying writing an ad hoc query where you type in what you think is the column name, and it turns out to be something completely different. You end up wasting your time going back to look it up and trying to figure out what it is.
Explain to me how that is helpful to somebody trying to troubleshoot or edit the code.
In my previous life as a developer, I would write what I referred to as “open-ended code” — that is, I wrote it with the idea that it would likely be rewritten somewhere down the line. I wanted to make it easy for me (or someone else) to go back and edit or change the code, if necessary. I like to think that other developers have this same mindset, but, all too often, I come across examples like this that tell me that that is not the case.
If you’re a developer — whether it’s for an application, website, database, network, or whatever — keep your naming conventions and infrastructure consistent and meaningful. You will save another developer or support analyst a great deal of grief, frustration, and time.