none
SQL server replication and Entity Data Model RRS feed

  • Question

  • For high availability, our company is adding a new webserver and sql server. We will be doing P2P replication for making the two SQL servers consistent. However, we are using EDM in our application and it is based on the existing server. If we add another SQL server, do I need to create another set of EDM for the second database. Maintaining 2 edmx files will be a nightmare. And may be in future for scaibility purposes we add another SQL server. There must be a better way then having a separate EDMX file for each SQl server. Furthermore, for the already existing code that accesses the database using EDM, how should I modify it so that it selects the correct database and hence it's EDM. Presently, I have code like:

    using (var context = new MyCOmpanyModel.MyCompanyEntities()) { .... }

    I don't want to change this on every page which accesses EDM. There must be a better way of selecting the SQL server (or Entities) and setting them may be in a base Page class or anything like that.

    Any help will be appreciated.

    Wednesday, October 19, 2011 10:34 PM

Answers

  • Yes, you could create a session variable or application scope variable with the correct connection string in it.  Then, you can pass that as the constructor to your objectcontext when you instantiate it.   
    Tom Overton
    Thursday, October 20, 2011 7:42 PM

All replies

  • Hi seemashah74,

    Even if your company is doing replication you should still be fine to stay linked to the original SQL Server (the one in your web.config). Since you say they are adding a new web server I assume your application is a web app?  If that's the case, the web.config can be changed by the server administration so that one web server points to the new sql server and your original web server still points to the original sql server.  This would be configured in the web.config files on each web server but your code doesn't need any changes to accommodate this.

    However, if indeed you do need to change the connection string for the EDMX inside your application, as long as the database structure of each database matches the original design structure for the EDMX then you can just pass in the connection string when you instantiate your context.  It accepts an optional connection string parameter which will override reading the hard-coded one in the config file:

    using (var context = new MyCOmpanyModel.MyCompanyEntities(connectionString)) { .... }
    


    You could populate this global connectionString at application startup so it uses that instead of the one connection string in the config file to give flexibility in case you need to connect to a different server on the fly. 

     


    Tom Overton
    Thursday, October 20, 2011 9:08 AM
  • Tom thanks for the reply. Yes, it is a web application and the two web servers can switch between the two sql servers. So  i need to change the connection string for the EDMX in my application. This is what I have got until now:

    <add name="MainDB"   connectionString="metadata=res://*/MainDB..."   providerName="System.Data.EntityClient"   /> 
    <add name="OtherDB"   connectionString="metadata=res://*/MainDB..."   providerName="System.Data.EntityClient" /> 
    

     

    string connectionStringKey;   if(Server == 1)     connectionStringKey = "MainDB"; else    connectionStringKey = "OtherDB";  
    
            using (var context = new MainDB("name=" + connectionStringKey))         {         } 
    


    So according to your suggestion I should do the above server check in application start in global.asax file and set the connectionStringKey may be in session. Then use the connectionStringKey value from session in the ContextObject constructor on each call.

     

    Thursday, October 20, 2011 7:36 PM
  • Yes, you could create a session variable or application scope variable with the correct connection string in it.  Then, you can pass that as the constructor to your objectcontext when you instantiate it.   
    Tom Overton
    Thursday, October 20, 2011 7:42 PM