Less than two weeks until #PASSDataCommunitySummit! #PASSSummit #SQLFamily #Networking

November arrived yesterday (where did this year go???), and it only recently occurred to me that I will be in Seattle in less than two weeks, speaking for the fourth time at PASS Data Community Summit (or it’s equivalent)! On Wednesday morning, among the first sessions of Summit, I will be giving one of my favorite presentations: the one on networking. Come check it out!

It still amazes me that I will be going back to speak at this awesome event for the fourth straight year. Whenever I look through the list of speakers — several of whom have become very good friends through my association with events such as SQL Saturday — I continue to be in awe of the fact that my name and face is associated with this amazing group of data professional rock stars. I started speaking on the SQL Saturday circuit in 2015, and if you’d told me back then that I would be speaking at PASS Summit for four straight years, I likely would’ve asked what you were smoking.

And yet, here I am. I don’t consider myself a SQL Server expert — heck, none of my presentations even have anything to do with SQL Server — but nevertheless, I am still contributing to the SQL Server, as well as other technical, communities. I sometimes ask myself if I really belong in this same group of talented data professionals; indeed, I was even once asked how I’m associated with this group. I think that’s a very valid question, and I sometimes ask myself that same question.

But one doesn’t get to speak at PASS Summit four straight years unless you’ve got the goods. I once described PASS Summit as being the SQL Saturday All-Star Game. If you’re picked once, it’s a great honor. If you’re picked more than once, you’re a solid player. Four straight years? Now we’re starting to get into Derek Jeter territory.

Okay, I don’t consider myself the same caliber as Jeter. I’d consider myself more like, say, Ozzie Smith: someone with a long and distinguished career who didn’t hit for a high batting average. He stayed steady and just did his thing. And that’s pretty much what I try to do.

Hope to see you in Seattle in two weeks!

Advertisement

#PASSDataCommunitySummit is here! (And I’m speaking!)

It’s here! PASS Data Community Summit starts today!

This is my third straight year speaking at PASS Data Community Summit (or its equivalent), and I look forward to this event each year! This is an event that has become near and dear to my heart, and I try to attend whenever I have the opportunity.

Today and tomorrow (Nov. 8-9) are the pre-con sessions. Unlike the Wed-Fri conference sessions, there is a fee for attending pre-cons. And while I, personally, am not attending the pre-con sessions, I can tell you that they are led by many world-class speakers. Check out their schedule, and if you see any sessions that interest you, they are well worth your time (and your money) to attend!

The rest of the conference (Nov. 10-12) is free to attend this year! There are a number of great sessions presented by many wonderful speakers! Again, if you see any sessions that interest you, I encourage you to check them out!

And, of course, I need to include a plug for myself! As I mentioned, this is the third straight year that I am speaking for this event. My session is titled: “I lost my job! Now what?!? A survival guide for the unemployed.” If you are out of work (or even if you’re looking for employment), this session offers tips on how to survive a jobless situation. I am scheduled to speak on Thursday at 9:30 am EST. Hope to see you there!

There are also opportunities for networking as well! You can speak with vendors, speakers, and other attendees. I encourage you to check out things like the Community Zone and the Expo Lounge.

It isn’t too late to register for this year’s free online PASS Data Community Summit! Just use the link to register and attend this great conference!

See you at Summit!

January CASSUG Monthly Meeting

Our January meeting will again be online. NOTE: you MUST RSVP to this Meetup (https://www.meetup.com/Capital-Area-SQL…/events/275432320/) to view the Zoom URL!

Our January guest speaker is Bob Ward!

Topic: SQL Server Edge to Cloud

SQL Server is everywhere you need it from the IOT Edge to your cloud to public clouds. With all of these options it can be difficult to know which to choose and why each options may be right for your data needs. In this presentation, I’ll review with you all the current released and preview versions of SQL Server from the edge to the cloud. I’ll compare each of them and discuss why you want to get ahead of the curve by understand what is possible with the modern SQL Server. I’ll discuss technical details of some of these options so you can understand what SQL Server flavor fits your company needs. This session will include some demos to highlight key innovations with SQL Server.

About Bob:

Bob Ward is a Principal Architect for the Microsoft Azure Data SQL Server team, which owns the development for all SQL Server versions. Bob has worked for Microsoft for 26+ years on every version of SQL Server shipped from OS/2 1.1 to SQL Server 2019 including Azure SQL. Bob is a well-known speaker on SQL Server, often presenting talks on new releases, internals, and performance at events such as PASS Summit, Red Hat Summit, Microsoft Ready, SQLBits, SQLIntersection, Microsoft Inspire, and Microsoft Ignite. You can follow him at @bobwardms or linkedin.com/in/bobwardms. Bob is the author of the books Pro SQL Server on Linux and SQL Server 2019 Revealed available from Apress Media.

Our online meeting schedule is as follows:
6:00: General chat, discussion, and announcements
6:30: Presentation
We usually wrap up between 7:30 PM and 8:00 PM.

Please RSVP to this Meetup (use the link above), then use the online event URL to join (note: you MUST RSVP for the URL to be visible). We will send out a meeting password as we get closer to the event.

Thanks to our sponsor, Datto, for making this event possible!

#PASSSummit2020 part 5: The debrief #PASSSummit #PASSVirtualSummit

(Ed. Note: I had intended to get this out last week, but a family emergency prevented me from doing so, so this article is a week later than I’d wanted to post.)

Now that I’ve had the weekend to recover from a busy PASS Summit 2020 week, I can write about my thoughts and impressions.

Overall, I thought PASS did a good job with holding a virtual PASS Summit under trying pandemic circumstances. These are, after all, trying times, and we have to play the cards that we’re dealt. That said, there were some glitches.

I’ll start with my own presentation. I had prerecorded my session, per instructions from PASS. My initial impression was that I would do my presentation live, and the recorded session would serve as a backup in case I ran into any problems with my presentation. That turned out not to be the case. PASS used my prerecorded presentation. I was, however, required to stand by to field any questions from attendees.

So, before the appointed time, I logged in and opened a chat window. My friend Andy Levy was kind enough to join me in the video chat room, and we chatted about a variety of topics while we waited.

The appointed time arrived, so I started my video. I found that it was impossible to monitor the chat rooms to field questions and to watch the video at the same time, so I turned off the video; after all, I had no pressing need to watch my own prerecorded presentation — or so I thought. I found out, much to my chagrin, that a number of slides had no audio to go with it.

This was a big disappointment. My first instinct was to point the finger at PASS and tell them, “your technology didn’t work,” but that would’ve been disingenuous on my part. When I prerecorded my session a while back, I went back and did a quick listen of each section I recorded to make sure it was okay. When I was finished, I watched some of the presentation, but not all, and that was my mistake. I probably should’ve watched the entire presentation to make sure it was okay, but I didn’t. That was a mistake on my part to which I will own up. That said, it’s my understanding that there were a number of other presentations that also had audio problems (in fact, I tried to sit in on one that had issues, and they ended up rescheduling it — for a time that I couldn’t attend), so I’m guessing that it might not have entirely been on me.

I did post to the chat that I was available in the chat room for any questions, and a few attendees took me up on it. We ended up having a great discussion (and Andy, who also has his own ‘blog, was great with answering some questions and contributing to the conversation). In that sense, we ended up making lemonade out of the technical lemons.

That said, I haven’t yet looked at the feedback, and I don’t look forward to doing so.

A few of my friends also wrote their impressions of PASS Virtual Summit. I haven’t yet had a chance to read them, but I’m posting them here, both for you to peruse and for my own reference.

With that, here are a few of my quick thoughts regarding PASS Virtual Summit 2020.

As I mentioned earlier, these are trying times, and PASS did a decent job with Summit, given the cards they were dealt. The pandemic has affected them, as well as many of the rest of us (as of this article, I’m still looking for a job — it’s been over six months now), and PASS is dealing with those effects. As critical as I — and others — might be of PASS, I want them to survive, and I sincerely hope that they’re still around when we emerge from the other side of this pandemic.

I was not particularly fond of their decision to make use of mostly prerecorded sessions. I would have much preferred to have done my session live. PASS’s concern was with potential technical glitches with live sessions, so their thinking was that a prerecorded session would alleviate that situation. In fact, the opposite happened. The prerecorded session was the glitch, and I never had a problem with my live connection. While I understand why PASS decided to do it that way, I found the decision to be somewhat questionable.

(And if anyone reading this would like to see my presentation, I did this same presentation for the Professional Development Virtual Group back in January. You can view the recording of my January presentation here.)

Overall, I did enjoy PASS Virtual Summit, but as anyone who has attended PASS Summit or other virtual events can attest, the experience just isn’t the same. For me, a huge part of the appeal of events like these is the opportunity to network and to connect with my friends whom I don’t get to see that frequently. To their credit, PASS made accommodations with networking events and channels, but there’s only so much you can do, and only so many people you can see, with online channels. There’s something to be said about randomly bumping into one of your friends while walking down the hall.

Another big part is the travel. I love to travel, and I wish I could do more of it (pandemic aside, usually the lack of time or lack of money keeps me from traveling more). I enjoyed visiting Seattle last year, one of my favorite west coast cities to visit. This year’s Summit would’ve been in Houston, a city I’ve only visited once before.

Overall, I enjoyed PASS Virtual Summit, but it was not without its faults. Hopefully, PASS can take the feedback and lessons learned from this event, and use it to create a truly spectacular experience the next time around. Hopefully, the pandemic will be over for the next time, but these days, you can never tell.

#SQL101: Create tables from CSV flat files

With my previous article about getting into REST applications, I figured it would be a good idea for me to set up a data source so I could practice. Besides, I had reinstalled SQL Server 2019 on my machine, and I needed to import some data so that I could brush up on my SQL skills as well.

Being the baseball nut that I am, of course, I had to import baseball statistics, so I decided to reimport the most recent data from Sean Lahman’s baseball database. The last time I did this exercise, I downloaded a database format. I don’t remember what format I used (the links all say “Access” — which I don’t remember downloading), but the files I used had an .sql extension. This time, I used the comma-delimited version, which downloaded a zip file containing files with a .csv extension.

I wanted to import the files directly into my database and have them create the tables upon doing so, so I opened up my SSMS, created a new Baseball database, and looked into how to do this. After poking around a bit (and a little bit of Googling), I found that flat files could be imported by right-clicking the database of your choice (in the below example, “Baseball”), clicking Tasks, and selecting Import Flat File.

Selecting this opened an Import Flat File wizard. First, it prompted me to select the input file. (Note: if you are importing multiple files, as I did for this little exercise, the wizard is smart enough to remember your last folder when you click Browse.)

When it looks at the flat file, it gives you a preview of the data that you’re importing. Since, for this exercise, I’m importing comma-delimited flat files, it was able to put my data into nice, neat columns.

Clicking “Next” brought me to a screen where I could modify the columns. I like this option a lot, as it gives me an opportunity to set up my data schema the way I want. If you’re a SQL or database newbie, I strongly suggest that you learn about primary keys and data types and take the time to set them up at this point.

In this particular example, I set my yearID to char(4), stint to int (I will likely change this to tinyint), teamID to char(3), lgID to char(2), and pretty much everything after lgID to int. I also set my first five columns as a composite primary key and everything else to be nullable.

I must have set these columns up successfully, because when I ran it, it did so without complaining.

I wish I could say that I imported all of my flat files without a hitch, but I did run into a few that didn’t run successfully the first time. Here are some of the issues that I came across.

  • I had opened a file in Excel to check data types, forgotten to close it, and the import complained that it couldn’t work because the file was still open.
  • I miscalculated a few field sizes, and came across messages saying that my column sizes were too short (for example, setting nvarchar(10) for a column that included data with 15 characters).
  • There were a few cases where I simply had the wrong data type.
  • My Pitching table included a column for ERA, which I was surprised to see. Reason: ERA (Earned Run Average, for those of you who are baseball-challenged) is a calculated statistic, like batting average. However, batting average was not included in the Batting table. So, I set the column data type to float. However, when I tried to import it, it failed. When I looked at the data, I found entries under ERA that said “inf” (for “infinity”)*. In this case, I did some data cleansing. I got rid of these entries and saved the flat file. It then imported with no problem

(*Some of you might be wondering, how do you get an ERA of infinity? Answer: you give up runs without getting anyone out! Mathematically, you would get a divide-by-zero error for calculating ERA, but in baseball parlance, it means you give up runs and can’t get anyone out!)

So hopefully at this point, you now have an idea as to how to import flat files into a SQL Server database (and maybe even got a small taste of data types and primary keys). And hopefully, this little utility saves you a lot of grief when trying to import flat files.

May CASSUG Monthly Meeting @CASSUG_Albany #SQLUserGroup #SQLFamily

Our May CASSUG monthly meeting will be virtual! It will be on Monday, May 11, at 6 pm (EDT).

Our May speaker is Argenis Fernandez! He will talk about SQL Server Virtualization and I/O Patterns

To join the meeting, use the following link and follow the instructions: https://datto.zoom.us/j/99185518142?pwd=eDRZZXFLdis0YVBjU3BXRGdtQTdjdz09

For more information, see our Meetup link at https://www.meetup.com/Capital-Area-SQL-Server-User-Group/events/267751970/

Hope to see you online at the event!

Uninstalling SQL Server

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.

I decided to uninstall SQL Server. I don’t need it immediately, and I can reinstall it later once I get my new SSD.

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.)

#SQL101: Installing #SQLServer 2019 Developer 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.

@CASSUG_Albany March Monthly Meeting #SQLUserGroup #SQLFamily

Greetings, data enthusiasts!

Our next CASSUG monthly meeting is on Monday, March 9!

Our March speaker is our own Ken Schwartz! He will present his topic titled “Temporal Tables – what are they?”  For additional information and to RSVP, go to our Meetup event page at https://www.meetup.com/Capital-Area-SQL-Server-User-Group/events/267752251/

Additionally, Ed Pollack will present a lightning talk about SQL Notebooks!

Thanks to our sponsors, Datto, Capital Tech Search, and CommerceHub, for making this event possible!

Hope to see you there!

February CASSUG Monthly Meeting @CASSUG_Albany #SQLUserGroup #SQLFamily

Greetings, data enthusiasts!

Our next CASSUG monthly meeting is on Monday, February 10!

Our February speaker is Taiob Ali! He will present his topic titled “New features in Management Studio — Performance Troubleshooting made easier.” For additional information and to RSVP, go to our Meetup event page at https://www.meetup.com/Capital-Area-SQL-Server-User-Group/events/267111330/

Additionally, Greg Moore will present a lightning talk about PowerShell!

Thanks to our sponsors, Datto, Capital Tech Search, and CommerceHub, for making this event possible!

Hope to see you there!