locked
Migration from 2008R2 to 2014; Issues with Replication RRS feed

  • Question

  • ...migrating an application from 32 bits to 64 bits

    • Must retain Windows 7 (64 bit) compatibility
    • Windows.Forms application, if it makes any difference
    • Migrating from SQL Server 2008R2 to 2014
    • Significant use of replication (publish and subscribe)

    As a first pass, I've taken a clean machine and installed SQL Server 2014 Enterprise. Then, I retrieved the source build tree from the repository (paleontology required) and ensured that all projects are x64 only.

    • Clean machine to ensure that there are no 2008R2 "ghosts"

    My hope was that I could just re-target the projects' reference to the appropriate assemblies.

    What happened to the replication assemblies in GAC_MSIL? I'm pretty confident I asked for everything to be installed, yet, Rmo and Replication are just not there. Replication.BusinessModel and enum (not the actuals names; I'm on another machine).

    Any advice?

    Thanks.


    WeBMartians

    Thursday, July 28, 2016 10:53 PM

All replies

  • Hi Martian,

    Could you please share more information to us for analysis? We want to know that which Operation System your SQL Server 2014 is installed on and if you just want to find Microsoft.SqlServer.Replication.dll and Microsoft.SqlServer.Rmo.dll from GAC_MSIL?

    If you cannot find these .dll file from GAC_MSIL, you can find Microsoft.SqlServer.Replication.dll from ‘C:\Program Files\Microsoft SQL Server\120\SDK\Assemblies’ and  Microsoft.SqlServer.Rmo.dll from C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies.

    You can also add them to GAC_MSIL by referring to this article.

    Regards,
    Teige

    Friday, July 29, 2016 8:14 AM
  • Hi, Teige!

    Thank you for your response. I do indeed have additional information:

    • more matchings of C# usings to csproj references
    • lists of what's in my cleansed machine's GACs

              and

    • dumpbin summary of the DLLs installed by the SDK
      I find this somewhat perplexing as many of the DLLs in x64 folders are definitely 14C (32 bit).

    How do I get this to you?

    Best!


    WeBMartians

    Monday, August 1, 2016 2:43 PM
  • While long, I hope this post provides others with some approaches in case of having to accomplish the same task.

    As described in the original post (admittedly, not very clearly), I have been tasked with upgrading a laboratory / medical product that uses remote databases.

    I found that the typical practices of building and installing executable(s) and libraries is insufficient.
    In this case, it is not feasible (maybe even impossible) to create a new, monolithic deployment package and say to a client, "Here ya go! Knock yourself out!"
    With the push to upgrade to Windows 10, I am confident that I am not the only one faced with such a task.
    If your deployments are "vanilla," read no further and enjoy your day.
    If you have to do something similar, this post, even with its questions, may help.
    If you are proficient in this matter and can answer some of the remaining questions, please do.

    The new version must operate in and take advantage of 64 bit environments under either Windows 7 Professional / Enterprise (possibly, also, Ultimate) or Windows 10 Pro / Enterprise.
    More explicitly, the requisite environment is one in which only 64 bit Windows 7 or Windows 10 hosts are recognized, and customers are strongly encouraged to migrate to Windows 10, exclusively.
    The distinction is subtle and maybe it can be restated as, "As a customer, your constellation of systems may include Windows Vista, XP and maybe even NT machines.
    As if non-existent, they will be ignored by the new software."

    The code tree is, essentially, 100% C# and built under Microsoft Visual Studio 2010.
    Upgrading to MSVS 2015 was rejected because of the fear of incompatibilities with the current source-build tree and fear of analogous issues with Windows 7 deployments.
    (Pleasantly, these fears have proven to be unfounded.
    However, changing to MSVS 2015 at this moment would be significanlty inconvenient.)

    The product uses Microsoft SQL Server databases, notably, MSSQL's Replication facility.
    The legacy MSSQL is 2008R2; deployments will be required to upgrade to at least 2014, although, on any machine, a 2014 instance may be present alongside legacy 2008R2 and maybe even 2005 instances ... or even, newer, 2016 specimens.
    MSSQL 2016 installation rejects under Windows 7 (can't just upgrade to MSSQL 2016).
    Pre-2014 MSSQL installation under Windows 10 is strongly deprecated (can't just stay with MSSQL 2008R2).

    Importantly, as of MSSQL 2012, Microsoft has withdrawn support for some Replication-related elements: Remote Management Objects (can't just upgrade to MSSQL 2012).
    This is the first of a few of the risks.
    Rewriting those portions of the code that use Microsoft.SqlServer.Rmo looks to be expensive: the recommended transfer of actions from C# to Transact-SQL scripts does not support some imperatives, and creating script-based equivalent actors may require not just a lot of coding but "invention."
    Relying on the appearance of invented solutions is a sure path to project failure.

    Actions Taken:
    1- Upgrade the MSVS builds to produce only x64 products.
    This is relatively straight-forward and is not much different from the experience of ensuring an AnyCpu product.
    Be advised that MSVS has an annoying habit of automatically modifying your solution and project specifications to default to Mixed Platforms rather than x64.
    Just be vigilant and resist the urge to use expletives.

    2- Replace project references to MSSQL 2008R2 assemblies with references to MSSQL 2014 assemblies.
    Gathering the DLLs into some collection (such as a software development kit) is non-trivial.
    Not all of the necessary DLLs are made available by the standard MSSQL installation.
    You will certainly have to poke around the internet and find trusted installations for a variety of DLLs.
    Be advised, the key term is "TRUSTED installations."

    Once acquired, these DLLs become part of your build tree and are referenced, as appropriate, by each project.
    They are not deployed as part of your installation; they are expected to be installed, already, on the target machine(s) ... more about that in a bit.

    In my case, I needed:
    Microsoft.SqlServer.ConnectionInfo.dll
    Microsoft.SqlServer.Management.Sdk.Sfc.dll
    Microsoft.SqlServer.Replication.dll
    Microsoft.SqlServer.Replication.BusinessLogicSupport.dll
    Microsoft.SqlServer.Rmo.dll
    Microsoft.SqlServer.Smo.dll
    Microsoft.SqlServer.SmoExtended.dll
    Microsoft.SqlServer.SqlEnum.dll
    and all had to be, in some manner, x64 compatible.

    Remember the above note about the removal of support for Rmos?
    I could not find anything that would give me an x64 version 12 (MSSQL 2014) copy of Microsoft.SqlServer.Rmo.dll.

    Instead, I installed MSSQL 2014 (ensuring that the desired features are installed) and started poking around in the Global Assembly Cache.

    The GAC is in %WinDir%\assembly\ (not entirely true; details, later) but don't go trying to explore that folder with Windows Explorer.
    What you will see are assemblies and versions rather than folders and files.
    What you need is the Command Prompt
    [...or, even better, the Visual Studio Command Prompt.
    If you're not familiar with VS Command Prompt, make plans to become proficient.
    It is part of every MSVS installation and looks a lot like the so-called DOS command prompt ... except that it can run a variety of very useful tools.]

    Using the prompt, go explore %WinDir%\assembly\ as follows to find Microsoft.SqlServer.ConnectionInfo.dll:
    >cd "%WinDir%\assembly"
    C:\Windows\assembly>dir /B /S Microsoft.SqlServer.ConnectionInfo.dll
    C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll
    [As you can see, I've compacted some of the text to save space.]

    Note that the desired DLL is in the GAC_MSIL sub-folder.
    %WinDir%\assembly\ holds GAC, GAC_32, GAC_64 and GAC_MSIL sub-folders, among others.
    We need x64 DLLs, not so much for the code but for the assembly references.
    Thus, GAC_64 is the desired source, but GAC_MSIL sourced DLLs will do just as well.
    The DLLs from the GAC and GAC_32 trees will be rejected during the build.

    Copy the DLLs to your build's DLL staging area; for example,
    XCOPY "%WinDir%\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll" "C:\MyDLLs\" /Y

    Where is Microsoft.SqlServer.Replication.dll?
    C:\Windows\assembly>dir /B /S Microsoft.SqlServer.Replication.dll
    File Not Found

    It turns out that a little announced change in the GAC moved some elements to %WinDir%\Microsoft.NET\assembly\.
    XCOPY "%WinDir%\Microsoft.NET\assembly\GAC_64\Microsoft.SqlServer.Replication\v4.0_12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Replication.dll" "C:\MyDLLs\" /Y
    [Note also that the version is specified as v4.0_12.0.0.0 versus the expected v12.0.0.0.

    Once you have a complete set of DLLs, you can incorporate them into your build tree and, in MSVS, reference, by project, them as necessary.

    [Right here, I advise again, that you become familiar with Visual Studio Command Prompt and the following command:
    dumpbin /headers aDLLFile | find "machine"
    If it yields a code of 8664, you have an x64 DLL.
    If it yields 14C, you have a 32 bit DLL.
    Be advised that GAC_MSIL sourced DLL files may yield 14C (indeed, probably will) but are still OK to be used as referenced assemblies by your build.]

    QUESTION 1: The DLLs exist; why is there no SDK installation for some of them?
    There is the Microsoft.SQLServer.Replication.2014.ManagementPack MSI and a variety of updates (KB3158271 and KB3171021 to name two).
    Why no SDK?

    QUESTION 2: I'm still testing so I don't know if all is as it should be, but I was able to find an x64 version 12 of Microsoft.SqlServer.Rmo.dll as
    %WinDir%\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.Rmo\v4.0_12.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Rmo.dll.
    Microsoft still states that Rmos are deprecated.
    What's up with that?

    QUESTION 3: Let's say you build, referencing version 12 of Microsoft.SqlServer.somethingOrOther.dll, and the target machine has not MSSQL 2014 but MSSQL 2016 (version 13?) installed.
    Is version 13 used instead or does the application crash?

    QUESTION 4: If the target machine (with MSSQL 2014 installed) makes a SQL Server call to another machine running MSSQL 2016, is the call properly honored even though the client is downlevel?
    Assuming yes, what happens if the remote server is running MSSSQL 2008R2 (assuming that a compatible request is made)?

    QUESTION 0: The above is obviously not "best practices."
    It was what worked (or seems to work so far).
    Is there a better way?

    Thank you.


    WeBMartians


    Thursday, August 11, 2016 11:21 PM