Advice on a database Replication

Hi,

I.m trying to find the best practice for my solution.

This is the situation.

My application has two databases and 2 client interface. One with a windows forms having a database (sql server 2000) running in an office (on a static IP but on a slow connection). the second one is an web application running on the net with an online database. Both these databases have to have same information. Users can add records to both databases independently but these databases should be synchronize at a point. I hope the situation is cleare. Is there a standard way of doing this. If some one knows good article of how this can be done pls forward it to me.

Many thanks.

-VJ

You can look at Merge Replication, it can handle changes at both ends, as well as handle conflicts if necessary. How many changes per sec or minute do you expect at both ends?|||

Hi Greg,

Yes I was reading on merge replication. there can be about 200 -300 changes on the office side per day. And only about 50 changes from the web side. My only worry is the slow connection from the office end but it can run over night.

By the way whne u have created a replication can reverse it back as I created a replication and I need to change the tables. It's not very easy to do that while having the replication.

Thank you.

-VJ

|||

What is considered a "slow connection", dial-up? With such a small workload, you can schedule your syncs several times a day if you need the changes sooner.

In sql 2000, schema changes can be handled only by sp_addreplcolumn and sp_dropreplcolumn. In sql 2005, this has been expanded to include the actual ALTER TABLE command as well as several other regular TSQL commands.

|||

Hi Greg,

The slow connection is a ISDN and the speed is 64Kbps upload and 128Kbps download. The changes will only be adding and modifications to records and it's all text data. Hope this connection can handle it.

Cheers,

VJ

|||if it's a reliable connection, it should handle it fine. If you expect line drops, then the merge agent retries will cause overall duration to increase.|||

One more quick thing. if the connection drops will the server drops the data or will it rollback and run the synchronization again. This is the first time I'm doing this

Cheers,

-VJ

|||merge agent will retry the data it failed, or retry from its last savepoint.|||I had implemented with more than 10,000 record sync daily on merge replication running on a very bad dsl connection that has frequent bad packet, but it work well, as long as the config. on the replication suit the environments.|||

Hi,

Sounds good. But what exactly did u implied by the config suits the environment,

Thanks,

-VJ

|||U have to config merge agent profile to suit your current environment.For example, the size of the batch to sync and others.sql

Advice on a database Replication

Hi,

I.m trying to find the best practice for my solution.

This is the situation.

My application has two databases and 2 client interface. One with a windows forms having a database (sql server 2000) running in an office (on a static IP but on a slow connection). the second one is an web application running on the net with an online database. Both these databases have to have same information. Users can add records to both databases independently but these databases should be synchronize at a point. I hope the situation is cleare. Is there a standard way of doing this. If some one knows good article of how this can be done pls forward it to me.

Many thanks.

-VJ

You can look at Merge Replication, it can handle changes at both ends, as well as handle conflicts if necessary. How many changes per sec or minute do you expect at both ends?|||

Hi Greg,

Yes I was reading on merge replication. there can be about 200 -300 changes on the office side per day. And only about 50 changes from the web side. My only worry is the slow connection from the office end but it can run over night.

By the way whne u have created a replication can reverse it back as I created a replication and I need to change the tables. It's not very easy to do that while having the replication.

Thank you.

-VJ

|||

What is considered a "slow connection", dial-up? With such a small workload, you can schedule your syncs several times a day if you need the changes sooner.

In sql 2000, schema changes can be handled only by sp_addreplcolumn and sp_dropreplcolumn. In sql 2005, this has been expanded to include the actual ALTER TABLE command as well as several other regular TSQL commands.

|||

Hi Greg,

The slow connection is a ISDN and the speed is 64Kbps upload and 128Kbps download. The changes will only be adding and modifications to records and it's all text data. Hope this connection can handle it.

Cheers,

VJ

|||if it's a reliable connection, it should handle it fine. If you expect line drops, then the merge agent retries will cause overall duration to increase.|||

One more quick thing. if the connection drops will the server drops the data or will it rollback and run the synchronization again. This is the first time I'm doing this

Cheers,

-VJ

|||merge agent will retry the data it failed, or retry from its last savepoint.|||I had implemented with more than 10,000 record sync daily on merge replication running on a very bad dsl connection that has frequent bad packet, but it work well, as long as the config. on the replication suit the environments.|||

Hi,

Sounds good. But what exactly did u implied by the config suits the environment,

Thanks,

-VJ

|||U have to config merge agent profile to suit your current environment.For example, the size of the batch to sync and others.

Advice needed

We are preparing to migrating to SQL Server 2005 from 2000 and to implement
a
High Availability solution.
Has anyone got any suggestions in choosing between Clustering and Database
mirroring from your implementation experiences?More folks with high availablitiy experience attend to the Clustering group.
You may want to post there.
Since db mirroring is relatively new, there is far more 'real' experience
with clustering options to draw upon.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"SAM" <SAM@.discussions.microsoft.com> wrote in message
news:11EBAC7B-C98C-4BB3-8D63-F5BB37F38D9F@.microsoft.com...
> We are preparing to migrating to SQL Server 2005 from 2000 and to
> implement a
> High Availability solution.
> Has anyone got any suggestions in choosing between Clustering and Database
> mirroring from your implementation experiences?|||Thank you. is the clustering group under Windows 2003 server or SQL Server
newsgroups?
"Arnie Rowland" wrote:

> More folks with high availablitiy experience attend to the Clustering grou
p.
> You may want to post there.
> Since db mirroring is relatively new, there is far more 'real' experience
> with clustering options to draw upon.
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "SAM" <SAM@.discussions.microsoft.com> wrote in message
> news:11EBAC7B-C98C-4BB3-8D63-F5BB37F38D9F@.microsoft.com...
>
>|||microsoft.public.sqlserver.clustering
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"SAM" <SAM@.discussions.microsoft.com> wrote in message
news:0F693B30-686D-4072-8BFB-EE133690E2FE@.microsoft.com...[vbcol=seagreen]
> Thank you. is the clustering group under Windows 2003 server or SQL Server
> newsgroups?
> "Arnie Rowland" wrote:
>|||"SAM" <SAM@.discussions.microsoft.com> wrote in message
news:11EBAC7B-C98C-4BB3-8D63-F5BB37F38D9F@.microsoft.com...
> We are preparing to migrating to SQL Server 2005 from 2000 and to
implement a
> High Availability solution.
> Has anyone got any suggestions in choosing between Clustering and Database
> mirroring from your implementation experiences?
Keep in mind the key differences.
Clustering is a complete failover. It's more for a hardware failure
(excluding the shared disks of course.) It operates at the server level.
Mirroring mirrors at the database level. So you have to make sure to keep
logins in synch across servers etc.

Advice needed

We are preparing to migrating to SQL Server 2005 from 2000 and to implement a
High Availability solution.
Has anyone got any suggestions in choosing between Clustering and Database
mirroring from your implementation experiences?More folks with high availablitiy experience attend to the Clustering group.
You may want to post there.
Since db mirroring is relatively new, there is far more 'real' experience
with clustering options to draw upon.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"SAM" <SAM@.discussions.microsoft.com> wrote in message
news:11EBAC7B-C98C-4BB3-8D63-F5BB37F38D9F@.microsoft.com...
> We are preparing to migrating to SQL Server 2005 from 2000 and to
> implement a
> High Availability solution.
> Has anyone got any suggestions in choosing between Clustering and Database
> mirroring from your implementation experiences?|||Thank you. is the clustering group under Windows 2003 server or SQL Server
newsgroups?
"Arnie Rowland" wrote:
> More folks with high availablitiy experience attend to the Clustering group.
> You may want to post there.
> Since db mirroring is relatively new, there is far more 'real' experience
> with clustering options to draw upon.
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "SAM" <SAM@.discussions.microsoft.com> wrote in message
> news:11EBAC7B-C98C-4BB3-8D63-F5BB37F38D9F@.microsoft.com...
> > We are preparing to migrating to SQL Server 2005 from 2000 and to
> > implement a
> > High Availability solution.
> >
> > Has anyone got any suggestions in choosing between Clustering and Database
> > mirroring from your implementation experiences?
>
>|||microsoft.public.sqlserver.clustering
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"SAM" <SAM@.discussions.microsoft.com> wrote in message
news:0F693B30-686D-4072-8BFB-EE133690E2FE@.microsoft.com...
> Thank you. is the clustering group under Windows 2003 server or SQL Server
> newsgroups?
> "Arnie Rowland" wrote:
>> More folks with high availablitiy experience attend to the Clustering
>> group.
>> You may want to post there.
>> Since db mirroring is relatively new, there is far more 'real' experience
>> with clustering options to draw upon.
>> --
>> Arnie Rowland*
>> "To be successful, your heart must accompany your knowledge."
>>
>> "SAM" <SAM@.discussions.microsoft.com> wrote in message
>> news:11EBAC7B-C98C-4BB3-8D63-F5BB37F38D9F@.microsoft.com...
>> > We are preparing to migrating to SQL Server 2005 from 2000 and to
>> > implement a
>> > High Availability solution.
>> >
>> > Has anyone got any suggestions in choosing between Clustering and
>> > Database
>> > mirroring from your implementation experiences?
>>|||"SAM" <SAM@.discussions.microsoft.com> wrote in message
news:11EBAC7B-C98C-4BB3-8D63-F5BB37F38D9F@.microsoft.com...
> We are preparing to migrating to SQL Server 2005 from 2000 and to
implement a
> High Availability solution.
> Has anyone got any suggestions in choosing between Clustering and Database
> mirroring from your implementation experiences?
Keep in mind the key differences.
Clustering is a complete failover. It's more for a hardware failure
(excluding the shared disks of course.) It operates at the server level.
Mirroring mirrors at the database level. So you have to make sure to keep
logins in synch across servers etc.

Adventure Works DW.sln Starting to write data of the 'Internet ~1 ~MG' partition. Hangs Foreve

Hello,

I am trying to process the sample solution "Adventure Works DW.sln",

whenever I get to the point

...

Starting to write data of the 'Internet ~1 ~MG' partition.

...

This processing step NEVER finishes.

Does anyone have a 'way' to make this 'finish' processing?

If I Exit out of Visual Studio and try again, sometimes I will get the error.

Error 5 Errors in the OLAP storage engine: An error occurred while processing the '~CaseDetail ~MG' partition of the '~CaseDetail ~MG' measure group for the 'Customer Clusters ~MC' cube from the Adventure Works DW database. 0 0

Does anyone have any ideas?

Thanks,

Andre Mikulec

AIMDBA

Andre_Mikulec@.Hotmail.com

Also,

When I do individual object processing ...

Processing Dimension 'Clustered Customers.'

Processing Dimension 'Attribute Name'

Started reading data for the 'Attribute Name' attribute.

This <Started reading data for the 'Attribute Name' attribute.> hangs forever and never finishes.

Does anyone have any ideas?

Thanks.

AIMDBA

Andre_Mikulec@.Hotmail.com

|||

Moving to DM forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I was just going through my solution explorer.

I have noticed thate the cube or mining structure 'Customer Clusters' (the one that hangs and does not finish) does IS NOT SEEN IN MY Solution Explorer.

How can this not be?

Does anyone have any idea on how I could fix.

This problem.

I have installed the SQL Server Developer's Edition using my company's MSDN subscription at work.

I installed and deployed the Analysis Services sample solution "Adventure Works DW" just fine without any problems.

This is weird.

Does anyone have any idea on how to fix this.

I have installed and re-installed the AdventureworksBI.msi on my non-company problem host.

The same problem occurs. My old settings in OLAP seem that they are being kept.

I know that the security in the OLAP directories is strange.

Does anyone have any ideas on how to proceed?

Note, I applied Visual Studio Service Pack 1.

Now, I get the same error, but at a different spot.

Processing Measure Group '~CaseDetail ~MG'.
Start time 5/26/2007 2:14:58 AM
Processing Partition '~CaseDetail ~MG'.
Start time 5/26/2007 2:14:58 AM
SQL queries 1 ( *** BEFORE VISUAL STUDIO SERVICE PACK 1 IT HUNG HERE ***)
SELECT DATAID ( [Adventure Works].[$Customer].[Customer] )
AS [tomer0_0], KEY ( [Adventure Works].[$Customer].[Number of Cars Owned],0 )
AS [tomer0_1], KEY ( [Adventure Works].[$Customer].[Number of Children At Home],0 )
AS [tomer0_2], KEY ( [Adventure Works].[$Customer].[Total Children],0 )
AS [tomer0_3]
FROM [Adventure Works].[$Customer]
GROUP BY [tomer0_0],[tomer0_1],[tomer0_2],[tomer0_3],[tomer0_0]
ORDER BY DATAID ( [Adventure Works].[$Customer].[Customer] )
ASC
Processing Dimension 'Subcategory Basket Analysis'.
Start time 5/26/2007 2:15:02 AM
Processing Dimension Attribute '(All)' completed successfully.
Start time: 5/26/2007 2:15:02 AM; End time: 5/26/2007 2:15:02 AM; Duration: 0:00:00
Processing Dimension Attribute 'Attribute Name'.
Start time 5/26/2007 2:15:02 AM ( *** AFTER VISUAL STUDIO SERVICE PACK 1 IT HANGS HERE ***)

Does anyone have any ideas on how to proceed?

Thank you very much.

AIM

Andre_Mikulec@.Hotmail.com

Advance Problem , Just for the expeirinces

Forbidden
You don't have permission to access /localhost:8080/sas/(S(b2vekk55equugyjebolothq0))/Main1.aspx on this server.

what is the solution ?

The issue appears to be that you don't have access to a web page, but you are posting in the wrong forum.

Thanks

Laurentiu

ADP or VS

I am developing a solution using Access and now i come to know that SQL Express is free for unlimited connection so i want to switch to SQL as backend for scalability and VPN support.

But i want to make sure i make the right choice on the frontend too. Could anyone help give some insight on the pros and cons of using ADP or Visual Studio Express as frontend?

Many thanks.

ADP is fine for smaller groups of people, and if you have a ready supply of people that can troubleshoot the applications if they have an issue. Visual Studio applications can scale much higher and are better supported.

Buck Woody

|||As Buck Woody says, but remember as long as you get a solid back end, it would be really easy to add a web front end also.

I find a nice mix of Access / VS / Web apps perform nicely together for various needs.

ADP or VS

I am developing a solution using Access and now i come to know that SQL Express is free for unlimited connection so i want to switch to SQL as backend for scalability and VPN support.

But i want to make sure i make the right choice on the frontend too. Could anyone help give some insight on the pros and cons of using ADP or Visual Studio Express as frontend?

Many thanks.

ADP is fine for smaller groups of people, and if you have a ready supply of people that can troubleshoot the applications if they have an issue. Visual Studio applications can scale much higher and are better supported.

Buck Woody

|||As Buck Woody says, but remember as long as you get a solid back end, it would be really easy to add a web front end also.

I find a nice mix of Access / VS / Web apps perform nicely together for various needs.

ADO.NET or OLEDB connection/recordset?

My package needs to be a High Performance (target: 150,000 rows in 30 minutes) ETL solution. We are using all MS technologies - SSIS, SQL 2005, BIDS, etc. I need to loop the recordset executed by a Stored Proc in a Execute SQL Task in a Source Script Component.

If I use an ADO.NET Connection Manager, here is the code in the Source Script Component Public Overrides Sub CreateNewOutputRows()

Code 1

Dim sqlAdapter As New SqlDataAdapter

Dim dataRow As Data.DataRow

Dim ds As DataSet = CType(Me.Variables.rsSomeResultset, DataSet)

sqlAdapter.Fill(ds)

Iget: Error: System.InvalidCastException: Unable to cast object of type 'System.Object' to type 'System.Data.DataSet'.

Code 2

Dim oledbAdapter As New OleDb.OleDbDataAdapter

Dim dataTable As DataTable

oledbAdapter.Fill(dataTable, Me.Variables.rsSomeResultset)

Error: System.ArgumentException: Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb

It works all right when I use an OLEDB Connection Manager with the second code sample.

Question: In order to extract the maximum performance, wouldn't it be preferred to use ADO.NET with SqlClient Provider in an all SQL Server 2005 environment? Or will an OLEDB Connection provide comparable or equal performance?

If so, what code can I use? Since the recordset returned by the Stored Proc (in the Execute SQL Task) can only be captured in a System.Object variable and you can only use the overload of the Fill() method of the OleDbDataAdapter to accept an ADO Recordset or Record object.

There was a post recently that compared some of the connection types, but thanks to the lovely search functionality, I can't find it. If anyone else has it, please post it to this thread.

In general, I don't think you are going to see a significant performance difference between ADO.NET and OLEDB against SQL Server.

That being said, if you want to do some further research into the problem, try adding a message box to your script to display the type of the variable.

Code Snippet

System.Windows.Forms.MessageBox.Show(Me.Variables.rsSomeResultset.ToString())

|||You Code 1 snippet doesn't look right for ADO.NET. You shouldn't be trying to call sqlAdapter.Fill(ds). Try Dim dataTable as DataTable = ds.Tables(0) instead. I'd guess that OLE DB would be faster. Please post your findings.
|||

Do you mean this discussion?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1985034&SiteID=1

ADO.NET or OLEDB connection/recordset?

My package needs to be a High Performance (target: 150,000 rows in 30 minutes) ETL solution. We are using all MS technologies - SSIS, SQL 2005, BIDS, etc. I need to loop the recordset executed by a Stored Proc in a Execute SQL Task in a Source Script Component.

If I use an ADO.NET Connection Manager, here is the code in the Source Script Component Public Overrides Sub CreateNewOutputRows()

Code 1

Dim sqlAdapter As New SqlDataAdapter

Dim dataRow As Data.DataRow

Dim ds As DataSet = CType(Me.Variables.rsSomeResultset, DataSet)

sqlAdapter.Fill(ds)

Iget: Error: System.InvalidCastException: Unable to cast object of type 'System.Object' to type 'System.Data.DataSet'.

Code 2

Dim oledbAdapter As New OleDb.OleDbDataAdapter

Dim dataTable As DataTable

oledbAdapter.Fill(dataTable, Me.Variables.rsSomeResultset)

Error: System.ArgumentException: Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb

It works all right when I use an OLEDB Connection Manager with the second code sample.

Question: In order to extract the maximum performance, wouldn't it be preferred to use ADO.NET with SqlClient Provider in an all SQL Server 2005 environment? Or will an OLEDB Connection provide comparable or equal performance?

If so, what code can I use? Since the recordset returned by the Stored Proc (in the Execute SQL Task) can only be captured in a System.Object variable and you can only use the overload of the Fill() method of the OleDbDataAdapter to accept an ADO Recordset or Record object.

There was a post recently that compared some of the connection types, but thanks to the lovely search functionality, I can't find it. If anyone else has it, please post it to this thread.

In general, I don't think you are going to see a significant performance difference between ADO.NET and OLEDB against SQL Server.

That being said, if you want to do some further research into the problem, try adding a message box to your script to display the type of the variable.

Code Snippet

System.Windows.Forms.MessageBox.Show(Me.Variables.rsSomeResultset.ToString())

|||You Code 1 snippet doesn't look right for ADO.NET. You shouldn't be trying to call sqlAdapter.Fill(ds). Try Dim dataTable as DataTable = ds.Tables(0) instead. I'd guess that OLE DB would be faster. Please post your findings.
|||

Do you mean this discussion?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1985034&SiteID=1

ADO

What does the migration look like for moving an existing ADO VBasic 6 Client solution using SQL Server 2000 look like and will it remain largely compatable on the server end with SQL server 2005?

We are looking at moving it to .NET for Studio 2005, and i am concerned about massive recoding on the client side to deal with security issues between Server 2000/2005 and 2008.

If you're taking the leap from vb6 to .Net, then yes it's a leap. It will require full recoding with the exception of queries.

This is why detailed and accurate documentation is an ethical imperative. Without it, look forward to 6 months of investigation before you even begin to think about the code.

P.S. There are code converters floating around that will traspose the code but I can't comment on their accuracy.

Adamus

|||

jason.bronner wrote:

What does the migration look like for moving an existing ADO VBasic 6 Client solution using SQL Server 2000 look like and will it remain largely compatable on the server end with SQL server 2005?

We are looking at moving it to .NET for Studio 2005, and i am concerned about massive recoding on the client side to deal with security issues between Server 2000/2005 and 2008.

If the issue is VB6 going against SQL Server 2005 there should not be any significant issue there. Test it obviously, but most code should run just fine. As to the migration to .NET, yes, that is a much bigger leap, but the leap is an issue regardless - it's not just an ADO vs. ADO.NET thing. VB.NET is a different animal than VB6 (heck, many VB6 developers actually find it easier to go to C#), and VB6 to C# has obvious issues too. Such a migration is a good time to look at your approach, as straight porting of code may not make any real sense. How you want to arrange components, differences in data access, differences in how interfaces work vs. COM, and a host of other things are reasons to rethink your approach to the appllication.

ADO

What does the migration look like for moving an existing ADO VBasic 6 Client solution using SQL Server 2000 look like and will it remain largely compatable on the server end with SQL server 2005?

We are looking at moving it to .NET for Studio 2005, and i am concerned about massive recoding on the client side to deal with security issues between Server 2000/2005 and 2008.

If you're taking the leap from vb6 to .Net, then yes it's a leap. It will require full recoding with the exception of queries.

This is why detailed and accurate documentation is an ethical imperative. Without it, look forward to 6 months of investigation before you even begin to think about the code.

P.S. There are code converters floating around that will traspose the code but I can't comment on their accuracy.

Adamus

|||

jason.bronner wrote:

What does the migration look like for moving an existing ADO VBasic 6 Client solution using SQL Server 2000 look like and will it remain largely compatable on the server end with SQL server 2005?

We are looking at moving it to .NET for Studio 2005, and i am concerned about massive recoding on the client side to deal with security issues between Server 2000/2005 and 2008.

If the issue is VB6 going against SQL Server 2005 there should not be any significant issue there. Test it obviously, but most code should run just fine. As to the migration to .NET, yes, that is a much bigger leap, but the leap is an issue regardless - it's not just an ADO vs. ADO.NET thing. VB.NET is a different animal than VB6 (heck, many VB6 developers actually find it easier to go to C#), and VB6 to C# has obvious issues too. Such a migration is a good time to look at your approach, as straight porting of code may not make any real sense. How you want to arrange components, differences in data access, differences in how interfaces work vs. COM, and a host of other things are reasons to rethink your approach to the appllication.