locked
MS SQL migration to 64bit, c# assembly question RRS feed

  • Question

  • We are going to migrate MS SQL 2005 32 bit server to MS SQL 2008 64bit.

    There is an assembly on the 32bit DB server. The stored procedure is made in c# as part of the whole ASP project (build for Any CPU). version of IIS is 6.0, running on ms server 2003 SP2. what is necessarily to made the assembly to work correctly on the new 64bit server? 

    Do I have to build the whole project for the 64bit CPU and just copy (where exactly? depends the copy path from something?) the database assembly to the new 64bit DB server? Or do I have to register something somewhere?

    Here is the assembly:

    USE [Crdx]
    GO

    /****** Object:  SqlAssembly [CrdxDB]    Script Date: 06/06/2012 11:24:51 ******/
    CREATE ASSEMBLY CrdxDB
    AUTHORIZATION [DOM\DB]
    FROM 0x4D5A9....
    WITH PERMISSION_SET = SAFE

    GO

    ALTER ASSEMBLY CrdxDB
    ADD FILE FROM 0x4D69...
    AS N'CrdxDB.pdb'

    GO

    EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'ASSEMBLY',@level0name=N'CrdxDB'
    GO

    EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyProjectRoot', @value=N'C:\Crdx\CrdxCommonPlatform\CrdxDB' , @level0type=N'ASSEMBLY',@level0name=N'CrdxDB'
    GO


    • Edited by tomas.t Wednesday, June 6, 2012 11:18 AM
    Wednesday, June 6, 2012 11:00 AM

Answers

  • If you restore a backup of the database on the new server, there is no need to register anything. Unless, that is, the assembly requires elevated permissions, that is EXTERNAL_ACCESS or UNSAFE. In that case, you need to do the same thing that you did on the old server on the new server.

    But before you ask about the, review the current permission set for the assembly by querying sys.assemblies.

    Or are you talking about an extended stored procedure? Such a procedure is typically written in C++, not C#.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Iric Wen Thursday, June 14, 2012 9:32 AM
    Wednesday, June 6, 2012 1:49 PM
  • Thx everybody for answer.  There was problem on my site, that I didn't understand correctly, what it means to have SQl procedure written in c# and what it means to register the assembly. After reading some topics on msdn, everything is now clear for me.

    and to the registering in this case. te assembly was already registered when restoring the DB.  you can check it running:

    select * from sys.objects 
    where type_desc in ('CLR_STORED_PROCEDURE','CLR_SCALAR_FUNCTION')
    order by name

    • Marked as answer by tomas.t Thursday, June 14, 2012 9:45 AM
    Thursday, June 14, 2012 9:44 AM

All replies

  • ... (build for Any CPU)  ... what is necessarily to made the assembly to work correctly on the new 64bit server? 

    Hello,

    If you compile it with target platform = "Any CPU", then the assembly should work also on 64 bit server without any modification.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Wednesday, June 6, 2012 11:18 AM
  • thx. and where to copy the assembly file, which is in the 32bit DB(the content of the file posted in question)? Does the copy path depends on somethink? Actually I become this question from the DB admin. First I thought, that it will be enough just script the assembly as Create, and run this script on the new 64bit server.

    the admin wrote me, that he copied the assembly to this path. Should it be ok?

    U:\MSSQL10_50.CrdxCP\MSSQL\dll\CrdxDB.dll


    • Edited by tomas.t Wednesday, June 6, 2012 11:25 AM
    Wednesday, June 6, 2012 11:21 AM
  • You can copy the assembly to disk and run CREATE ASSEMBLY from disk, or you can script it and load it through binary string. The latter appears simpler.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, June 6, 2012 12:07 PM
  • I got another question from DB admin. There was an c# DLL on the database server. The DLL is an stored procedure programmed in c#. he toke the DLL and copied it to the new DB server. But he need to register the DLL on the new server and doesn't know how, me neither.
    Wednesday, June 6, 2012 1:20 PM
  • If you restore a backup of the database on the new server, there is no need to register anything. Unless, that is, the assembly requires elevated permissions, that is EXTERNAL_ACCESS or UNSAFE. In that case, you need to do the same thing that you did on the old server on the new server.

    But before you ask about the, review the current permission set for the assembly by querying sys.assemblies.

    Or are you talking about an extended stored procedure? Such a procedure is typically written in C++, not C#.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Iric Wen Thursday, June 14, 2012 9:32 AM
    Wednesday, June 6, 2012 1:49 PM
  • Thx everybody for answer.  There was problem on my site, that I didn't understand correctly, what it means to have SQl procedure written in c# and what it means to register the assembly. After reading some topics on msdn, everything is now clear for me.

    and to the registering in this case. te assembly was already registered when restoring the DB.  you can check it running:

    select * from sys.objects 
    where type_desc in ('CLR_STORED_PROCEDURE','CLR_SCALAR_FUNCTION')
    order by name

    • Marked as answer by tomas.t Thursday, June 14, 2012 9:45 AM
    Thursday, June 14, 2012 9:44 AM