Month: January 2018

MySQL RPC with Linked Server

Doug

Regular blogging is one of my goals this year. One of the areas I struggle with is what content to blog about. While I was listening to SQL Server Radio (Episode 84) with Guy Glantser (t) and Matan Yungman (t), they mentioned a new blog post series they are starting titled “Something Cool Learned Today”. These are meant for sort 1-2 paragraph blog posts about things you learned. This is the spark I needed to build some additional content for my blog.

We have a door access system that uses MySQL. We interact with the data via a SQL Server linked server so we can join MySQL tables with other SQL Server tables. Because of a bug in the vendor application, I needed a way to update some records, every few minutes, based on some records having specific column values. I knew I wanted to build a Stored Procedure (SP) similar to SQL Server but didn’t know the specific syntax for doing so. Then there was the ability to execute the MySQL SP from SQL Server, didn’t know the syntax for this either.

Off to ‘The Google’. My first stop was the MySQL documentation for CREATE STORED PROCEDURE. I didn’t know the exact MySQL version but I wasn’t trying to do anything complicated so I figured it would suffice. MySQL enforces the semicolon delimiter for all commands, something SQL Server says it will enforce in future releases (start using them now). The issue with trying to create a new SP under MySQL is the SP needs a delimiter as well as the statements inside of the SP. In order for MySQL to recognize the new SP, you need to change the delimiter temporarily to something like //, create the SP, and change the delimiter back to a semicolon. I wonder how this will pan out with SQL Server?

Now that my SP is built and execution rights were set I needed to figure out how to call the MySQL SP from a SQL Server Linked Server. Most of the time we interact with the MySQL via the OPENQUERY call. This allows us to build the statements in native MySQL so they execute faster. I found that you execute MySQL SPs with ‘call’ instead of ‘exec’ as with SQL Server but when I tried to use the CALL syntax, the query threw an error.

Msg 7357, Level 16, State 2, Line 13
Cannot process the object “CALL BugFix();”. The OLE DB provider “MSDASQL” for linked server “MySQL” indicates that either the object has no columns or the current user does not have permissions on that object.

Off to ‘The Google’. Searching for “executing MySQL stored procedures from SQL Server linked servers” landed me on a sqlservercentral.com thread with the exact issue (score!). Buried in the replies was the syntax I was looking for, EXEC(‘CALL BugFix’) AT MySQL. I was not familiar with using EXEC this way. Looking through Books On Line for EXECUTE, there it was, you are able to Execute a pass-through command against a linked server. Feeling good I headed over to SQL Server to run the command and bam, error:

Msg 7411, Level 16, State 1, Line 7
Server ‘MySQL’ is not configured for RPC.

I remembered that linked servers have lots of options under the hood. I headed over to the linked server definition and found a few RPC settings but which ones should I enable? A quick search took me to a blog post that answered the exact question. I ran the following statement:EXEC master.dbo.sp_serveroption @server=N’MYSERVER’, @optname=N’rpc out’, @optvalue=N’true’;

I re-ran my EXEC statement (no restart required) and it returned the results I was looking for: (2 rows affected).

Going into this problem I was a little hesitant to get started, procrastination set in because of the fear of the unknown. I’ve identified this problem before and the best way to get over it is to just start a small task like doing a quick Google search. Most times, the momentum of small tasks will help me get over the fear and get excited about the task at hand.

The other part of this exercise was the various error messages along the way. You can’t just give up and think it can’t be solved, someone has usually gone through the same issue before. Because of search engines, product documentation, and community bloggers, solutions are out there waiting for you to find them.

Doug Purnell
@SQLNikon
sqlnikon@gmail.com
https://sqlnikon.com

Spectre/Meltdown Baselining

Doug

This month’s #tsql2sday is being hosted by Arun Sirpal (b|t). The topic this month is how we conquered a technical challenge. The area I’ve been spending the most time in recently is baselining for the Spectre/Meltdown patches that are coming. We have a monitoring tool from Idera, SQL Diagnostic Manager, on our key production boxes but I wanted something consistent across all boxes that we could use for tracking and baselining wait types.

The Spectre/Meltdown can have a significant performance impact on servers running Windows Server OS that have intensive IO-intensive applications. SQL Server falls right into this area. We need a way to identify CPU changes across all our instances (dev/test/prod) while we apply the patches

There are plenty of queries to help identify wait types but I was looking for something that could be put into place quickly as patches from VMware and Microsoft are being released this week. I found the perfect one, sp_BlitzFirst from Brent Ozar Unlimited.

Their solution gives you the wait stat breakdown and reporting to help you identify baseline changes. When you have limited time to put something in place, these types of solutions from community partners are a great place to start. Having baseline wait stats has been on my list for years. There was always another project that was pushed to the front of the list, but now Spectre/Meltdown is at the top of my list!

I used the following blog post to get me started. We have all of our instance registered and organized under a Central Management Server. This feature allows us to run a query across multiple instances at once. I pulled down the latest First Responder Kit which includes the script to schedule and a Power BI report to analyze the results.

The next phase was how to identify a process to baseline. We don’t have any automated tools to regression test our apps (something else to work on) so I needed something to stress our SQL Server instances and record the wait types throughout the patching process. Once again the fine folks at Brent Ozar Unlimited had a blog post for this problem as well. Using SQLQueryStress, we can simulate a load on our SQL Server Instances and record the wait types. After each patch, we can execute the same load and compare the results using Power BI.

When faced with a technical challenge like Spectre/Meltdown, having a process in place to baseline your SQL Server Instances is important to show how changes to your environment can have lasting effects on performance.