locked
Copy remote server database to my desktop database application RRS feed

  • Question

  • I have a desktop database application, which in the first instance was planned to work with data from a remote server database, and due to the fact that pulling data from remote server was taking too long, I decide to put in the application an copy of the remote database and make all queries on this copy.

    Info:

    1. both databases are having the same schema and are not bigger that 50 mb, and are done in MS SQL

    2. Select statement are done in the local db, Update, Insert, Delete are done on the server

    3. Application in done in visual basic (goal is see data in tables or charts, and insert/update new data)

    How can I do to have on the users computer a refresh of the server database every time when they are opening the application, or when they will press a Refresh database button ???

    I am new with programming and still need to learn many things :), I manage to make the app but now I am stack to this issue?

    Thanks,

    • Moved by Youjun Tang Wednesday, July 1, 2015 7:09 AM more appropriate
    Saturday, June 20, 2015 8:50 PM

All replies

  • Hello,

    One method which requires changes on the remote database is to use the SqlDepenence class. There are restrictions list here.

    Once setup on the remote database in short you have subscribed to changes excluding those listed above.

    The code below is but a simple example. Let's say you open the database in another app, commit changes to the remote database. The code below immediately receives these changes.  The same holds true if you make changes in SQL Server Management Studio.

    There is a lot to take in here if you are new to programming so I would suggest taking time to learn the methods required to make this all work.

    Sample backend code

    Imports System.Data.SqlClient
    Public Class Watcher
        Private Shared mConnectionString As String = "Server=WT02\SandBox;user id=ServiceUserAcct;password=**password*;" 'Database=SandBox"
        Private mConnection As SqlConnection = Nothing
    
        Public Delegate Sub NewMessage()
        Public Event OnNewMessage As NewMessage
    
        Public Sub New()
            ' Stop an existing services on this connection string just be sure
            SqlDependency.Stop(mConnectionString)
    
            ' Start the dependency, User must have -- SUBSCRIBE QUERY NOTIFICATIONS permission
            ' Database must also have SSB enabled --  ALTER DATABASE Chatter SET ENABLE_BROKER
            SqlDependency.Start(mConnectionString)
    
            ' Create the connection
            mConnection = New SqlConnection(mConnectionString)
        End Sub
        Protected Overrides Sub Finalize()
            SqlDependency.Stop(mConnectionString)
        End Sub
        ''' <summary>
        ''' Retreive messages from database via conventional SQL statement
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function GetMessagesFromNonStoredProc() As DataTable
    
            Dim dt As New DataTable()
    
            Try
    
                Dim cmd As New SqlCommand(
                    <SQL>
                        SELECT 
                            [AuditLogId],
                            [ApplicationId],
                            [UserId],
                            [DateStamp] 
                        FROM [EnterpriseSandbox].[Portal].[AuditLog]
                    </SQL>.Value, mConnection)
    
                cmd.Notification = Nothing
    
                Dim dependency As New SqlDependency(cmd)
    
                AddHandler dependency.OnChange, AddressOf OnChange
    
                If mConnection.State = ConnectionState.Closed Then
                    mConnection.Open()
                End If
    
                dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection))
    
            Catch ex As Exception
                Throw ex
            End Try
    
            Return dt
    
        End Function
        ''' <summary>
        ''' Handler for the SqlDependency OnChange event
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub OnChange(ByVal sender As Object, ByVal e As SqlNotificationEventArgs)
            Dim dependency As SqlDependency = TryCast(sender, SqlDependency)
    
            ' Notices are only a one shot deal so remove the existing one so a new one can be added
            RemoveHandler dependency.OnChange, AddressOf OnChange
    
            ' Fire the event
            RaiseEvent OnNewMessage()
        End Sub
    
        ''' <summary>
        ''' Get users from database to show current data
        ''' </summary>
        ''' <returns></returns>
        Public Shared Function GetLogData() As DataTable
    
            Dim Conn As New SqlConnection(mConnectionString)
            Dim cmd As New SqlCommand("SELECT AuditLogId, ApplicationId, UserId, DateStamp FROM Portal.AuditLog", Conn)
            Dim dt As New DataTable()
    
            Conn.Open()
    
            Try
                dt.Load(cmd.ExecuteReader())
            Finally
                Conn.Close()
            End Try
    
            Return dt
    
        End Function
    End Class
    

    Sample front end code which returns all changes in a DataTable.

    Public Class Form1
        Private mAuditData As PortalAuditLogWatcher.Watcher
        Public Sub New()
            InitializeComponent()
            Try
                Dim perm As New SqlClient.SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted)
                perm.Demand()
            Catch
                Throw New ApplicationException("No permission")
            End Try
        End Sub
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            DataGridView1.DataSource = PortalAuditLogWatcher.Watcher.GetLogData
            DataGridView1.ExpandColumns()
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.

    Saturday, June 20, 2015 10:28 PM
  • I am trying to achieve the synchronization between 2 databases by following this tutorial:

    https://msdn.microsoft.com/en-us/library/ff928525%28v=sql.110%29.aspx

    Defining the Scope and provisioning the 2 databases work just fine, now when I am trying the execute the synchronization I receive the following error:

    I am a beginner and this error I do not know to fix it, can someone please help!!!

    I tried to insert here a picture, for me is not allowed yet :), so I put the error picture on my Google drive:

    https://drive.google.com/file/d/0B9jhbVSkNnSaRDZjUDE0clJoaGc/view?usp=sharing

    Monday, June 22, 2015 9:10 PM
  • I found a more simple solutions, which is not working yet for me :)

    Microsoft sync framework with this tutorial:

    Tutorial: Synchronizing SQL Server and SQL Express

    (https://msdn.microsoft.com/en-us/library/ff928700%28v=sql.110%29.aspx)

    I manage to made the provisioning for both databases, and during synchronization I got an error which I don't know how to fix.

    the error is :

    A first chance exception of type 'Microsoft.Synchronization.SyncException' occurred in Microsoft.Synchronization.dll

    Additional information: Retrieving the COM class factory for component with CLSID {046C184F-2188-4C99-A95A-9C0DCDC19050} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).


    • Marked as answer by iLucian Monday, June 22, 2015 9:20 PM
    • Unmarked as answer by iLucian Monday, June 22, 2015 9:20 PM
    Monday, June 22, 2015 9:20 PM
  • I would post your question to the below Microsoft Sync Framework forum:

    https://social.microsoft.com/Forums/en-US/home?category=sync#forum=syncdevdiscussions&content=Search


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, June 23, 2015 12:59 PM
  • If you would like, myself or another moderator can move your question to the forum Paul suggested.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.

    Tuesday, June 23, 2015 1:24 PM
  • You can Use Tool for that. Its easy to create Replicas using it.This tool works for SQL Azure as well

    Name of that tool is : SqlAzureMW

    Tuesday, June 23, 2015 1:28 PM
  • Please move my question.

    thank you

    Tuesday, June 23, 2015 9:22 PM