Replication and Different Domains

Hi guys,
We have a client who wants to set up the publisher (for merge
resplication) on a particular domain, but the subscribers will be on a
different domain. Is this even possible?
The main question was about the snapshot folder location. Is there a way
for the subscriber to get access to the folder being on a different domain?
Thanks,
Maer
You could have a look at
http://www.replicationanswers.com/InternetArticle.asp for details of
non-trusted setup. If there is a posibility of setting up a trust (or VPN)
then things are pretty straightforward.
HTH,
Paul Ibison
|||Hi Paul - Thank you so much for the article, it was extremely helpful!
Maer
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:7A661F57-4BB5-4D0C-9936-0A358E71683D@.microsoft.com...
> You could have a look at
> http://www.replicationanswers.com/InternetArticle.asp for details of
> non-trusted setup. If there is a posibility of setting up a trust (or VPN)
> then things are pretty straightforward.
> HTH,
> Paul Ibison
>

Replication / Synchronisation

Hi Guys, my first post here for quite a while.

I have a question regarding replication and or database synchronisation:

I am doing some work on a SCADA control system in .Net - (SCADA=[S]upervision, [C]ontrol And [D]ata [A]quisition)

Obviously, these are mission critical systems that must have uptimes of greater 99%.

Tradiotionally, we have used custom written database in assembler or C, but these are getting rather long in the tooth, and, in my opinion, represent a massive man-time investment developing something that already exists 'off the shelf' (for example, our custom written database, the first version of which roled out nearly 20 years ago doesn't support SQL)

So, I'm looking to see if there is an off the shelf DB that can do what we need it to do. I lean towards Microsoft because of it's integration with the .Net platform.

However, probably the most important criteria for us is that of database synchronisation - as data from the plant arrives it is stored in a database (obviously) - however, at the SAME TIME, it is sent to a back up (standby) database which is identical in every respect, except it does not 'answer' queries, it only listens and keeps it's database up to date. Then, should the 'primary' database server fall over, the standby will become active, and it is up-to-date ready to run.

Does SQL server offer such a facility that changes to a database can be immediately and automatically 'echoed' to a standby database? Automatic promotion from standby to main is not an issue, as this decision would be taken by the application software 'in front' of the database - if it cannot contact the main DB server, it will elect to start talking to the standby, thus, philisophically (sp?) the standby is now the main server.

Hope I haven't confused anyone here with my rambling?

The problem is my hunch (not being a DB guru) is that replication is NOT what I am looking for - I *think* replication does sync data and schema (correct me if i'm wrong) but not immediately - it is scheduled? Yes?

I am looking more for synchronisation - but it has to be 'on the fly' - not every 15 minutes or 8 hours etc...

If SQL server cannot do it, then it's not a major problem, since the DB access layers of the applications themselves can simply write their data to two DB's, and queue writes in a buffer if one of the partners is offline, such that they will be written to the partner when it comes online - however, I'm all for an easy life, and if the boys at Microsoft have already settled this issue, then i'm all for that!

Your advice would be appreciated.

Regards

Mark WillsAssuming you can make a few minor schema changes (like establishing Primary Keys for replicated tables), then SQL Merge replication will do what you've described. Depending on hardware, network connection, etc you can get latency down to about a minute. Then you can have two or two thousand SQL servers, with little or no difference to the application.

If you need instant or selective replication, I'd suggest letting the application do the replication, but in all other cases I'd use SQL replication.

-PatP|||If you are looking at SQL 2000, then I might consider log shipping. Log shipping runs on a schedule, yes, and that might not meet your requirements precisely, but I believe that there are ways to catch up the "standby" server in the event of disaster. Log shipping does not automate the failover process, but the commands to make it work are not overly complex.

If you are willing to look at SQL 2005, it has much better provisions for this. I cannot now remember what the name of this feature is called (on their website, it looks like it is called "Database Mirroring").

We are going to start taking our first hard loot at SQL 2005 in about 2 weeks; database mirroring is one capability we are seriously interested in. It will extend our off-site DR capabilities greatly.

Regards,

hmscott

replication

Guys,
I am new to MS SQL server replication. As of now, I am trying to spec thngs
out. What is the difference between Multiple Publisher/Multiple subscriber
scenario and Updating Subscriber scenario? Both seem to me as a Sybase DBA
as Multiple master scenario as both have the same data and replication
happens both ways(two way replication). Also, In the Updating subscriber
scenario, server A is sending data to server B via the distribution server
but server B is sending data to server A via a SQL server Queue(Queue Reader
Agent). Why is the subscriber not sending data back to the publisher via the
distribution server? Also, what is the difference between immediate
updates and queued updates? As far as I can understand everything has to
be queued in order to send them to the other side.
Can someone please answer my questions?
Regards
Jaideep
Jaideep,
as a bit of background...
In normal transactional replication, an inserted record on the subscriber
will be replicated to the subscriber via the distribution database
(MSrepl_commands: use sp_browsereplcmds to view them). This is unidirectional
only. For queued updating subscribers the data flow is bi-directional, and
data changes are stored in a table on the subscriber (use sp_replqueuemonitor
to read this MSreplication_queue table). It has to be this way, because the
queue is available for offline access - ie there is the posibility that the
subscriber won't be able to access the publisher\distributer. In the case of
immediate updating subscribers the flow is again bidirectional, but there is
no need to save the command anywhere, because the subscriber enters a
distributed transaction.
I hope this clears up any confusion.
Regards,
Paul Ibison, SQL Server MVP

Replication

Hi guys,
Is it possible to replicate in MSSQL SERVER standard edition and be
supported? Or do I need to buy enterprise edition.

Regards

Jaideepjai (dba_sybase2003@.yahoo.com) writes:
> Hi guys,
> Is it possible to replicate in MSSQL SERVER standard edition and be
> supported? Or do I need to buy enterprise edition.

You can do replicaiton in Standard Edition.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yeah I kind of knew that replication is possible in the standard
edition but will MIcrosoft support it.

Regards

Jaideep|||jai (dba_sybase2003@.yahoo.com) writes:
> Yeah I kind of knew that replication is possible in the standard
> edition but will MIcrosoft support it.

Yes.

Please check Books Online, the book SQL Server Architecture ->
Implementation Details -> Editions of SQL Server 2000 ->
Features Supported by the Editions of SQL Server 2000.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
I am new to sql server replication but I have worked with sybase
replication. At present, the machine housing the sql server has 2 g of
memory with 1.4 g for sql server. How much more (memory), would I need
if I make the same machine work as publisher as well as the
distributor?

Regards

Jaideep|||jai (dba_sybase2003@.yahoo.com) writes:
> Erland,
> I am new to sql server replication but I have worked with sybase
> replication. At present, the machine housing the sql server has 2 g of
> memory with 1.4 g for sql server. How much more (memory), would I need
> if I make the same machine work as publisher as well as the
> distributor?

You will have to ask the friendly people in
microsoft.public.sqlserver.replication about that. My own experience
of replication is very thin. My gut feeling is that it would depend a
lot of the data volume you intend to replicate.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"jai" <dba_sybase2003@.yahoo.com> wrote in message
news:1124737698.551148.233410@.z14g2000cwz.googlegr oups.com...
> Erland,
> I am new to sql server replication but I have worked with sybase
> replication. At present, the machine housing the sql server has 2 g of
> memory with 1.4 g for sql server. How much more (memory), would I need
> if I make the same machine work as publisher as well as the
> distributor?

Howmuch data do you expect to replicate at any time?

Basically all that happens is the log reader puts rows into a distribution
DB which then gets used to distribute them to the subscribers.

You probably have enough RAM since you can't get much more in Standard
version anyway.

> Regards
> Jaideep

Replicating User Defined Data Types

Hey guys,
Thanks so much for this great newsgroup btw, helped me hugely through doing
replication via handheld units etc.
I'm running into a bit of a problem between server 2 server replication.
Both databases have user defined data types. All the tables using them are
fine, but once the snapshot is applied to the subscriber, and the replication
is hooked up to the publisher, all the data types are converted to their base
types (char 10 etc)
Is there any way to stop this? The subscriber needs to keep the user defined
data types.
I noticed in the publication wizard there is an option that says "Keep the
existing table unchanged" but the default value for this is "DROP the
existing table and re-create it" this is all under the Snapshot tab of the
article properties.
If I set it to "Keep the existing table unchanged" will that fix my problems
or is that not the proper fix for this issue?
Thanks and take care!
Simon,
on the article propertuies at the bottom there is the option to change user
defined datatypes to base types and by default this is enabled. Also, you
can add the user defined types themselves to a prescript.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replicating Primary Key value onto the Foreign key value

Hey Guys,

I am just new to SQL Server. I have just created 2 tables Table1 and Table2. Table1 has a primary key (Table1.ID), which has been set on identity values.Both tables have different columns except the ID columns.

I have also used Table1.ID as foreign key and primary key on Table2 as Table2.ID.

I am just wondering guys if it is possible that when I enter or insert a data on Table1, can the same value (Table1.ID) be instantaneously copied to Table2.ID for the next data entry for Table2.

Please could you show me how this can be done using SQL Server Express 2005.

After you do the insert into table 1

INSERT INTO Table1 (<ColumnList>) VALUES(<ValueList>)

You can capture the newly created Identity value by doing the following

SET @.Variable = Scope_Identity()

You would then use this value for the ID column when you insert the row in Table2.

|||Thank you very much for your response. Like I expressed I am just new to these, and I hope you do pardon my ignorance as I am still learning.

I do follow the first part of your response but kinda clueless from the
'SET @. ..........................

Thanks for your patience.

|||

The SET statement captures the identity created when data was inserted in table 1.

That is the value to use in Table2.ID.

Code Snippet

DECLARE @.Variable <datatype of ID column>

INSERT INTO Table1 (<ColumnList>) VALUES(<ValueList>)

SET @.Variable = Scope_Identity()

INSERT INTO Table2 (ID, <remaining ColumnList>) VALUES(@.Variable, <remaining values>)

|||Thank you very much for your response. I'm just wondering if there is a way the code you provided above can be incorporated into a trigger. Thanks once again.
|||

If you want to do it in a trigger, there is a different method to use.

You would put the trigger on table1. Inside that insert trigger, you would use the virtual table named inserted which will contain a row for each row just inserted into table1, including the newly generated identity value for each row.

If you post your table create staements, and a descripttion of what you want to do, we can likely help you in writing the trigger.

|||

Code Snippet

IF OBJECT_ID ('dbo.Table1InsertTrigger', 'TR') IS NOT NULL

DROP TRIGGER dbo.Table1InsertTrigger

GO

CREATE TRIGGER dbo.Table1InsertTrigger

ON dbo.Table1

AFTER INSERT

IF ( @.@.ROWCOUNT = 0 )

RETURN

INSERT INTO Table2 (ID, <remaining ColumnList>)

SELECT ID, <remaining ColumnList>

FROM INSERTED

GO

|||Hi,

Here is the create statement for the tables.

CREATE TABLE [T1] (
[ID] IDENTITY(1000, 2) NOT NULL,
[NAME] VARCHAR(100),
[SERV_TYPE] VARCHAR(40),
[NWT] VARCHAR(40),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [T1_ID] PRIMARY KEY ([T1_ID])
)
GO

CREATE TABLE [T2] (
[ID] INT NOT NULL,
[CONTACT_TYPE] VARCHAR(1),
[CONTACT_NAME] VARCHAR(40),
[CONTACT_1] VARCHAR(100),
[CONTACT_2] VARCHAR(50),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [PK_T2] PRIMARY KEY ([ID])
)
GO

ALTER TABLE [T2] ADD CONSTRAINT [T1_T2]
FOREIGN KEY ([ID]) REFERENCES [T1] ([ID])
GO

Thanks for you help.
|||

(Unless I am confused, which does happen) What you are wanting to do doesn't really make sense in SQL Server storage. To do this, you would be creating partial rows. It would likely be a bad practice to automatically create a row with null values that might just end up as a useless row.

What you are describing sounds more of a task that might be done in a user interface, in order to display a new row for a person to edit. If you want to create a row in T1 and T2 simultaneously, you could use a stored procedure and build the rows together in the context of a transaction.

|||Well, Louis. How can I get T1.ID automatically available or copied into T2.ID when I have entered data into in T2.
|||Hi,

Here is the create statement for the tables.

CREATE TABLE [T1] (
[ID] IDENTITY(1000, 2) NOT NULL,
[NAME] VARCHAR(100),
[SERV_TYPE] VARCHAR(40),
[NWT] VARCHAR(40),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [T1_ID] PRIMARY KEY ([T1_ID])
)
GO

CREATE TABLE [T2] (
[ID] INT NOT NULL,
[CONTACT_TYPE] VARCHAR(1),
[CONTACT_NAME] VARCHAR(40),
[CONTACT_1] VARCHAR(100),
[CONTACT_2] VARCHAR(50),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [PK_T2] PRIMARY KEY ([ID])
)
GO

ALTER TABLE [T2] ADD CONSTRAINT [T1_T2]
FOREIGN KEY ([ID]) REFERENCES [T1] ([ID])
GO

I still have have not had this to work so far. My problem still remains how can I get T1.ID automatically available or copied into T2.ID, when I have opened the table, T2 for data entry.

Thanks you guys for your help.|||

Here ya go.

But like Louis, I wonder why you're creating a placeholder. I would think the application would have the ID of the associated T1 entry when the user is working on the T2 entry.

Code Snippet

CREATE TABLE [T1] (

[ID] int IDENTITY(1000, 2) NOT NULL,

[NAME] VARCHAR(100),

[SERV_TYPE] VARCHAR(40),

[NWT] VARCHAR(40),

[ROWVERSION] TIMESTAMP,

CONSTRAINT [T1_ID] PRIMARY KEY ([ID])

)

GO

CREATE TABLE [T2] (

[ID] INT NOT NULL,

[CONTACT_TYPE] VARCHAR(1),

[CONTACT_NAME] VARCHAR(40),

[CONTACT_1] VARCHAR(100),

[CONTACT_2] VARCHAR(50),

[ROWVERSION] TIMESTAMP,

CONSTRAINT [PK_T2] PRIMARY KEY ([ID])

)

GO

ALTER TABLE [T2] ADD CONSTRAINT [T1_T2]

FOREIGN KEY ([ID]) REFERENCES [T1] ([ID])

GO

IF OBJECT_ID ('dbo.Table1InsertTrigger', 'TR') IS NOT NULL

DROP TRIGGER dbo.Table1InsertTrigger

GO

CREATE TRIGGER dbo.Table1InsertTrigger

ON dbo.T1

AFTER INSERT

AS

IF ( @.@.ROWCOUNT = 0 )

RETURN

INSERT INTO T2 (ID)

SELECT ID

FROM INSERTED

GO

insert into t1( [NAME] , [SERV_TYPE], [NWT] )

values ('Name1', 'Service1', 'what is nwt?')

select * from t1

select * from t2