Clustering - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Clustering

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-21 : 06:41:58

Is this possible with Express Edition.

We have two servers both running SQL 2005 express. Is there anyway that any changes on one could be replicated on the other?

Thanks

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-21 : 08:17:32

You can do replication, check out this link:
http://technet.microsoft.com/en-us/library/ms165700.aspx

Future guru in the making.

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 09:02:13

Not really; SQL Server Express can only be a Subscriber MeThinks ...

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-21 : 09:37:35

I think you're right, you would need at least one full version to act as the publisher.

Our data is updated at most perhaps twice per day, would something like the following work.

1. All updates made on server 1
2. Trigger detects insert/update/delete on server 1 and runs a stored proc which backs up the database to file.
3. Stored procedure calls a second stored procedure on server 2 which restores the database from the file.

Would this work - can an SP on 1 server call an SP on another?

Thanks

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 11:13:56

You could just write an Insert/Update/Delete trigger which did:


DELETE T
FROM RemoteServer.RemoteDatabase.dbo.RemoteTable AS T
JOIN deleted D
ON D.MyPK = T.MyPK

INSERT RemoteServer.RemoteDatabase.dbo.RemoteTable
SELECT *
FROM inserted

... provided there were no foreign key issues

Kristen

mikewa
Microsoft SQL Server Product Team

84 Posts

Posted - 2007-09-21 : 11:20:54

HI Mondeo

Since the thread is titled "Clustering" I'll first comment on that...Clustering is a High Availability feature that is not available in SQL Express.

As you've found, SQL Express supports participating in replication as a subscriber, but you would need one of the higher level Editions, say Workgroup, to act as a publisher. The workaround you suggest is possible; SQL Express support running remote queries in a number of different ways, such as Linked Servers. You may also want to explore the use of RDO, a programming model designed for data access across servers.

Finally, I want to caution you about "roll your own" replication solutions. If you needs are really as simple as just moving changes from Server 1 to Server 2, the solution is fairly simple, but will you be moving changes from Server 2 to Server 1 as well? If so, how are you going to handle when the same record has been changed on Server 1 and Server 2 so that you don't lose data? How are you going to track deleted records? (Since they're deleted they no longer exist for you to "move".) Getting replication right is actually quite hard, which is why we have a whole team of people dedicated to making it work right. If your needs are complex, it may be cheeper in the long run to just purchase Workgroup Edition rather than roll your own. I've seen a lot of people try to roll thier own replication solution thinking it would be simple and would save them money, they end up spending a lot of time and money trying to get it right, so it's worth planning ahead here.

Regards,
Mike Wachal
SQL Express

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-25 : 06:06:44

Hi Mike,

Thanks for that.

"If you needs are really as simple as just moving changes from Server 1 to Server 2, the solution is fairly simple"

This is correct, its all I need to do. I want server 2 to be effectively read only and I need to move all data from server 1 to 2, twice maybe three times a day.

Whats my best option?

Thanks a lot.

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 06:35:04

Anything wrong with the trigger I suggested?

Failing that you could do:

-- Delete stale records
DELETE FROM Target
WHERE Source-no-longer-exists

-- Freshen changed records
UPDATE Target
FROM Source
WHERE Any-columns-are-changed

-- Create new records
INSERT INTO Target
WHERE not-exists-on-Target

We use scripts that do this sort of work to get data between Production and Test databases, which works well, but you need to do the deletes and updates/inserts in opposite orders of foreign key dependency levels, and even then you may have some FK troubles where some changes have been made which are mutually interdependent.

Kristen

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-25 : 07:17:55

Hi Kirstin,

I did look at your suggestion, but I confess I didn't understand it completely! I haven't used triggers before, what is deleted and inserted? Are they tables generated on the fly by the trigger?

Thanks

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-26 : 04:27:36

quote:
Originally posted by Kristen

You could just write an Insert/Update/Delete trigger which did:

DELETE T
FROM RemoteServer.RemoteDatabase.dbo.RemoteTable AS T
JOIN deleted D
ON D.MyPK = T.MyPK

INSERT RemoteServer.RemoteDatabase.dbo.RemoteTable
SELECT *
FROM inserted

... provided there were no foreign key issues

Kristen

Hi Kristen,

I'm goint to go with this, what did you mean by foreign key issues?

Thanks

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 05:34:26

"what is deleted and inserted? Are they tables generated on the fly by the trigger?"

Yup, pseudo tables available within a trigger containing the Before and After data.

If you INSERT some rows then "deleted" will be empty, if you DELETE some then "inserted" will be empty, and for an UPDATE there will be an entry in both "inserted" and "deleted" for every row.

"what did you mean by foreign key issues?"

You could delete everything from the remote table that is being changed. For an Insert the Delete operation would obviously find nothing to delete, for an Update all the records being changed would be deleted, and then re-inserted again (and for a straight Delete the records would be deleted from the Target and then the Insert would find nothing to do and not re-insert them).

However, for the Update the Delete-first then re-insert will fail if there are any records in other tables that are dependant on them, with that referential integrity declared in a Foreign Key. SQL Serer will not allow them to be deleted.

Take for example an Invoice head table with an Invoice Items table. You won't be allowed to delete the Invoice Header record if Invoice Items exist (well, that's assuming that you have a Foreign key enforcing that referential integrity requirement, and if you haven't you jolly well should!)

Kristen

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-26 : 06:12:41

Okay thanks, think I got it.

Trying to create the trigger then I got this

-- Insert statements for trigger here
DELETE T
FROM www.myserver.co.uk.dbPubMatrix.dbo.tblTestTrigger AS T
JOIN deleted D
ON D.id = T.id

INSERT www.myserver.co.uk.dbPubMatrix.dbo.tblTestTrigger
SELECT *
FROM inserted

END
GO

It says The object name 'www.myserver.co.uk.dbPubMatrix.dbo.tblTestTrigger' contains more than the maximum number of prefixes. The maximum is 3.

How to I specify the remote server with using periods .

Thanks

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 06:19:08

FROM [www.myserver.co.uk].dbPubMatrix.dbo.tblTestTrigger AS T

assuming that you have already set it up as a linked server - and if not I recommend that you do so using a name with only underscores for "punctuation"!

Kristen

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-26 : 06:49:27

Thanks.

To set up the linked server I found this

USE dbPubMatrix;
GO
EXEC sp_addlinkedserver
'NHSServer',
N'SQL Server'
GO

But how do I specify the second servers hostname or IP address?

Thanks

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 07:46:15

Usually using the Client Network Manager utility, installed as part of the SQL Client Tools. You can allocate an Alias to the remote machine, together with an IP address, or some network name that DNS can resolve, or somesuch.

Hmmm ... on SQL 2005 I think its the Server Configuration Manager

Kristen

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-26 : 12:15:12

Thanks...getting there.

Trying to run this

-- Insert statements for trigger here
DELETE T
FROM NHS.dbPubMatrix.dbo.tblTestTrigger AS T
JOIN deleted D
ON D.id = T.id

INSERT NHS.dbPubMatrix.dbo.tblTestTrigger
SELECT *
FROM inserted

END
GO

I get

Msg 213, Level 16, State 1, Procedure trgNHS, Line 21
Insert Error: Column name or number of supplied values does not match table definition.

Table 1 is

id int identity
data nchar(10)

Table 2

id int
data nchar(10)

Is that right? I want my id column in table 2 to be the same as table 1 so haven't specified itentity?

Thanks

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 15:40:13

"Insert Error: Column name or number of supplied values does not match table definition."

usually means that you have a different number of columns between the Source and the Destination tables (or the columns have different types).

An additional problem arises where the DESTINATION has matching columns, but one of them has IDENTITY property (in which case you will need to use the SET IDENTITY MyTable ON / OFF syntax)

Kristen

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-09-26 : 15:45:01

Fixed it thanks, I was trying to define the trigger on the wrong table!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources