One DBA's Ongoing Search for Clarity in the Middle of Nowhere


*or*

Yet Another Andy Writing About SQL Server

Wednesday, November 16, 2022

On Hybrid Events

 In my blog post last night on Days 1 and 2 of the PASS Summit, I included the following comment:

(Along with that, I think having "hybrid" events like our current Summit dilutes the impact of the in-person event, although that is a conversation for another place, and as always is my $.02)

As I expected, someone asked me about it - in this case one of the SQLPeople I greatly respect Mala Mahadevan (@sqlmal):


I want to start by saying I am not picking a fight - this is my opinion and I respect anyone who disagrees.

Like anything else, the concept of a Hybrid event is both positive in some ways and negative in others.

The biggest positive, especially in the current pandemic world but also even before that, is that there are people who can't travel - they have health reasons, they can't get time off work, they can't afford to travel, and so many more.  These people get the benefit of viewing the sessions remotely and in some cases asking questions of the presenter where otherwise they would not have been able to benefit at all.

I have no disagreement with that and I am glad that so many people like Mala are being able to benefit from that.

The main negatives I see are twofold - one for the attendees themselves and one for the overall event.

These negatives both make the same assumption - that the existence of the hybrid event "enables" people who would have attended the event in-person to not come - people who would be in Seattle but are not simply because they have an option.  I personally know of multiple people in this situation - they were waffling about whether to come or not simply to save money (not for health reasons, etc.) and ultimately decided to stay remote.

This does not judge the choice of those people - it is just what I feel is the impact of it.

The biggest direct impact I see to the attendees is the absence of those persons from the in-person experience - it is one thing to virtually raise your hand when the speaker is buzzing along or even when there is a session of questions, but it isn't possible to interact the same way as when you are in the room.  In both of the pre-cons I attended as well as in past hybrid meetings - where there are a group of people in a conference room and one or more people remotely - the remote people can't interject quickly enough compared to the people in the room. 

The other problem rising from the absence of person is the conversations that *don't* happen because the person isn't there; both days I have sat in a room full of people with people on all sides of me.  In both presentations there were side conversations that happened during the talk - conversations that raised questions that wouldn't have been asked if the two people were on their laptops at home and unaware of each other, as well as answers that were amplified because someone in the room was able to share a relevant experience that the speaker didn't have ("we ran into this problem in my shop and this is what happened to us.")

Both days I had interesting people sitting around me, and we talked about many interesting personal and professional things that weren't directly related to the session content, and those conversations wouldn't have happened if we were at home in front of our laptops.

This is true at events outside the sessions as well - how many conversations and connections *didn't* happen at the Welcome Reception last night because the remote persons were...remote.

Full disclosure - if you don't already know, I am a 100% remote employee working on a team of DBA"s who are all 100% remote for over nine years now.  At the beginning I saw some of these same issues there and wondered about it - how does it work when you don't eat lunch together or stand around in the breakroom together?  We have forged a culture and process where everyone sits in online TEAMS rooms all day every day and we randomly discuss what is going on in our lives or in the outside world live in chat; many meetings with people outside our immediate group start or end with 5-10 minutes of discussing what's going on this weekend or where someone went on vacation (just like in an in-person office).  Even with all of this some people over the years simply can't handle being remote and choose to leave because of it.  The biggest difference, and the reason it can work, is that we are a relatively small group - 8-10 people on a team or in a meeting rather than 60 people in a session - and we are all remote - there are no people "in the room" to miss interacting with. 

The impact to the event itself is a longer term issue and can to a degree be prevented *if* the in-person event continues to be more and more amazing (although there is some upper bound to how amazing something can be).

As we saw with "classic PASS" it is difficult to fund an organization on the reduced funds that virtual registration fees bring - it eventually causes issues to the program.  (Maybe Redgate has made some internal decision that "we don't care if it makes money" but I don't know.)  

This year someone commented that there are around 1,800 in-person registrations (not a confirmed number) and an unknown number of remote registrations.  The Welcome Reception last night looked like a good crowd, and it will be interesting to see how full the Microsoft Keynote room is this morning compared to my memories of Summits past. 

All in all this year seems positive - there are enough people here to make things work, even with hybrid available.

The gotcha is what comes in future years, and I know my wife is seeing this at some event she attends; right now to us in-person is again "new" and everybody is excited to be here if they possibly can - but will that continue in a year or two when it is no longer unique to be in-person again?

Let's make up some numbers for an example - we will say that the 1,800 number is correct, and that there are hypothetically 1,000 remote registrations - no problem.

Next year, some of those 1,800 people decide that they would rather save the travel expenses, or not take time off work (not judging those decisions), and there are now 1,500 in-person registrations and 1,300 reduced fee virtual registrations.

The following year, partially because the previous year's Summit was down to 1.500 people so not as impressive, another 200 people "stay home" and now you have 1,300 people on-site and 1,500 reduced fee registrations.

At some point you don't have enough people in the room anymore, and the event folds for everyone (again).

As mentioned above, the event organizers can help prevent this - the in-person experience needs to continue to be so great that people *don't* choose to stay home unless they absolutely have to - that people come to Seattle if at possible.

...and I hope that's what happens - keep it up Redgate, Microsoft, and everyone else.

That's my $.02 - thanks for reading!


PASS Summit 2022 - Pre-Cons Were Amazing!

Other PASS Summit 2022 Days - Day 1 |  Day 2 | Day 3

--

It's that time again...TUESDAY!


https://www.mememaker.net/static/images/memes/4791280.jpg

I mean...NOVEMBER!


https://tinyurl.com/33hezsje

OK fine...SUMMIT!


https://img.memegenerator.net/images/10802365.jpg

I have very much missed in-person events and I am glad that Redgate (@Redgate) has managed to continue the @PASSDataSummit framework - virtual presentations are better than absolutely nothing, but it will never (*NEVER*) be the same as being together to not only share comments and questions but also for all of the other networking and social opportunities that in-person events bring.

(Along with that, I think having "hybrid" events like our current Summit dilutes the impact of the in-person event, although that is a conversation for another place, and as always is my $.02)

Now that I am here (AWESOME!) I wanted to share the highlights of my first two days here at Summit 2022.

On Monday I participated in the "Securing the Data Platform" pre-con given by one of my SQLfriends from Iowa, Ed Leighton-Dick (@eleightondick/blog).  Over the last eight years, Ed has grown his company Kingfisher Technologies into a great database security resource.


Ed's presentation followed the same style as many other security talks I have seen - he spent the first half of his talk scaring the bejeezus out of everyone to demonstrate the importance of adopting the principles covered in the second half of the talk!  Ed covered some well known hacks from the last ten years as well as several that I had never heard of at major organizations, and discussed the specific gaps that allowed the incidents to occur


https://s3.memeshappen.com/memes/still-scared--you-should-be-meme-31789.jpg

Ed have some great tips on encryption, including encrypting connection strings, proper ways to set up service accounts to prevent inappropriate escalation of privilege, and general security implementation best practices.

Tuesday brought the pre-con I was most excited for (sorry Ed) - the SQL Server 2022 Workshop presented by Bob Ward (@bobwardms) and an all-star team of Microsoft team members.  Bob's presentations are always "brain melting" and today's did not disappoint.


The main focus of the workshop is what Bob called the SQL 2022 "Wheel of Power" - five grouping of cool new features and improvements in the new product:

courtesy Bob Ward

The presentation ran through a wide variety of items, including:

  • Linking Azure SQL Managed Instance to your traditional SQL Server instance for DR or to migrate to MI
  • Parameter Sensitive Plan Optimization with multiple query plans for a single stored procedure
  • Cardinality Estimation Feedback that leverages Query Store to determine which Cardinality Estimator is better for your high impact queries
  • Degree of Parallelism Feedback which similarly uses Query Store to discern if a lower DoP would be "good enough" for your query at lesser CPU cost
  • Contained Availability Groups to duplicate instance level objects (including SQL Agent jobs!) as part of the AG
  • Ledger to track data changes to document attempts to fraudulently change your data
  • Improved T-SQL
  • Major Intelligent Query Processing enhancements
...and so much more!


https://i.imgflip.com/4710a3.jpg


The evening closed with the Welcome Reception, which as always was a fun opportunity to reconnect with #sqlfamily that I haven't seen since before the pandemic started.

I am looking forward to the rest of the week - it's going to be great!

--

An aside - this is my first blog post in over a year; I haven't written much since COVID and have not presented at all - I hope to correct both of these going forward, although opportunities for in-person speaking are still greatly diminished after the demise of PASS SQL Saturdays; there are the "new" SQL Saturdays underwritten by the new covering organization, but there simply aren't that many events yet.

Thanks for reading everyone and hope to see you soon!


Tuesday, March 2, 2021

Disabling Non-Clustered Indexes For Fun and Profit

One of the frequently quoted ETL best practices is to disable your non-clustered indexes during a data load.  Basically the conventional wisdom is that you disable the non-clustered indexes, do your load, and then enable the non-clustered indexes, which functionally rebuilds them with the newly loaded data (a step you should be performing after a load of any size even if you *don't* disable your indexes - rebuild those newly fragmented indexes!)

So why doesn't anybody seem to do this?

https://memegenerator.net/img/instances/53039992/ponder-we-must.jpg

--

IMPORTANT NOTE <cue scary music> - do NOT disable the clustered index when doing data loads or at any other time you want the table to be accessible.  Disabling the clustered index makes the object (table or view) inaccessible.

If you disable the clustered index you are functionally disabling the object, and any attempts to access that object will result in an Error 8655:

Msg 8655, Level 16, State 1, Line 23

The query processor is unable to produce a plan because the index ‘PK_Person_BusinessEntityID’ on table or view ‘Person’ is disabled.

https://memegenerator.net/img/instances/62333367/no-please-dont-do-that.jpg

-- 

I recently had a situation with a client where they have a load process that altogether runs over 24 hours - not one day-long query but rather a string of processes that usually takes 24+ hours to complete.  The situation has worsened to the point where other things are impacted because the process is taking so long, and they asked me to check it out.

I started with all of the basics, and right away Page Life Expectancy during the relevant period jumped out:



The server has 128GB of RAM...so a PLE consistently below 1,000 is pretty low.

I used XEvents to gather queries with large memory grants, and it brought to light an issue that the client has previously considered but not handled - disabling indexes during the load.

** SIDEBAR**

The code for the XEvents session to pull large memory grant queries I used is as follows - it collects any memory grant over 8MB (which you can modify in the WHERE clause) and its calling query:

CREATE EVENT SESSION [Ntirety_MemoryGrantUsage] ON SERVER 
ADD EVENT sqlserver.query_memory_grant_usage
(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id
,sqlserver.database_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text)

WHERE ([package0].[greater_than_uint64]([granted_memory_kb],(8192))))

ADD TARGET package0.event_file(SET filename=N'Ntirety_MemoryGrantUsage',max_file_size=(256)),

ADD TARGET package0.ring_buffer(SET max_events_limit=(0),max_memory=(1048576))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON
)

The query to then retrieve the data is this - it dynamically pulls your default error log path and then queries the XEL output file from the event_file target in the session at that path:

USE master
GO

DECLARE @ErrorLogPath nvarchar(400), @XELPath nvarchar(500)

SET @ErrorLogPath = (
SELECT LEFT(cast(SERVERPROPERTY('ErrorLogFileName') as nvarchar(400)),LEN(cast(SERVERPROPERTY('ErrorLogFileName') as nvarchar(400)))-8)
)

SET @XELPath = @ErrorLogPath+'Ntirety_MemoryGrantUsage*.xel'

SELECT DISTINCT *
FROM 
(
SELECT 
  DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_data.value(N'(event/@timestamp)[1]', N'datetime'))as Event_time
, CAST(n.value('(data[@name="granted_memory_kb"]/value)[1]', 'bigint')/1024.0 as DECIMAL(30,2)) AS granted_memory_mb
, CAST(n.value('(data[@name="used_memory_kb"]/value)[1]', 'bigint')/1024.0 as DECIMAL(30,2)) AS used_memory_mb
, n.value('(data[@name="usage_percent"]/value)[1]', 'int') AS usage_percent
, n.value ('(action[@name="database_name"]/value)[1]', 'nvarchar(50)') AS database_name
, n.value ('(action[@name="client_app_name"]/value)[1]','nvarchar(50)') AS client_app_name
, n.value ('(action[@name="client_hostname"]/value)[1]','nvarchar(50)') AS client_hostname
, n.value ('(action[@name="server_principal_name"]/value)[1]','nvarchar(500)') AS [server_principal_name]
, n.value('(@name)[1]', 'varchar(50)') AS event_type
, n.value ('(action[@name="sql_text"]/value)[1]', 'nvarchar(4000)') AS sql_text
FROM 
(
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(
@XELPath
, NULL
, NULL
, NULL)
AS Event_Data_Table
CROSS APPLY event_data.nodes('event') AS q(n)
) MemoryGrantUsage
ORDER BY event_time desc

** END SIDEBAR **

Looking at the queries that were the top memory consumers over the time period led to some pretty basic queries:

INSERT INTO "dbo"."Junk" 
SELECT "UID"
, "ContainerId"
, "CategoryCode"
, "CategoryName"
, "CategoryDataValue"
, "CharacteristicCode"
, "CharacteristicName"
, "CharacteristicDataValue"
, "DimensionCode"
, "DimensionName"
, "DimensionDataValue"
, "OptionId"
, "NumberOfItems"
, "IsStandard"
, "LanguageId"
, "Market"
, "VehicleType"
, "YearMonth"
, "BatchRefreshDate"
, "CreatedBy"
, "CreatedTimestamp"
, "ModifiedBy"
, "ModifiedTimestamp"
, "BatchId" 
FROM "dbo"."Junk_Stg" 
where "Market" = 'XYZ'       
  
https://media.makeameme.org/created/so-simple-right.jpg

The immediate problem to me was that memory grants usually come from SORT and HASH operations (check out Erik Darling's great description at https://www.erikdarlingdata.com/starting-sql/starting-sql-memory-grants-in-execution-plans/) and this query doesn't obviously do any of that - it is a very straightforward INSERT...SELECT from one table into another, but the query plan looks like this:

 
Pretty crazy right?  With all of those SORT's, no wonder SQL Server wants to give this query a giant memory grant!

But why are there six different SORT operations on this one basic INSERT...SELECT?

Then I realized this is why:


Six SORT's...for SIX non-clustered indexes!

Inserting rows into a table with enabled non-clustered indexes requires a SORT to add rows to that index - the INSERT can't simply dump the rows on to the end of the index, but has to sort the input to match the indexes sort.

As a test I created a copy of the target table that I could tweak the indexes on, and a second copy without the indexes in place, and my INSERT...SELECT query plan against the No Index copy looked a little bit better:


As I test I copy-pasted the two INSERT...SELECT's into a single query window and pulled the plans for each so that I could compare them:


The Query Cost numbers in Management Studio always have to be taken with a grain of salt, but for comparison's sake consider - the six-way insert costs almost four times as much as the no-index query!

To test further I disabled three of the indexes on my copy table and ran the plan again:


Bingo - six streams now down to three for the three remaining enabled indexes.

I disabled the rest of the non-clustered indexes and:


https://memegenerator.net/img/instances/63334945/booyah.jpg

For one final comparison I ran the double query against  my "Indexes All Newly Disabled" copy and my "No Index" copy together:


Spot on!

--

As I mentioned above the cost to this is that at the end of the process you have to have time to enable the indexes again, which does a rebuild of that index.

As always Your Mileage May Vary, as the direct impact will relate to the size of the table, the number of non-clustered indexes in place, and multiple other items - but check it out, especially if your process runs longer than you want - this may be the problem!

Hope this helps!

Monday, February 8, 2021

Unable to Connect to Target Server? But, I'm Already Connected!

I am working on an Azure SQL Database migration from an on-premises VM, and at the client's request I am working through the BACPAC Import/Export process rather than using Azure Database Migration Service.

https://i.imgflip.com/2t69mm.jpg
I exported the database into a BACPAC file, and went to perform the Import operation into Azure, using the wizards in SSMS

(Awesome step-by-step instructions at https://www.mssqltips.com/sqlservertip/5255/learn-how-to-migrate-a-sql-server-database-to-sql-azure-server/ - thanks Vitor Montalvão!)

The problem is my Import operation failed with an error that didn't make any sense.  To start the process you connect to you Azure SQL Server in Management Studio, and then run the Import Data-Tier Application wizard:

..and point at you BACPAC file to import, providing details on what you want the new database to be called, what service level it needs to be in, etc.  At that point the import runs, and everything is slick, right?

"Unable to connect to target server"

Um...I was already connected to the server to get *this* far!

After some poking around, I tripped over several references to a potential "bug" related to importing BACPAC files, as described here https://stackoverflow.com/questions/19990779/unable-to-connect-to-target-server-when-restoring-bacpac-file-to-sql-server-20 

When I checked, sure enough the Management Studio on this server was 2014.

https://media.tenor.com/images/258a205ccd6bfa979a3505e95f1fcda7/raw

I went to https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms/ and downloaded the current Management Studio and applied it to the server, and:

If you can keep your tools updated - in most cases the tools are backwards compatible so unless you have written custom plug-ins, etc you are likely OK - also most recent versions let you install multiple versions side-by-side so you can install a new version without deleting the old one.

Hope this helps!


Friday, January 15, 2021

Another One Bites the Dust - PASS is Gone, so...What Now?

There has been a lot of noise over the last few weeks since it was announced that due to financial constraints related to canceling the in-person Summit due to COVID-19, PASS would cease to exist as of January 15, 2021. 

After reading the release and a few tweets, I paused and considered.

As I read through the various materials, I was struck by several distinct emotions...

https://tenor.com/view/blue-cry-sad-bad-day-gif-13423780

My initial response was Sadness; I knew when the decision was made (and basically had to be made) to move Summit from in-person to all-virtual that there would be repercussions, especially financial ones.  I have never been on the Board or anything like that, but I have always paid enough attention to be aware that a majority of the funding that kept our no-membership-fee organization going came from Summit registrations, and you can't charge in-person experience fees for a virtual conference.  I have been to a half dozen Summits over the years, presenting twice, presented at numerous SQL Saturdays, and been a part of I-don't-know-how-many in-person and virtual user groups.  The first thing through my mind was that all of that was...gone.


The more I read, the more I felt Anger and Disgust; over the years there has been an ongoing conflict within the SQL Server community regarding the operations of PASS and its management company Christianson & Company (C&C).  There have been numerous snipes back and forth on whether C&C did a good job, did they cost too much money, did they care about the community, etc. etc. etc.  I have always stayed out of these discussions, but also always been annoyed how some people couldn't make the distinction between the workings of the organization and the volunteers that make things work.  At times I have seen people on the PASS Executive Board and other volunteers attacked because of one decision or another, and it just isn't right.  Even if a certain person participated in a decision (or made it individually themselves) there is no reason to attack someone who is giving personally of their time.  I have met many of the people who have served as PASS leadership over the years, and while they have varying personalities they have all been dedicated to supporting the organization for little personal gain, not back-room power-playing, and I salute them.

https://imgflip.com/memetemplate/63747205/inside-out-joy

Finally I arrived at a wistful sense of Joy.  I have learned so much from the various functions of PASS over the years, but even more importantly I have met and interacted with so many amazing people along the way - people I never would have met without PASS.  I blog because I saw a presentation talking about how blogging can help you learn and share your knowledge. I became a speaker as the next step down that learn and share path, and becoming a speaker is what really opened up networking to me.  For everything I have learned in a session at a SQLSaturday or User Group session, I have learned even more from someone I met at a Speaker Dinner or in the hallway between sessions.  This is true of both Community members and Microsoft employees as well - on multiple occasions I have met and had long discussions with Microsoft Product Team members who design, build, and fix the SQL Server product and I would not have done so without PASS.

So now what?  What should we feel now?

https://wallha.com/wallpaper/fear-inside-out-440608

Fear? NO!!!

There are lots of options for content post-PASS, and I have compiled a list I want to share with you!

--

The biggest single source of content I have found is meetup.com.  Many of the larger user groups have turned to Meetup during COVID while they couldn't meet in-person, and even more are springing up there now since PASS made its announcement.  All of the groups I list here are "Public" so regardless of what geographic area they are affiliated with, anyone should be able to join!

--

First are a couple of groups that have arisen from the ashes of large PASS Virtual Chapters:

Database Professionals Virtual Meetup Group - 1,453 members - Meets Biweekly https://www.meetup.com/dbavug/ 

DBA Fundamentals Group - 877 members - Meets Weekly

There are many regional/local groups on Meetup as well; I gathered this list by spending some time searching Meetup for SQL and Azure content and then checking the groups to see if they have regular meetings.  All of the groups listed here meet at least monthly and have regular meetings scheduled.  I found many groups that had hundreds of people listed as members but if they didn't have anything scheduled I didn't include them.

Here they are sorted by member count - an omission from this list does not mean anything in particular other than a group didn't show up on my search or didn't have regular meetings scheduled at the time I ran the search:

New England SQL Server User Group - 2,767 members - Meets Monthly

Azure in the ATL - 2,409 members - Meets Monthly

Northern Virginia SQL Server Users Group - 1,553 members - Meets Monthly

Triangle SQL Server User Group (NC) - 1,541 members - Meets Monthly

Arizona SQL Server User Group - 1,136 members - Meets Monthly

Sacramento SQL Server User Group - 994 members - Meets Monthly

North Boston Azure Cloud User Group - 899 members - Meets Monthly

Utah SQL Server Group - 848 members - Meets Monthly

Rhode Island SQL Server User Group - 610 members - Meets Monthly

PASSMN (Minnesota SQL Server User Group) - 596 Members - Meets Monthly

Atlanta Azure Data User Group - 589 members - Meets Monthly

CincyPASS - Cincinnati SQL Server Users Group - 476 members - Meets Monthly

Albuquerque SQL Server User Group - 354 members - Meets Monthly

Data Platform Downunder Meetup Group (Australia Time Zone) - 90 members - Meets Weekly

--

A couple of more non-Meetup links:

Data Saturdays - a potential replacement for SQLSaturdays - there are a few upcoming events scheduled already:

SQLUGs - a site to host local user group webpages being provided for *free* by Denny Cherry and Associates (website/@DCACco) as a service to the SQL Community:

--

Let me know if you find other treasure troves of information to share, and hope this helps!


https://www.lemax.net/wp-content/uploads/2018/09/Much-to-learn-we-all-still-have.jpg

Friday, December 11, 2020

Toolbox - When Intellisense Doesn't See Your New Object

I was just working on a new SQL job, and part of creating the job was adding a few new tables to our DBA maintenance database to hold data for the job.  I created my monitoring queries, and then created new tables to hold that data 

One tip - use SELECT...INTO as an easy way to create these types of tables - create your query and then add a one-time INTO clause to create the needed object with all of the appropriate column names, etc.

https://i.redd.it/1wk7ki3wtet21.jpg

SELECT DISTINCT SERVERPROPERTY('ServerName') as Instance_Name
, volume_mount_point as Mount_Point
, cast(available_bytes/1024.0/1024.0/1024.0 as decimal(10,2)) as Available_GB
, cast(total_bytes/1024.0/1024.0/1024.0 as decimal(10,2)) as Total_GB
, cast((total_bytes-available_bytes)/1024.0/1024.0/1024.0 as decimal(10,2)) as Used_GB
, cast(100.0*available_bytes/total_bytes as decimal(5,2)) as Percent_Free
, GETDATE() as Date_Stamp
INTO Volume_Disk_Space_Info
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs      
INNER JOIN sys.master_files AS mf WITH (NOLOCK)      
ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id  
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID)   
order by volume_mount_point

I thought at this point that everything was set, until I tried to write my next statement...


The dreaded Red Squiggle of doom!

I tried to use an alias to see if Intellisense would detect that - no luck.


Some Google-Fu brought me to the answer on StackOverflow - there is an Intellisense cache that sometimes needs to be refreshed.

The easiest way to refresh the cache is simply a CTRL-SHIFT-R, but there is also a menu selection in SSMS to perform the refresh:


Edit>>Intellisense>>Refresh Local Cache

In my case, once I performed the CTRL-SHIFT-R, the red squiggles disappeared!

https://memegenerator.net/img/instances/61065657/you-see-its-magic.jpg

Hope this helps!


Tuesday, December 1, 2020

Would You Pass the SQL Server Certifications Please? What Do You Mean We're Out?

I have held various certifications through my DBA career, from CompTIA A+ certification back when I worked help desk (I'm old) through the various MCxx that Microsoft has offered over the years (although I never went for Microsoft Certified Master (MCM), which I still regret).

I have definitely gotten some mileage out of my certs over the years, getting an interview or an offer not just because I was certified, but rather because I had comparable job experience to someone else *and* I was certified, nudging me past the other candidate.

I am currently an MCSA: SQL 2016 Database Administration and an MCSE: Data Management and Analytics, which is pretty much the top of SQL Server certifications currently available.

I also work for a company that is a Microsoft partner (and have previously worked for other Microsoft partners) and part of the requirements to become (and stay) a Microsoft partner is maintaining a certain number of employees certified at certain levels of certification dependent on your partnership level.

I completed the MCSE back in 2019, and my company is starting to have a new re-focus on certifications (a pivot, so to speak - I hate that term but it is accurate), so I went out to look at what my options were.  We have two SQL Server versions past SQL Server 2016 at this point, so there must be something else right?

On top of that, the MCSA and MCSE certs I currently have are marked to expire *next* month (January 2021 - seriously, check it out HERE)...so there *MUST* be something else right - something to replace it with or to upgrade to?

I went to check the official Microsoft certifications site (https://docs.microsoft.com/en-us/learn/certifications/browse/?products=sql-server&resource_type=certification) and found that the only SQL Server-relevant certification beyond the MCSE: Data Management and Analytics is the relatively new "Microsoft Certified: Azure Database Administrator Associate" certification (https://docs.microsoft.com/en-us/learn/certifications/azure-database-administrator-associate).  

The official description of this certification is as follows:

The Azure Database Administrator implements and manages the operational aspects of cloud-native and hybrid data platform solutions built with Microsoft SQL Server and Microsoft Azure Data Services. The Azure Database Administrator uses a variety of methods and tools to perform day-to-day operations, including applying knowledge of using T-SQL for administrative management purposes.

Cloud...Cloud, Cloud...Cloud...(SQL)...Cloud, Cloud, Cloud...by the way, SQL.

Microsoft has been driving toward the cloud for a very long time - everything is "Cloud First" (developed in Azure before being retrofit into on-premises products), and the company definitely tries to steer as much into the cloud as it can.

I realize this is Microsoft's reality, and I have had some useful experiences using the cloud for Azure VM's and Azure SQL Database over the years...but...

There is still an awful lot of the world running on physical machines - either directly or via a certain virtualization platform that starts with VM and rhymes with everywhere.

As such, I can't believe Microsoft has bailed on actual SQL Server certifications...but it sure looks that way.  Maybe something shiny and new will come out of this; maybe there will be a new better, stronger, faster SQL Server certification in the near future - but the current lack of open discussion doesn't inspire hope.

--

Looking at the Azure Database Administrator Associate certification, it requires a single exam (DP-300 https://docs.microsoft.com/en-us/learn/certifications/exams/dp-300) and is apparently "Associate" level.  Since the styling of certs is apparently changing (after all it isn't the MCxx Azure Database Administrator) I went to look at what Associate meant.

Apparently there are Fundamental, Associate, and Expert level certifications in the new role-based certification setup, and there are currently only Expert-level certs for a handful of technologies, most of them Office and 365-related technologies.

This means that for most system administrators - database and otherwise - there is nowhere to go beyond the "Associate" level - you can dabble in different technologies, but no way to be certified as an "Expert" by Microsoft in SQL Server, cloud or otherwise. (The one exception I could find for any sysadmins is the "Microsoft Certified: Azure Solutions Architect Expert" certification, which is all-around design and implement in Azure at a much broader level.)

--

After reviewing all of this, I am already preparing for the Azure Database Administrator Associate certification via the DP-300 exam, and I am considering other options for broadening my experience, including Azure administrator certs and AWS administrator certs.  I will likely focus on Azure since my current role has more Azure exposure than AWS (although maybe that is a reason to go towards AWS and broaden my field...hmm...)

If anything changes in the SQL Server cert world - some cool new "OMG we forgot we don't have a new SQL Server certification - here you go" announcement - I will let you know.