June 26, 2015 at 9:52 am
I need to do some Performance tweaking on a SSIS package. It is heavily reliant on features that are only supported by OleDb (e.g variables being used as sql parameters, lookups). OleDb does not support MultiSubnetFailover using a AlwaysOn listener so there are long delays iterating through the ip addresses in the availability group.
My idea was to add a step with a Execute SQL task with a ADO.Net connection to the listener and somehow get the name of the primary instance. My first question - how do I get the name of the primary instance?
The second part of this is I would have to reconstruction the OleDb ConnectionString on the fly (using the direct name of the Primary Instance). Is there a automated way to generate an OleDb connection string (e.g. via .Net) by just inputting values (Instance,DatabaseName,login, password, etc). I can probably do this by hand, but I'd rather avoid any gotchas that i might miss from testing.
June 26, 2015 at 12:34 pm
Not sure about your second question but for getting the primary instance with an Execute SQL task could you not just run the following?
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS');
Joie Andrew
"Since 1982"
June 26, 2015 at 2:26 pm
If I understand correctly, you might be able to use the System.Data.OleDBConnectionStringBuilder class to construct the connection string. There's equivalent classes for SQL Native Client as well in case that would be useful.
Looking at it you pretty much have to know the attribute names ahead of time (i.e. "Data Source", "Initial Catalog", etc), so I'm not sure how much value this has beyond simple string concatenation, but hopefully it's helpful.
Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software
June 26, 2015 at 3:32 pm
Also, if you want to make sure that you always get the name of the primary replica (in case you connect to the listener with read-only intent and have that set up to go to a secondary), you could run this:
SELECT primary_replica
FROM sys.dm_hadr_availability_group_states
Cheers!
June 29, 2015 at 10:01 am
Joie Andrew (6/26/2015)
Not sure about your second question but for getting the primary instance with an Execute SQL task could you not just run the following?
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS');
This doesn't provide the instance name if it is a named instance. Jacob's response is more of what I was looking for.
Still thank you for the post, I learned something new by using this.
July 1, 2015 at 3:57 pm
I believe you can use the @@SERVICENAME global variable to find the instance name, though you'd have to account for the fact it returns MSSQLSERVER for the default instance.
Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software
July 1, 2015 at 4:19 pm
Just in case anyone was curious, here's how it turned out:
I have to provide two connection strings, one for ADO.Net (with MultiSubnetFailover) and one for OleDb.
I use the ADO.Net connection to connect to an Execute SQL task to get the name of the primary using the dmv Jacob provided with intent on replacing the Data Source in the provided OleDb connection string.
However I ran into a small hurdle here - 1) we use multiple domains, and 2) we use multiple ports. The DMV provide only supplies HOSTNAME\INSTANCE format so I had to do add code which added any additional information (e.g. domain suffix, portnumber) to the information provided by the DMV.
Example: If the original OleDb connection string has "Data Source=DbListener.il.global.company.com,1912"
and the ADO.Net Execute SQL task connects to the listener and using the dmv, it returns "DbNode1\Instance1" for the primary instance...then....
I need my new OleDb connection string to have "Data Source=DbNode1.il.global.company\Instance1,1912"
So it took a bit of parsing logic to break off each part, but it's pretty simple if you just split by the delimiters.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply