Online/Offline Application that must synchronize with SQL 2005

Hi !

I must developp a WPF Application with online and offline capabilities! First I think to use XML file on the local application and transfer these XML files to a webservice that will synchronize them with the SQL 2005 Server

BUT

I read about "Replication"... and I think it will be much simpler to implement!!!

Do you think it is a good idea to have a "local" SQL Express database and replicate it (when connection available is) with the principal database that will run a standard SQL 2005 version!

Do you have another suggestion to make such an application?

Thanks for help!!!

PlaTyPuS

PS: when the sql express solution a good idea is, does it give a simple solution to programm an automatic synchronization every hour?

Yes, you can use replication to sync data from the "remote" server to "local" server. You can schedule the job run in certain interval or even continuously.

You might want to take a look Microsoft Synchronization Services for ADO.NET. It might be exactly what you want. The CTP release can be downloaded from here (http://www.microsoft.com/downloads/details.aspx?FamilyID=75FEF59F-1B5E-49BC-A21A-9EF4F34DE6FC&displaylang=en). Rather than simply replicating database data, it provides a set of API to sync between data services and a data store. There are also more discussion in another MSDN forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1225&SiteID=1

|||

Peng Song wrote:

Yes, you can use replication to sync data from the "remote" server to "local" server. You can schedule the job run in certain interval or even continuously.

You might want to take a look Microsoft Synchronization Services for ADO.NET. It might be exactly what you want. The CTP release can be downloaded from here (http://www.microsoft.com/downloads/details.aspx?FamilyID=75FEF59F-1B5E-49BC-A21A-9EF4F34DE6FC&displaylang=en). Rather than simply replicating database data, it provides a set of API to sync between data services and a data store. There are also more discussion in another MSDN forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1225&SiteID=1

thx for answer! very interesting links (the blog of Rafik is very very interesting!!! : http://blogs.msdn.com/synchronizer/archive/2007/03/01/sync-demos-write-up.aspx)

++

Online Database Transfer

Hi.
I need to periodically copy my 3 databases to another SQL Server (both
SQL Server 2005).
I want to use "Copy Database Wizard" (mode SMO) or DTS Package
"Transfer database task" (online)
(I think booth use the same engines mechanism).
I have to use SMO/online method because my servers don't have a
network share-point.
Each of my databases has more then 100 tables (no more than 200).
During executing CDW job or DTS package I get an error:
"OnError,PC_NAME,ACCOUNT_NAME,PACKAGE_NAME,{5568F7 82-2BCD-4446-
BFF0-0287199B830E},{B5740EDC-D526-4B6E-BE13-E3FCCFFAA52C},
2007-02-26 22:50:08,2007-02-26 22:50:08,0,0x,ERROR :
errorCode=-1073548784 description=
Executing the query "IF EXISTS (SELECT name FROM sys.databases WHERE
name = N'DEST_TABLE_NAME')
DROP DATABASE [DEST_TABLE_NAME]
" failed with the following error: "Cannot drop database
"DEST_TABLE_NAME" because it is currently in use.".
Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly,
or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
E9D8-4D23-9739-DA807BCDC2AC}"
Every time the same error.
I did some research and I noticed that this error appears only if a
database has more than 49 tables.
I made a TEST databases with 49 empty and identical tables and the
process succeed. After I added another one table
(50th) then process failed.
Additionally I noticed that this error appears only on MS SLQ Server
2005 with SP1 or SP2. I was testing it on SLQ Server without Service
Pack
and I didn't get any error. (On the version without SP there is small
bug - tables loose a identity column info)
I'm waiting for any advices or suggestion ...
Greetings
gioodo
Hello,
The destination database must be already availble in the destinatgion server
and it should be used by some one. Best methodology
to copy the database to one server to another is:-
1. Backup the database using BACKUP DATABASE command
2. Copy the Backup (.BAK) file to destination
3.Set the destination database to Sinle user using ALTER DATABASE<dbname>
set single_user with rollback immediate
4. Restore the database using RESTORE DATABASE WITH MOVE options
5. Set the database to Multi user using ALTER DATABASE <Dbname> set
Multi_user
Thanks
Hari
<gioodo@.gmail.com> wrote in message
news:1172533698.435434.55890@.k78g2000cwa.googlegro ups.com...
> Hi.
> I need to periodically copy my 3 databases to another SQL Server (both
> SQL Server 2005).
> I want to use "Copy Database Wizard" (mode SMO) or DTS Package
> "Transfer database task" (online)
> (I think booth use the same engines mechanism).
> I have to use SMO/online method because my servers don't have a
> network share-point.
> Each of my databases has more then 100 tables (no more than 200).
> During executing CDW job or DTS package I get an error:
> "OnError,PC_NAME,ACCOUNT_NAME,PACKAGE_NAME,{5568F7 82-2BCD-4446-
> BFF0-0287199B830E},{B5740EDC-D526-4B6E-BE13-E3FCCFFAA52C},
> 2007-02-26 22:50:08,2007-02-26 22:50:08,0,0x,ERROR :
> errorCode=-1073548784 description=
> Executing the query "IF EXISTS (SELECT name FROM sys.databases WHERE
> name = N'DEST_TABLE_NAME')
> DROP DATABASE [DEST_TABLE_NAME]
> " failed with the following error: "Cannot drop database
> "DEST_TABLE_NAME" because it is currently in use.".
> Possible failure reasons: Problems with the query, "ResultSet"
> property not set correctly, parameters not set correctly,
> or connection not established correctly.
> helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
> E9D8-4D23-9739-DA807BCDC2AC}"
> Every time the same error.
> I did some research and I noticed that this error appears only if a
> database has more than 49 tables.
> I made a TEST databases with 49 empty and identical tables and the
> process succeed. After I added another one table
> (50th) then process failed.
> Additionally I noticed that this error appears only on MS SLQ Server
> 2005 with SP1 or SP2. I was testing it on SLQ Server without Service
> Pack
> and I didn't get any error. (On the version without SP there is small
> bug - tables loose a identity column info)
> I'm waiting for any advices or suggestion ...
> Greetings
> gioodo
>

Online Database Transfer

Hi.
I need to periodically copy my 3 databases to another SQL Server (both
SQL Server 2005).
I want to use "Copy Database Wizard" (mode SMO) or DTS Package
"Transfer database task" (online)
(I think booth use the same engines mechanism).
I have to use SMO/online method because my servers don't have a
network share-point.
Each of my databases has more then 100 tables (no more than 200).
During executing CDW job or DTS package I get an error:
" OnError,PC_NAME,ACCOUNT_NAME,PACKAGE_NAM
E,{5568F782-2BCD-4446-
BFF0-0287199B830E},{B5740EDC-D526-4B6E-BE13-E3FCCFFAA52C},
2007-02-26 22:50:08,2007-02-26 22:50:08,0,0x,ERROR :
errorCode=-1073548784 description=
Executing the query "IF EXISTS (SELECT name FROM sys.databases WHERE
name = N'DEST_TABLE_NAME')
DROP DATABASE [DEST_TABLE_NAME]
" failed with the following error: "Cannot drop database
"DEST_TABLE_NAME" because it is currently in use.".
Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly,
or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
E9D8-4D23-9739-DA807BCDC2AC}"
Every time the same error.
I did some research and I noticed that this error appears only if a
database has more than 49 tables.
I made a TEST databases with 49 empty and identical tables and the
process succeed. After I added another one table
(50th) then process failed.
Additionally I noticed that this error appears only on MS SLQ Server
2005 with SP1 or SP2. I was testing it on SLQ Server without Service
Pack
and I didn't get any error. (On the version without SP there is small
bug - tables loose a identity column info)
I'm waiting for any advices or suggestion ...
Greetings
gioodoHello,
The destination database must be already availble in the destinatgion server
and it should be used by some one. Best methodology
to copy the database to one server to another is:-
1. Backup the database using BACKUP DATABASE command
2. Copy the Backup (.BAK) file to destination
3.Set the destination database to Sinle user using ALTER DATABASE<dbname>
set single_user with rollback immediate
4. Restore the database using RESTORE DATABASE WITH MOVE options
5. Set the database to Multi user using ALTER DATABASE <Dbname> set
Multi_user
Thanks
Hari
<gioodo@.gmail.com> wrote in message
news:1172533698.435434.55890@.k78g2000cwa.googlegroups.com...
> Hi.
> I need to periodically copy my 3 databases to another SQL Server (both
> SQL Server 2005).
> I want to use "Copy Database Wizard" (mode SMO) or DTS Package
> "Transfer database task" (online)
> (I think booth use the same engines mechanism).
> I have to use SMO/online method because my servers don't have a
> network share-point.
> Each of my databases has more then 100 tables (no more than 200).
> During executing CDW job or DTS package I get an error:
> " OnError,PC_NAME,ACCOUNT_NAME,PACKAGE_NAM
E,{5568F782-2BCD-4446-
> BFF0-0287199B830E},{B5740EDC-D526-4B6E-BE13-E3FCCFFAA52C},
> 2007-02-26 22:50:08,2007-02-26 22:50:08,0,0x,ERROR :
> errorCode=-1073548784 description=
> Executing the query "IF EXISTS (SELECT name FROM sys.databases WHERE
> name = N'DEST_TABLE_NAME')
> DROP DATABASE [DEST_TABLE_NAME]
> " failed with the following error: "Cannot drop database
> "DEST_TABLE_NAME" because it is currently in use.".
> Possible failure reasons: Problems with the query, "ResultSet"
> property not set correctly, parameters not set correctly,
> or connection not established correctly.
> helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
> E9D8-4D23-9739-DA807BCDC2AC}"
> Every time the same error.
> I did some research and I noticed that this error appears only if a
> database has more than 49 tables.
> I made a TEST databases with 49 empty and identical tables and the
> process succeed. After I added another one table
> (50th) then process failed.
> Additionally I noticed that this error appears only on MS SLQ Server
> 2005 with SP1 or SP2. I was testing it on SLQ Server without Service
> Pack
> and I didn't get any error. (On the version without SP there is small
> bug - tables loose a identity column info)
> I'm waiting for any advices or suggestion ...
> Greetings
> gioodo
>

Online Database Transfer

Hi.
I need to periodically copy my 3 databases to another SQL Server (both
SQL Server 2005).
I want to use "Copy Database Wizard" (mode SMO) or DTS Package
"Transfer database task" (online)
(I think booth use the same engines mechanism).
I have to use SMO/online method because my servers don't have a
network share-point.
Each of my databases has more then 100 tables (no more than 200).
During executing CDW job or DTS package I get an error:
"OnError,PC_NAME,ACCOUNT_NAME,PACKAGE_NAME,{5568F782-2BCD-4446-
BFF0-0287199B830E},{B5740EDC-D526-4B6E-BE13-E3FCCFFAA52C},
2007-02-26 22:50:08,2007-02-26 22:50:08,0,0x,ERROR :
errorCode=-1073548784 description= Executing the query "IF EXISTS (SELECT name FROM sys.databases WHERE
name = N'DEST_TABLE_NAME')
DROP DATABASE [DEST_TABLE_NAME]
" failed with the following error: "Cannot drop database
"DEST_TABLE_NAME" because it is currently in use.".
Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly,
or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
E9D8-4D23-9739-DA807BCDC2AC}"
Every time the same error.
I did some research and I noticed that this error appears only if a
database has more than 49 tables.
I made a TEST databases with 49 empty and identical tables and the
process succeed. After I added another one table
(50th) then process failed.
Additionally I noticed that this error appears only on MS SLQ Server
2005 with SP1 or SP2. I was testing it on SLQ Server without Service
Pack
and I didn't get any error. (On the version without SP there is small
bug - tables loose a identity column info)
I'm waiting for any advices or suggestion ...
Greetings
gioodoHello,
The destination database must be already availble in the destinatgion server
and it should be used by some one. Best methodology
to copy the database to one server to another is:-
1. Backup the database using BACKUP DATABASE command
2. Copy the Backup (.BAK) file to destination
3.Set the destination database to Sinle user using ALTER DATABASE<dbname>
set single_user with rollback immediate
4. Restore the database using RESTORE DATABASE WITH MOVE options
5. Set the database to Multi user using ALTER DATABASE <Dbname> set
Multi_user
Thanks
Hari
<gioodo@.gmail.com> wrote in message
news:1172533698.435434.55890@.k78g2000cwa.googlegroups.com...
> Hi.
> I need to periodically copy my 3 databases to another SQL Server (both
> SQL Server 2005).
> I want to use "Copy Database Wizard" (mode SMO) or DTS Package
> "Transfer database task" (online)
> (I think booth use the same engines mechanism).
> I have to use SMO/online method because my servers don't have a
> network share-point.
> Each of my databases has more then 100 tables (no more than 200).
> During executing CDW job or DTS package I get an error:
> "OnError,PC_NAME,ACCOUNT_NAME,PACKAGE_NAME,{5568F782-2BCD-4446-
> BFF0-0287199B830E},{B5740EDC-D526-4B6E-BE13-E3FCCFFAA52C},
> 2007-02-26 22:50:08,2007-02-26 22:50:08,0,0x,ERROR :
> errorCode=-1073548784 description=> Executing the query "IF EXISTS (SELECT name FROM sys.databases WHERE
> name = N'DEST_TABLE_NAME')
> DROP DATABASE [DEST_TABLE_NAME]
> " failed with the following error: "Cannot drop database
> "DEST_TABLE_NAME" because it is currently in use.".
> Possible failure reasons: Problems with the query, "ResultSet"
> property not set correctly, parameters not set correctly,
> or connection not established correctly.
> helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
> E9D8-4D23-9739-DA807BCDC2AC}"
> Every time the same error.
> I did some research and I noticed that this error appears only if a
> database has more than 49 tables.
> I made a TEST databases with 49 empty and identical tables and the
> process succeed. After I added another one table
> (50th) then process failed.
> Additionally I noticed that this error appears only on MS SLQ Server
> 2005 with SP1 or SP2. I was testing it on SLQ Server without Service
> Pack
> and I didn't get any error. (On the version without SP there is small
> bug - tables loose a identity column info)
> I'm waiting for any advices or suggestion ...
> Greetings
> gioodo
>