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 Re-indexing vs Offline Re-indexing

Hi,

The other day we tried online re-indexing feature of SQL 2005 and it’s performing faster than offline re-indexing. Could you please validate if it’s supposed to do be this way? I always thought offline should be faster than online.

Thanks,

Ritesh

Generally offline should be faster than online. Offline doesn't have to handle any changes that might be occurring on the table while the re-indexing is taking place, etc., so there's less work that needs to be done. If it seems the online form is faster than the offline, then perhaps it's a matter of caching or disk speed or some concurrent workload that is making it seem faster. Here's a few things that might make your timings invalid:

First, is the table whose index is being rebuilt of good size so that the difference isn't just a couple of seconds so that we're really just talking about 'noise' in the timing?

Has SQL Server's cache been cleared so that you're not including disk reads in one case and not including them in another?

Are they both going to the same disk(s), or is one benefiting from faster drives?

Are you sure one isn't benefiting from a data file that's already been grown to a sufficient size while the other had to wait for the files to be automatically grown? Or perhaps it's the log file that had to grow during the slower test but not during the faster one?

Was some other query running during the slow test that wasn't running during the faster one? Or some other process that was running on the same machine so that less memory or CPU was available?

Don

|||

Both were ran on same system utilizing the same drives. The index on each tables takes more than an hour hence we can really see the difference. The difference on one of the table is huge from 14 hours in offline to 5 hours in online.

Is there a possibility that if indexes are not fragmented it will take lesser time. We are just running rebuild index on tables currently there's no logic to eliminate certain indexes with less fragmentation.

The online re-indexing was ran after offline re-indexing does the sequence can make the difference?

Thanks,

Ritesh

|||

I'd bet the offline (first) test had to do a lot more work than the online (second) test. It's likely that you are continually having to auto-grow your datafile or log by a small amount at a time so that in the first test it's continually stopping to grow the file. Then the second test just uses that already-grown file and never has to wait like the first test did. Growing a data or log file can have a significant impact. Try growing the file to an appropriate size before you do either re-index operation and don't rely on auto-grow to size your files correctly.

When comparing operations like this you should start from exactly the same starting position. I'd recommend you restore from a backup before each re-index test .. it's very important to compare apples to apples when trying to analyze something like this.

Don

|||

We tried online re-indexing by restoring the same database and still we got the better results as compared to offline indexing. Timings have almost double, is there anything that data engine does internally so that online re-indexing is not a bottleneck on the application.

Thanks,

Ritesh

|||

It seems that to get to the bottom of this we'd have to know a lot more about the table(s), indexes, fragmentation, initial file sizes, concurrent workload, etc., which unfortunately I would likely not be able to dig into as deeply as might be needed.

Offline should generally be faster .. it has exclusive access to the table and indexes so there's less overhead.

I still think there must be something else that's adding extra work for the offline form, like having to grow data or log files where the online rebuild doesn't have to do this work. Can you confirm that you've increased the datafile and logfile sizes before you start the test so that it doesn't have to wait for autogrow?

Don

Online Re-indexing vs Offline Re-indexing

Hi,

The other day we tried online re-indexing feature of SQL 2005 and it’s performing faster than offline re-indexing. Could you please validate if it’s supposed to do be this way? I always thought offline should be faster than online.

Thanks,

Ritesh

Generally offline should be faster than online. Offline doesn't have to handle any changes that might be occurring on the table while the re-indexing is taking place, etc., so there's less work that needs to be done. If it seems the online form is faster than the offline, then perhaps it's a matter of caching or disk speed or some concurrent workload that is making it seem faster. Here's a few things that might make your timings invalid:

First, is the table whose index is being rebuilt of good size so that the difference isn't just a couple of seconds so that we're really just talking about 'noise' in the timing?

Has SQL Server's cache been cleared so that you're not including disk reads in one case and not including them in another?

Are they both going to the same disk(s), or is one benefiting from faster drives?

Are you sure one isn't benefiting from a data file that's already been grown to a sufficient size while the other had to wait for the files to be automatically grown? Or perhaps it's the log file that had to grow during the slower test but not during the faster one?

Was some other query running during the slow test that wasn't running during the faster one? Or some other process that was running on the same machine so that less memory or CPU was available?

Don

|||

Both were ran on same system utilizing the same drives. The index on each tables takes more than an hour hence we can really see the difference. The difference on one of the table is huge from 14 hours in offline to 5 hours in online.

Is there a possibility that if indexes are not fragmented it will take lesser time. We are just running rebuild index on tables currently there's no logic to eliminate certain indexes with less fragmentation.

The online re-indexing was ran after offline re-indexing does the sequence can make the difference?

Thanks,

Ritesh

|||

I'd bet the offline (first) test had to do a lot more work than the online (second) test. It's likely that you are continually having to auto-grow your datafile or log by a small amount at a time so that in the first test it's continually stopping to grow the file. Then the second test just uses that already-grown file and never has to wait like the first test did. Growing a data or log file can have a significant impact. Try growing the file to an appropriate size before you do either re-index operation and don't rely on auto-grow to size your files correctly.

When comparing operations like this you should start from exactly the same starting position. I'd recommend you restore from a backup before each re-index test .. it's very important to compare apples to apples when trying to analyze something like this.

Don

|||

We tried online re-indexing by restoring the same database and still we got the better results as compared to offline indexing. Timings have almost double, is there anything that data engine does internally so that online re-indexing is not a bottleneck on the application.

Thanks,

Ritesh

|||

It seems that to get to the bottom of this we'd have to know a lot more about the table(s), indexes, fragmentation, initial file sizes, concurrent workload, etc., which unfortunately I would likely not be able to dig into as deeply as might be needed.

Offline should generally be faster .. it has exclusive access to the table and indexes so there's less overhead.

I still think there must be something else that's adding extra work for the offline form, like having to grow data or log files where the online rebuild doesn't have to do this work. Can you confirm that you've increased the datafile and logfile sizes before you start the test so that it doesn't have to wait for autogrow?

Don

Online Re-indexing vs Offline Re-indexing

Hi,

The other day we tried online re-indexing feature of SQL 2005 and it’s performing faster than offline re-indexing. Could you please validate if it’s supposed to do be this way? I always thought offline should be faster than online.

Thanks,

Ritesh

Generally offline should be faster than online. Offline doesn't have to handle any changes that might be occurring on the table while the re-indexing is taking place, etc., so there's less work that needs to be done. If it seems the online form is faster than the offline, then perhaps it's a matter of caching or disk speed or some concurrent workload that is making it seem faster. Here's a few things that might make your timings invalid:

First, is the table whose index is being rebuilt of good size so that the difference isn't just a couple of seconds so that we're really just talking about 'noise' in the timing?

Has SQL Server's cache been cleared so that you're not including disk reads in one case and not including them in another?

Are they both going to the same disk(s), or is one benefiting from faster drives?

Are you sure one isn't benefiting from a data file that's already been grown to a sufficient size while the other had to wait for the files to be automatically grown? Or perhaps it's the log file that had to grow during the slower test but not during the faster one?

Was some other query running during the slow test that wasn't running during the faster one? Or some other process that was running on the same machine so that less memory or CPU was available?

Don

|||

Both were ran on same system utilizing the same drives. The index on each tables takes more than an hour hence we can really see the difference. The difference on one of the table is huge from 14 hours in offline to 5 hours in online.

Is there a possibility that if indexes are not fragmented it will take lesser time. We are just running rebuild index on tables currently there's no logic to eliminate certain indexes with less fragmentation.

The online re-indexing was ran after offline re-indexing does the sequence can make the difference?

Thanks,

Ritesh

|||

I'd bet the offline (first) test had to do a lot more work than the online (second) test. It's likely that you are continually having to auto-grow your datafile or log by a small amount at a time so that in the first test it's continually stopping to grow the file. Then the second test just uses that already-grown file and never has to wait like the first test did. Growing a data or log file can have a significant impact. Try growing the file to an appropriate size before you do either re-index operation and don't rely on auto-grow to size your files correctly.

When comparing operations like this you should start from exactly the same starting position. I'd recommend you restore from a backup before each re-index test .. it's very important to compare apples to apples when trying to analyze something like this.

Don

|||

We tried online re-indexing by restoring the same database and still we got the better results as compared to offline indexing. Timings have almost double, is there anything that data engine does internally so that online re-indexing is not a bottleneck on the application.

Thanks,

Ritesh

|||

It seems that to get to the bottom of this we'd have to know a lot more about the table(s), indexes, fragmentation, initial file sizes, concurrent workload, etc., which unfortunately I would likely not be able to dig into as deeply as might be needed.

Offline should generally be faster .. it has exclusive access to the table and indexes so there's less overhead.

I still think there must be something else that's adding extra work for the offline form, like having to grow data or log files where the online rebuild doesn't have to do this work. Can you confirm that you've increased the datafile and logfile sizes before you start the test so that it doesn't have to wait for autogrow?

Don

Online Backup

Hello There Does anyone know how can i make an online backup with taking the
database offline
thank yousorry i mean without taking the database in offline mode
"Amir Fahmy" wrote:
> Hello There Does anyone know how can i make an online backup with taking the
> database offline
> thank you
>|||Hello,
You could always perform the database backup online. All the below backups
types in SQL Server are online.
1. FULL DATABASE Backup
2. Transaction Log Backup
3. Differential Backup
Take a look into BACKUP DATABASE, BACKUP LOG and Recovery Models topic in
SQL Server books online.
Thanks
Hari
"Amir Fahmy" <AmirFahmy@.discussions.microsoft.com> wrote in message
news:55EB6BB3-ACA4-4CDC-8E1F-09D1DD3CD122@.microsoft.com...
> Hello There Does anyone know how can i make an online backup with taking
> the
> database offline
> thank you
>