CLR Running in Mirrored Environment

Question

CLR Running in Mirrored Environment

jzelenda

Valued Member

Points: 52
More actions
January 27, 2009 at 8:39 am

#196477

I've been trying to do some research on this topic and I'm having a hard time finding any information on the subject. My question is I have a Sql Server mirrored environment and I'm setting up CLR on my primary server. Will this cause issues with my secondary server? Is it possible to run CLR in a Sql Server mirrored environment?
Thanks,

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

What safety level are your assemblies? That is what determines the level of headache you will have at failover and during deployments.

jzelenda Valued Member Points: 52 More actions · Answer 2

My .dll file is set to "WITH PERMISSION_SET = EXTERNAL_ACCESS;"

and my XmlSerializers.dll is set to "WITH PERMISSION_SET = SAFE;"

I'm assuming the 2nd one will cause issues?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

Nope. SAFE Assemblies work just fine. How much trouble you have with the EXTERNAL_ACCESS one depends on how you deployed it. IF you used a key to sign the assembly, then you should be ok in a failover so long as the Login on both the Principle and the Mirror have the same SID. You can accomplish this by scripting the Assymetric Key from the principle, and then scripting the Login from the Principle using the scripter on this link:

http://support.microsoft.com/kb/918992

When you do this the SID's match between the Logins on the Servers, so when the failover happens, the Database User in the SQLCLR database maps directly to the Login by SID and it all comes up nicely.

If you used TRUSTWORTHY = ON, you will be manually intervening in the event of a failover, and there is no work around for that. You will have to remap the dbo and then retrust the database because TRUSTWORTY is set OFF when recovery happens to the mirror. See the following blog post:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/03/06/clr-safety-issues-after-database-restore-to-new-server.aspx

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 4

What would be even more interesting is finding out what the CLR actually does.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

Based on External_Access and a Serializer assembly, it would most likely be a webservice call, which are becoming more and more normal place.

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 6

I was hoping the op would tell me... but Ok... what does the CLR actually do? Remember, I'm not much into GUI's or WebServices.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

Jeff Moden (1/28/2009)
I was hoping the op would tell me... but Ok... what does the CLR actually do? Remember, I'm not much into GUI's or WebServices.

Not that your personal preference for CLR has much to do with the question being asked, or anything you might add the actual answer to the question being asked will sway the OP one way or another, but more and more, the use of a webservice to enforce constraints or validate data on load in the database engine, is taking root. Of course that could probably open a different argument about where is data integrity supposed to be controlled in the app or in the database. If you say the database, the a CLR function making a webservice call to enforce data integrity can be an acceptable thing.

Like it or not, this is becoming considered by a number of people to be a valid use for CLR.

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 8

Heh... and we still don't know what the CLR does, do we? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 9

jzelenda,

What does the CLR actually do, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

Does it really matter for the context of the question posed in this thread?

Your aversion to CLR takes every thread in this forum completely off topic and clutters the workspace at times. I can definately see making a post where something is obviously not correctly being implemented or where it is obviously prone to problems, but just chasing every CLR question like you do isn't very helpful to people who search for actual solutions to questions like this one.

jzelenda Valued Member Points: 52 More actions · Answer 11

Sorry it took so long to reply. The CLR actually calls a Webservice that in return based on what's being passed to it can execute various stored procedures. The reasoning for going through a webservice is because the process that is executing is within an transaction statements. So this allows us to load error log tables without the transactions be rolled back when there is an error. Hopefully that makes sense.

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 12

Actually, that does. Thank you for taking the time to explain.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 13

Jonathan Kehayias (1/28/2009)
Does it really matter for the context of the question posed in this thread?
Your aversion to CLR takes every thread in this forum completely off topic and clutters the workspace at times. I can definately see making a post where something is obviously not correctly being implemented or where it is obviously prone to problems, but just chasing every CLR question like you do isn't very helpful to people who search for actual solutions to questions like this one.

As a matter of fact, it does, Jonathan. People who don't know how to use SQL Server are writing CLR's to make up for their ignorance of T-SQL. Most CLR's are a lot slower and tougher on resources than T-SQL to do the same job. My aim is not "chasing" CLR's... it's trying to educate people. I do the same thing with people who use Cursors for the very same reason... it's usually a mistake to use them. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)