Sql Replication with Ravendb

[michelle sollicito]

I have tried to create a sql replication script and I am getting errors but they really do not tell me much.  How do I work out what is wrong with my script?  Is there a detailed description of how Sql replication works somewhere?

This link is the only write up I could find out about how it all works https://ravendb.net/docs/article-page/3.5/csharp/server/bundles/sql-replication/basics

session.Store(new SqlReplicationConfig

{

Id = "Raven/SqlReplication/Configuration/Appointments",

Name = "Appointments",

ConnectionString = @"

data source=1XX.XX.XXX.XX;initial catalog=XXXXX;user id=XXXX;password=XXXXXX;MultipleActiveResultSets=True; ",

FactoryName = @"System.Data.SqlClient",

RavenEntityName = "Appointments",

SqlReplicationTables =

{

new SqlReplicationTable

{

TableName = "DoctorSchedules", DocumentKeyColumn = "Id"

}

},

Script = @"

var appData = {

        SchedulerId: SchedulerId,

                                CStartDate: CStartDate,

                    

            CEndDate: CEndDate,

                    

            StartDate: StartDate,

                    

            EndDate: EndDate,

                    

            Text: Text,

                                Holder: Holder,

                    

            Patient: Patient,

                                RecType: RecType,

                                RecPattern: RecPattern,

                                _StartDate: _StartDate,

                    

            _EndDate: _EndDate,

                                State: State,

                                DoctorId: DoctorId,

                                EventLength: EventLength,

                    

            EventPid: EventPid,

};

replicateToDoctorSchedules(appData);"

});

[Oren Eini (Ayende Rahien)]

And what errors did you get?

Hibernating Rhinos Ltd  

Oren Eini l CEO l Mobile: + 972-52-548-6969

Office: +972-4-622-7811 l Fax: +972-153-4-622-7811

 

--
You received this message because you are subscribed to the Google Groups "RavenDB - 2nd generation document database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Grisha Kotler]

Hi,

"this" is missing before the property names:

For example:

SchedulerId: this.SchedulerId,

Hibernating Rhinos Ltd  

Grisha Kotler l RavenDB Core Team Developer l Mobile: +972-54-586-8647

Office: +972-4-622-7811 l Fax: +972-153-4-622-7811

RavenDB paving the way to "Data Made Simple" http://ravendb.net/

--

[Oren Eini (Ayende Rahien)]

Even if it is missing, it is still there and implicit, no?

Hibernating Rhinos Ltd  

Oren Eini l CEO l Mobile: + 972-52-548-6969

Office: +972-4-622-7811 l Fax: +972-153-4-622-7811

 

[michelle sollicito]

yes I dont think that is the problem right?

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+u...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "RavenDB - 2nd generation document database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+u...@googlegroups.com.

[Oren Eini (Ayende Rahien)]

What does the log say?

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+unsubscribe@googlegroups.com.

[michelle sollicito]

I tried making it a much simpler piece of code.  I no longer get errors but the data does not appear in sql server

//dbo.Visits

session.Store(new SqlReplicationConfig 

{

Id = "Raven/SqlReplication/Configuration/Visits",

Name = "Visits",

ConnectionString = @"

   data source=146.88.108.39;initial catalog=ReplMID;user id=test;password=Myidealdoctor01;MultipleActiveResultSets=True; ",

 FactoryName = @"System.Data.SqlClient",

 RavenEntityName = "Visits",

 SqlReplicationTables = 

    { 

         new SqlReplicationTable 

        { 

            TableName = "Visits", 

           DocumentKeyColumn = "Id"

       } 

 },

 Script = @" 

 var appData = {

Id : documentId , 

};

replicateToVisits(appData); 

}); 

[michelle sollicito]

By the way there are no logs since November 30

[michelle sollicito]

I do not really want the answers - I want to know how to get the skills and tools to answer this myself?   There must be some kind of debugger for these scripts surely?  Some more meaningful error messages somewhere?

[Oren Eini (Ayende Rahien)]

Manage Your Server > Admin Logs:

http://live-test.ravendb.net/studio/index.html#admin/settings/adminLogs

Hibernating Rhinos Ltd  

Oren Eini l CEO l Mobile: + 972-52-548-6969

Office: +972-4-622-7811 l Fax: +972-153-4-622-7811

 

--

[Grisha Kotler]

Yes. You're right.

Hibernating Rhinos Ltd  

Grisha Kotler l RavenDB Core Team Developer l Mobile: +972-54-586-8647

Office: +972-4-622-7811 l Fax: +972-153-4-622-7811

RavenDB paving the way to "Data Made Simple" http://ravendb.net/

[michelle sollicito]

So the problem was that the Simulation tools were not producing ANY output just because I had a couple of carriage returns in the script.  Really bad form guys!!

Once I realized this and removed them I am now getting useful "debug" info which tells me why my scripts are not working.

However now I have another problem.  

The reason my scripts are not working is because my enum types are coming through as strings whereas sql server needs the int equivalents.  I thought I could use a custom function or transform to do that but I am having trouble working out how to do that.  Any tips?  I tried using this example here but I need to make the custom function work for all documents, not just one as in this example.. and I need it to run every time the replication script runs - how do I put it into my replication script?

Here is a sample replication script.  I need to put three custom functions in the place of 1,2 and 3 below, to transform the enums from strings into ints:

 var appData = {

Activation : 1 ,

Alcohol : Alcohol ,

AlcoholFrequency : AlcoholFrequency ,

Allergies : Allergies ,

AllergyOther : AllergyOther ,

ChildImmunizations : ChildImmunizations ,

ConditionOther : ConditionOther ,

Conditions : Conditions ,

ConditionsMaternal : ConditionsMaternal ,

ConditionsMaternalOther : ConditionsMaternalOther ,

ConditionsPaternal : ConditionsPaternal ,

ConditionsPaternalOther : ConditionsPaternalOther ,

ConditionsSiblings : ConditionsSiblings ,

ConditionsSiblingsOther : ConditionsSiblingsOther ,

CurrentMeds : CurrentMeds ,

DrugAllergyListString : DrugAllergyListString ,

EndedAt : EndedAt ,

FatherDeathAge : FatherDeathAge ,

FatherLiving : FatherLiving ,

Female : Female ,

HasNoDrugAllergies : 2 ,

HasNoMedications : 3 ,

HeightFeet : HeightFeet ,

HeightInches : HeightInches ,

MotherDeathAge : MotherDeathAge ,

MotherLiving : MotherLiving ,

NoKnownDrugAllergy : 1 ,

NonDrugAllergyList : NonDrugAllergyList ,

Pregnant : Pregnant ,

RavenId : RavenId ,

Smoker : Smoker ,

SmokerForYears : SmokerForYears ,

StartedAt : StartedAt ,

Summary : Summary ,

Surgeries : Surgeries ,

SurgeryOther : SurgeryOther ,

TakingMeds : TakingMeds ,

TetanusDateKnown : TetanusDateKnown ,

TetanusWeeksAgo : TetanusWeeksAgo ,

Weight : Weight 

};

replicateToAssessments(appData); 

For example 1 should be something like this:

exports.Activate = function(ActivationString)

{

    if (ActivationString == "Unknown")

    {

        return(-1);

    }

    if (ActivationString == "Inactive")

    {

        return(0);

    }

    if (ActivationString == "Active")

    {

        return(1);

    }

    if (ActivationString == "Unverified")

    {

        return(20);

    }

    if (ActivationString == "Invited")

    {

        return(30);

    }

    return(-1);

}

But I tried to invoke it by saying:

var appData = {

Activation : Activate(Activation) ,

.. but that causes errors

Yes. You're right.

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+u...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "RavenDB - 2nd generation document database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+u...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "RavenDB - 2nd generation document database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+u...@googlegroups.com.

[Oren Eini (Ayende Rahien)]

What do you mean, carriage returns not showing debug info?

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+unsubscribe@googlegroups.com.

[Oren Eini (Ayende Rahien)]

That should work, what do you get when you run it?

For that matter, what happens if you put the function directly in the script?

Hibernating Rhinos Ltd  

Oren Eini l CEO l Mobile: + 972-52-548-6969

Office: +972-4-622-7811 l Fax: +972-153-4-622-7811

 

On Wed, Dec 7, 2016 at 4:24 PM, michelle sollicito <michelle....@gmail.com> wrote:

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+unsubscribe@googlegroups.com.

[michelle sollicito]

I have only put the functions directly into the script.  It is whenever there is a spurious carriage return in the script that I do not get any messages / debug info when I try to use Tools-> Simulation (Simulate/Test Transaction)

Right now I have managed to get past that problem but although the replication seems to work for one of my tables, and passes the Simulate/Test a number of my replication scripts are only bringing over IDs and the rest of the row is full of nulls.  With one of my tables, I got this same thing at first but later the data got filled in, but with these other scripts the rest of the data is not filling in.

[michelle sollicito]

Is there any way to track what replication is doing?  It appears to occasionally copy over a row of data but it seems to happen maybe once every hour right now - that does not seem right to me?

Apart from that all the other data is full of nulls

One of the scripts worked, but at first it had rows of null data and then later it filled in the data - it now looks like this

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

This is an example of one of the rows that is full of nulls - it appears that maybe the replication is two pass?  the first pass puts nulls in many fields and later it comes in and fills in the rest of the data?  But the second pass is not happening for some reason?

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

Any ideas?

[Oren Eini (Ayende Rahien)]

If you put the value directly into the script, you can't use exports.FunctionName.

Just use: function FunctionName(), normally.

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+unsubscribe@googlegroups.com.

[Oren Eini (Ayende Rahien)]

No, it is one pass, likely properly is that your function is defined incorrectly

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+unsubscribe@googlegroups.com.

[michelle sollicito]

The problem is that eventually the data DOES get there - so it cannot be defined wrongly - it just takes forever to update the values from nulls to actual values

[Oren Eini (Ayende Rahien)]

How many docs do you have in the system?

What do the logs say?

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+unsubscribe@googlegroups.com.

[michelle sollicito]

There is nothing in the logs.

10,000 Appointments    297,000 Patients   50,000 Visits  but I am trying replication with our smaller document collections - with 30 - 150 docs in them

[Dan Bishop]

One thing that I have noticed with SQL replication is that RavenDB will batch quite a few updates together, which could result in long delays to see the committed transactions appear in SQL. I would suggest that you verify whether the data is in fact in SQL, just in an uncommitted state. To do this you can run some SELECT or COUNT statements in SQL with the suffix "WITH (READUNCOMMITTED)", for example:

SELECT COUNT(*) FROM dbo.Patients WITH (READUNCOMMITTED)

Have you checked the on-disk debug logs for RavenDB to see if there is any information there? You'd be looking for something like this:

2016-12-08 19:30:15.1404,Raven.Database.Bundles.SqlReplication.RelationalDatabaseWriter,Debug,,"Insert took: 4ms, statement: INSERT INTO [Patients] (...)..."

Dan Bishop

[michelle sollicito]

Thanks - this is along the lines of the kinds of info I needed.. however, all the ROWS are appearing in sql.. they just appear with nulls in all or most of the values.  It is not til much later that the values are filled in (and some never seem to be filled in - or maybe I just havent waited long enough yet)..    like I had a four row table contain four rows within about a minute but all the values were nulls apart from the ids - then eventually (I think one hour later) the rest of the values magically updated..  but this was with a very small resultset..

I will see if I can work out how to look at the debug logs

[Oren Eini (Ayende Rahien)]

That isn't how it works with RavenDB. We write the row to the relational db once per change.

You can reset the sql replication to make it do the work from the start.

Note that it might be that you have an old script, which replicated some of the data wrongly, fixed it, and then whenever a document changed it will update it.

You can test it by looking up a null filled row on SQL and then making a minor update to the document in RavenDB and see what it is doing.

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+unsubscribe@googlegroups.com.

[michelle sollicito]

I tried to look at the replication logs but I guess I did something wrong?  Is that url wrong?

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

[michelle sollicito]

Metrics worked at this url though http://162.218.137.46/debug/metrics?&database=MyIdealDoctorRepTest2

[Oren Eini (Ayende Rahien)]

/databases/MY_DB_NAME/debug/sql-replication-stats

But I actually meant the debug log on the server. See Manage Your Server > Admin Logs

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+unsubscribe@googlegroups.com.

[Oren Eini (Ayende Rahien)]

Those are metrics for the system database, not the right one.

To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+unsubscribe@googlegroups.com.