none
Sincronizzazione / replica DB SQL RRS feed

  • Domanda

  • Ciao a tutti,

    devo pubblicare un db di SQL affinchè possa essere consultato, e non solo in lettura dai CLIENTI di un'azienda.

    Sto parlando di un server di produzione dove lavorano già un certo numero di UTENTI di dominio.

    Chi ha in gestione il gestionale spinge per pubblicare direttamente il server di produzione in modo che i clienti e non gli utenti consultino esattamente i dati in tempo reale del gestionale ( disponibilità, etc).

    Io preferisco pubblicare un secondo server in dmz e fare in modo che i clienti accedano  a questo server e non al server di produzione per le note ragioni di sicurezza.

    Che strumenti, se ci sono, potrei utilizzare affinchè il secondo server in dmz abbia esattamante in tempo reale le stesse info del db di produzione?

    Mi spiego meglio i clienti accedono al server in DMZ e scrivendoci sopra è come se scrivessero anche sul server di produzione e viceversa gli utenti di produzione scrivono, modificano e cancellano dati sul server in produzione e contemporaneamente vengono scritti anche sul server web in DMZ.

    Spero di essere stato chiaro.

    Attendo suggerimenti in merito.

    Grazie.

     

    domenica 10 luglio 2011 18:22

Tutte le risposte

  • Ciao Giampaolo,

    La proposta di utilizzare un unico database centralizzato per l'operatività "interna" ed "esterna" è, a mio avviso, la più moderna, veloce e potente.

    Le ragioni di sicurezza che riporti a favore di una soluzione duplicata sono normalmente abbastanza incosistenti. Se non capisco male i dati vanno duplicati in lettura e scrittura e ti serviranno strumenti per sincronizzare i due database. Ciò significa, dal punto di vista strettamente della sicurezza, che l'unico risultato che hai ottenuto è che i dati finiscono su due server differenti, che sono uniti da un canale per comunicare e quindi i rischi di accesso non voluto banalmente raddoppiano. E' un po' come nascondere la formula dell'elisir di giovinezza in posto segretissimo e per mostrarla ad un amico, anzichè indicargli il posto segreto si mette una copia della formula anche altrove!

    Detto ciò gli strumenti che chiedi esistono, quasi.

    Una replica full merge continua permette di avere i dati sincronizzati quasi in tempo reale, quindi la tua richiesta di "esattamente in tempo reale" viene violata. L'intervallo di sincronizzazione, se tutto va bene, non può scendere sotto il paio di minuti. Questa strada, tra l'altro, non è certo una passeggiata. La duplicazione via replica del database pone grossi problemi replativamente ai campi identity, ai numeratori, alle stesse relazioni e quindi il database va normalmente pesantemente adattato.

    Per dettagli puoi cominciare da qui: http://technet.microsoft.com/en-us/library/ms151198.aspx

    marc.

    mercoledì 13 luglio 2011 07:41
  • Ciao,

    approfitto di questo thread - grazie Giampaolo - per chiedere un parere al grande Marcello :)

    Da un po' di tempo sto lavorando ad un progetto che ha come obiettivo quello d'implementare una serie di portali WEB, primo fra tutti il portale e-commerce.

    Sto valutando quale possa essere la tecnologia migliore da utilizzare per mantenere aggiornato il DB di una applicazione di e-commerce che dovrà ovviamente esporre sul web alcuni dati contenuti nel database del sistema ERP.

    In alcune realtà il server Web accede al server aziendale per leggere i dati, ma per fare questo è necessario aprire in ingresso la porta (1433) di SQL Server e questo viene tipicamente contestato dai responsabili IT.

    Sto pensando quindi ad una soluzione basata sulla replica di SQL Server, in cui prevedo di pubblicare *solo* le tabelle strettamente necessarie all'applicazione e-commerce (portale Web); le tabelle anagrafiche pubblicate con una replica transazionale (no sottoscrittori aggiornabili) mentre altre tabelle (ordini, ecc...) pubblicate con una replica merge. In questo modo la porta (1433) dell'istanza SQL Server aziendale sarebbe aperta solo in uscita (repliche push).

    Il database del sistema ERP (quello da pubblicare) non è strettamente pensato per la replica: la chiave primaria, in tutte le tabelle, è la colonna con proprierà IDENTITY... non ci sono trigger e le FOREIGN KEY verranno gestite NOT FOR REPLICATION. Il database dell'applicazione e-commerce avrà la stessa struttura del database del sistema ERP, con la differenza che solo alcune tabelle risulteranno essere popolate.

    Sul portale e-commerce, i clienti dovranno poter visualizzare il prezzo di listino di un determinato prodotto, inserire ordini di vendita e verificare la disponibilità di un prodotto.

    >>> Ciò significa, dal punto di vista strettamente della sicurezza, che l'unico risultato che hai ottenuto

    >>> è che i dati finiscono su due server differenti...

    Verissimo, e infatti ho pensato anche ad altre soluzioni: Service Broker or Web Services, ma per entrambe ci sarebbe davvero tando codice da scrievre... allo stesso tempo le aziende chiedono soluzioni sicure...

    In base alla tua esperienza, cosa mi consigli ?

    Ci sono delle best practicies per gestire al meglio gli aggiornamenti dello schema del database pubblicato (= cambio di versione del sistema ERP) ?

    Per Giampaolo: dai un'occhiata anche a questi due webcast di Marcello, sono su SS2005 ma i concetti base non cambiano:

    Grazie


    Sergio Govoni
    SQL Server MVP
    MVP Profile: https://mvp.support.microsoft.com/profile/Sergio.Govoni
    Blog: http://community.ugiss.org/blogs/sgovoni
    giovedì 14 luglio 2011 13:22
    Moderatore
  • Ciao Sergio,

    Pur avendo lavorato molto con le repliche, o forse proprio per quello, sostanzialmente alla fine la sconsiglio sempre. Io personalmente non la uso più da tempo per cui non sono nemmeno aggiornatissimo sugli ultimi sviluppi.

    La prima osservazione è banalissima, ma la faccio ugualmente. Un db visibile dal web è bene che non risponda sulla 1433 dove la stragrande maggioranza dei tools di attacco tentano la violazione di eventuali debolezze di sql server. Cambiare porta costa poco o nulla e, a mio avviso, è una generica best practice da applicare più o meno sempre. Mettere il server in ascolto sulla porta 12345 o simile non soddisfa ovviamente i responsabile della sicurezza ma è già un piccolo passettino.

    Personalmente, per le casistiche che descrivi, uso un web service banalissimo che mi permette di avere comunicazioni sicure con il database tramite https. Si tratta di così poco codice che te lo posso riportare per intero:

     

     [WebMethod(Description = "Getting Data from server")]
     public System.Data.DataSet[] Execute(bool tran, string[] commands)
     {
    
     System.Data.DataSet[] dss = new DataSet[commands.Length];
    
    
     System.Data.SqlClient.SqlConnection cnn;
    
     try
     {
      cnn = OpenConnection();
     }
     catch (Exception ex)
     {
      dss = new DataSet[1];
      dss[0] = GetError(ex);
      return dss;
     }
    
     System.Data.SqlClient.SqlTransaction tr = null;
     int i = 0;
    
    
    
     try
     {
      if (tran) tr = cnn.BeginTransaction();
      for (i = 0; i < commands.Length; i++)
      dss[i] = this.Execute(commands[i], cnn, tr);
      if (tran) tr.Commit();
     }
     catch (Exception ex)
     {
      commands = null;
      if (tran) tr.Rollback();
    
      dss = new DataSet[1];
      dss[0] = GetError(ex);
     }
     finally
     {
      cnn.Close();
     }
    
     return dss;
    
     }
     private static System.Data.DataSet GetError(System.Exception ex)
     {
     System.Data.DataTable t = new DataTable();
     t.Columns.Add("HProxy::ErrorMessage", typeof(string));
     t.Columns.Add("HProxy::ErrorLevel", typeof(int));
    
     t.Rows.Add(new object[] {ex.Message, 16 });
    
     System.Data.DataSet ds = new DataSet();
     ds.Tables.Add(t);
    
     return ds;
     }
     private static System.Data.SqlClient.SqlConnection OpenConnection()
     {
     System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection("MY CONNECTION STRING");
     cnn.Open();
     return cnn;
     }
     
     private System.Data.DataSet Execute(string cmd, System.Data.SqlClient.SqlConnection cnn, System.Data.SqlClient.SqlTransaction tr)
     {
     System.Data.DataSet ds = new System.Data.DataSet();
     System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
     adapter.SelectCommand = new System.Data.SqlClient.SqlCommand(cmd, cnn, tr);
     adapter.Fill(ds);
     return ds;
     }
    

     


    In sostanza il web service si aspetta dei commands, la richeista se eseguirli o meno in transazione e ritorna l'array di dataset conseguenti, il tutto via https.

    Lato client/sito/applicativo le chiamate le faccio in questo modo, senza instanziare il webserver perchè a me non piace:

     

    public static class Proxy
    {
     private static string mProxyUrl = null;
     private static object wsvcClass;
     private static MethodInfo mMethodExecute;
     private const string ServiceName="Data";
     public static System.Data.DataSet[] Execute(string proxyurl, bool tran, string[] commands)
     {
      
     object[] args = new object[]{tran, commands};
    
     if (wsvcClass == null || proxyurl != mProxyUrl)
     {
      wsvcClass = PrepareService(proxyurl);
      mProxyUrl = proxyurl;
      mMethodExecute = wsvcClass.GetType().GetMethod("Execute");
     }
     System.Data.DataSet[] ds= (System.Data.DataSet[])mMethodExecute.Invoke(wsvcClass, args);
    
     if (ds != null && ds.Length == 1 && ds[0] != null && ds[0].Tables.Count == 1 && ds[0].Tables[0].Rows.Count == 1 && ds[0].Tables[0].Columns[0].Caption == "HProxy::ErrorMessage")
      throw new System.Exception(ds[0].Tables[0].Rows[0][0].ToString());
    
     return ds;
     }
     public static bool CheckValidationResult(object o, X509Certificate certificate, X509Chain chain, System.Net.Security.SslPolicyErrors e)
     {
     return true;
     } 
     [SecurityPermissionAttribute(SecurityAction.Demand, Unrestricted = true)]
     private static object PrepareService(string webServiceAsmxUrl)
     {
     System.Net.ServicePointManager.ServerCertificateValidationCallback = new System.Net.Security.RemoteCertificateValidationCallback(CheckValidationResult);
    
     System.Net.WebClient client = new System.Net.WebClient();
     //-Connect To the web service
     System.IO.Stream stream = client.OpenRead(webServiceAsmxUrl + "?wsdl");
     //--Now read the WSDL file describing a service.
     ServiceDescription description = ServiceDescription.Read(stream);
     //--Initialize a service description importer.
     ServiceDescriptionImporter importer = new ServiceDescriptionImporter();
     importer.ProtocolName = "Soap12"; // Use SOAP 1.2.
     importer.AddServiceDescription(description, null, null);
     //--Generate a proxy client.
     importer.Style = ServiceDescriptionImportStyle.Client;
     //--Generate properties to represent primitive values.
     importer.CodeGenerationOptions = System.Xml.Serialization.CodeGenerationOptions.GenerateProperties;
     //--Initialize a Code-DOM tree into which we will import the service.
     CodeNamespace nmspace = new CodeNamespace();
     CodeCompileUnit unit1 = new CodeCompileUnit();
     unit1.Namespaces.Add(nmspace);
     //--Import the service into the Code-DOM tree. This creates proxy code
     //--that uses the service.
     ServiceDescriptionImportWarnings warning = importer.Import(nmspace, unit1);
     if (warning == 0) //--If zero then we are good to go
     {
      //--Generate the proxy code
      CodeDomProvider provider1 = CodeDomProvider.CreateProvider("CSharp");
      //--Compile the assembly proxy with the appropriate references
      string[] assemblyReferences = new string[5] { "System.dll", "System.Web.Services.dll", "System.Web.dll", "System.Xml.dll", "System.Data.dll" };
      CompilerParameters parms = new CompilerParameters(assemblyReferences);
      CompilerResults results = provider1.CompileAssemblyFromDom(parms, unit1);
      //--Finally, Invoke the web service method
      return results.CompiledAssembly.CreateInstance(ServiceName);
     }
     else
      throw new System.Exception("Error Creating Proxy");
     }
    

     


    L'unico vero inconveniente di questo metodo è che non si possono più utilizzare i commands del namespace System.Data. Per quanto mi rigarda la cosa non è mai stata un problema perchè ho sempre detestato il modo con cui il SqlCommand scrive le chiamate a Sql e quindi mi sono scritto anni fa un mio SqlCommand che ammette il metodo Render() che mi ritorna la string da inviare a Sql.

    Per venire alla questione replica, come ti ho detto, non sono più aggiornatissimo, ma se non ricordo male pubblicare un unico database su due repliche e per di più di tipo diverso non è possibile. Che io sappia un db supporta al più una pubblicazione. 

    Per quanto riguarda le specifiche richieste ad un database per andare in replica ti sconsiglio vivamente di provarci con gli identity di mezzo. Per una merge sql aggiunge uno uniqueidentifier rowguidcol se assente e questo scompagina tutti i "select *" oltre a sporcare tutte le tabelle. La cosa è diventata così parte di me che ad oggi, pur non usando più repliche, qualunque tabella scriva comincia con "create table nometabella(nomeid uniqueidentifier primary key rowguidcol..." senza eccezioni, nel caso un giorno dovessi andare in replica. Come osservi giustamente inoltre, per le repliche merge, non essendo transazionali, le relazioni possono essere di intralcio e quindi, anche in questo caso, ogni relazione che scrivo da anni ha immancabilmente la forma "references .... not for replication".

    Infine, il sogno della replica "parziale" è quasi sempre, per l'appunto, una chimera. Le relazioni tra le tabelle sono quasi sempre troppe per poter escludere qualcosa di significativo. Voglio mostrare i prodotti, quindi pubblico la t_Prodotti, ma ovviamente voglio mostrarne anche il tipo e quindi via con la t_TipiProdotto. Ma subito il cliente mi chiede di mostrare quanti ne ha comprati il suo cliente e via di pubblicazione dei movimenti, e come non permettere su un sito con due funzionalità di scaricare la fattura? pronti, pubblicate tutte le tabelle di contabilità, e così via.

    Insomma, io ti sconsiglio la replica.

    marc.



    venerdì 15 luglio 2011 07:54
  • Ciao Marcello,

    Pur avendo lavorato molto con le repliche, o forse proprio per quello, sostanzialmente alla fine la sconsiglio sempre. Io personalmente non la uso più da tempo per cui non sono nemmeno aggiornatissimo sugli ultimi sviluppi.

    Per il momento, per la soluzione con replica, ho un prototipo non in produzione; lo stiamo provando su SQL Server 2008/R2...

    La prima osservazione è banalissima, ma la faccio ugualmente. Un db visibile dal web è bene che non risponda sulla 1433 dove la stragrande maggioranza dei tools di attacco tentano la violazione di eventuali debolezze di sql server. Cambiare porta costa poco o nulla e, a mio avviso, è una generica best practice da applicare più o meno sempre. Mettere il server in ascolto sulla porta 12345 o simile non soddisfa ovviamente i responsabile della sicurezza ma è già un piccolo passettino.

    Chiarissimo :) vero però anche il fatto che i responsabili IT non sono soddisfatti, in termini di sicurezza, solo con questo... e in alcuni casi non autorizzano neppure l'uso del web service perchè la teoria è: "il server aziendale è sicuro se non ha accessi dall'esterno" il che significa non aprire porte dall'esterno verso l'interno ... ma questo mi costringe a replicare i dati ... con la tua correttissima considerazione sulla formula magica :) non se ne esce!!

    Personalmente, per le casistiche che descrivi, uso un web service banalissimo che mi permette di avere comunicazioni sicure con il database tramite https. Si tratta di così poco codice che te lo posso riportare per intero...

    Grazie Marcello, sarebbe un'ottima soluzione... il web service però dovrebbe girare sul server gestionale e questo in alcune realtà non passa (per il discorso sicurezza)... inoltre l'applicazione gestionale è scritta in Delphi e gran parte delle logiche di inserimento e reperimento del dato sono all'interno dell'applicazione e non sul DB. Questo mi costringerebbe a riscrivere parecchio codice ... dico bene ? Vorrei evitare di riscrivere ad esempio tutte le procedure di determinazione dei prezzi...

    Per quanto riguarda le specifiche richieste ad un database per andare in replica ti sconsiglio vivamente di provarci con gli identity di mezzo. Per una merge sql aggiunge uno uniqueidentifier rowguidcol se assente e questo scompagina tutti i "select *" oltre a sporcare tutte le tabelle. La cosa è diventata così parte di me che ad oggi, pur non usando più repliche, qualunque tabella scriva comincia con "create table nometabella(nomeid uniqueidentifier primary key rowguidcol..." senza eccezioni, nel caso un giorno dovessi andare in replica. Come osservi giustamente inoltre, per le repliche merge, non essendo transazionali, le relazioni possono essere di intralcio e quindi, anche in questo caso, ogni relazione che scrivo da anni ha immancabilmente la forma "references .... not for replication".

    Ho già avuto modo di apprezzare il problema :) delle colonne IDENTITY; probabilmente veniva gestito anche in SS2005, SS2008 in replica MERGE assegna "pacchetti" di ID al pubblicatore e ad ogni sottoscrittore in modo che in entrambi i lati non ci sia lo stesso valore per la colonna IDENTITY; non ho però ancora provato cosa accade quando una delle due entità (pubblicatore o sottoscrittore) termina il pacchetto assegnato... spero ne venga assegnato automaticamente un altro tenendo conto di tutti i pacchetti rilasciati...

    Proverò a cercare una semplificazione per ridurre al minimo le tabelle in replica MERGE o per lo meno in replica MERGE con possibilità di inserimenti sui sottoscrittori.

    In aggiunta, i DB dei portali avranno sicuramente le colonne chiave di tipo uniqueidentifier per poter andare in replica, il problema sta sulle tabelle del sistema ERP...

    Infine, il sogno della replica "parziale" è quasi sempre, per l'appunto, una chimera...

    Purtroppo è davvero così :(

    Insomma, io ti sconsiglio la replica.

    Sono molto combattuto, la prossima settimana farò altri test... vi farò sapere come andrà a finire; nel frattempo se ti viene in mente qualcosa...

    Grazie


    Sergio Govoni
    SQL Server MVP
    MVP Profile: https://mvp.support.microsoft.com/profile/Sergio.Govoni
    Blog: http://community.ugiss.org/blogs/sgovoni
    venerdì 15 luglio 2011 15:22
    Moderatore
  • Ciao Sergio,

    Grazie Marcello, sarebbe un'ottima soluzione... il web service però dovrebbe girare sul server gestionale 

    No, dovrebbe girare su un server che vede il database e che vede l'web.

    inoltre l'applicazione gestionale è scritta in Delphi e gran parte delle logiche di inserimento e reperimento del dato sono all'interno dell'applicazione e non sul DB. Questo mi costringerebbe a riscrivere parecchio codice ... dico bene ? Vorrei evitare di riscrivere ad esempio tutte le procedure di determinazione dei prezzi...

    Anche qui, no. Il web server che ti ho riportato esegue una generica chiama, non una specifica. E' chiaro che se nel codice ci sono mille aperture di connessione bisogna in effetti riscriverne gran parte. Ma se nel codice c'è, come dovrebbe, una classica e unica classe statica che riceve  un command e lo invia a sql si tratta di adattare pochissimo codice.

    non ho però ancora provato cosa accade quando una delle due entità (pubblicatore o sottoscrittore) termina il pacchetto assegnato... spero ne venga assegnato automaticamente un altro tenendo conto di tutti i pacchetti rilasciati...

    Nelle versioni precedenti di sql bisognava intervenire a manina, se non ricordo male. con 2008 e 2008r2 non so.

    marc.

    P.S.

    ma per quotare con sto coso come si fa? Si aggiungono tutti i <blockquote> a mano?

    rimarc.

    venerdì 15 luglio 2011 16:46
  • P.S.

    ma per quotare con sto coso come si fa? Si aggiungono tutti i <blockquote> a mano?

    rimarc.


    Pota si, è veramente penoso :-)

    Baciotti,


    Lorenzo Benaglia
    Microsoft MVP - SQL Server
    http://blogs.dotnethell.it/lorenzo
    http://social.technet.microsoft.com/Forums/it-IT/sqlserverit
    venerdì 15 luglio 2011 19:13
    Moderatore
  • Ciao Marcello,

    scusa il ritardo con cui rispondo...

    Nelle versioni precedenti di sql bisognava intervenire a manina, se non ricordo male. con 2008 e 2008r2 non so.


    Ho letto che l'assegnazione dovrebbe essere automatica in 2008/R2... è da provare. Sto preparando l'ambiente di test... Replica vs. Web Service... ti faccio sapere...

    Grazie


    Sergio Govoni
    SQL Server MVP
    MVP Profile: https://mvp.support.microsoft.com/profile/Sergio.Govoni
    Blog: http://community.ugiss.org/blogs/sgovoni
    martedì 19 luglio 2011 14:58
    Moderatore
  • Pota si, è veramente penoso :-)

    +1 :)
    Sergio Govoni
    SQL Server MVP
    MVP Profile: https://mvp.support.microsoft.com/profile/Sergio.Govoni
    Blog: http://community.ugiss.org/blogs/sgovoni
    martedì 19 luglio 2011 14:59
    Moderatore