How can I change the table schema?

I have perform merge replication in the SQL server with access database,
however I would like to change the database schema in the SQL server. How
can I do that? e.g. such as adding a field in the table or changing the data
type of the column field.
Thank you
Regards
Jabba
use sp_repladdcolumn or sp_repldropcolumn. You can also right click on your
publication, select publication properties, go to Fitler Columns and add
your columns there.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jabba" <Jabba@.discussions.microsoft.com> wrote in message
news:C10B986C-F38D-4360-B945-11184633AD78@.microsoft.com...
> I have perform merge replication in the SQL server with access database,
> however I would like to change the database schema in the SQL server. How
> can I do that? e.g. such as adding a field in the table or changing the
data
> type of the column field.
> Thank you
>
> Regards
> Jabba

How can I change the default identity_range value in Merge replication

I have set up Merge Replication and everything is fine until a subscriber
goes offline for any length of time. The issue has to do with the setting of
the @.identity_range parameter for each article.
I am configuring the publication through the SQL Management Studio (server
is SQL 2005) and realise that I can run scripts and even alter the values
through the IDE.
What I want to do is change the default value that the IDE uses when it adds
the articles. The default seems to be 1000 which isn't really enough. We
would like to change the default to something like 10000
Does anybody know where SQL 2005 would store this value?
Thanks in advance,
Andrew Stanford
It is best that you not change meta data values in system tables. I'll
provide a way to change the indentiy range value for subscribers via the UI.
Since you already defined a merge publication, access the publication
properties by right clicking on the publication name under the
Replication:Local Publications node in SQL 2005 Management Studio. In the
left pane, select Articles. In the right pane, select the article you would
like to change the identity range values for. After highlighting the
article, use the Article Properties button. Scroll down to the identity
range management section and you should be able to change the Subscriber
Range Size value from 1000 to 10000. Hit OK, OK to confirm the changes.
Note that since you have an active merge publication already defined, the
subscriber will continue to use the old identity range values until they are
exhausted. The next time the merge agent requests a new ientitiy range from
the publisher, it will start using the new subscriber range value of 10000.
If you were to create a new publication, when you define the articles that
are in the publication you are able to drill into article properties and set
the subscriber range value to something other than 1000 up front.
Hope this helps,
Tom
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew Stanford" <andrew.stanford@.bakertilly.co.uk> wrote in message
news:uUeZt4j2FHA.956@.TK2MSFTNGP10.phx.gbl...
>I have set up Merge Replication and everything is fine until a subscriber
>goes offline for any length of time. The issue has to do with the setting
>of the @.identity_range parameter for each article.
> I am configuring the publication through the SQL Management Studio (server
> is SQL 2005) and realise that I can run scripts and even alter the values
> through the IDE.
> What I want to do is change the default value that the IDE uses when it
> adds the articles. The default seems to be 1000 which isn't really enough.
> We would like to change the default to something like 10000
> Does anybody know where SQL 2005 would store this value?
>
> Thanks in advance,
> Andrew Stanford
>

How can I change my Primary Key

Hello,
I have transactional replication in place with @. 450 articles in the main
publication.
I need to modify the primary key to include an additional field.
How can I do this best?
Thanks.
~Lb
you can't without dropping your subscriptions and publications.
"Lonnye Blake Bower" <lbower@.excellerx.com> wrote in message
news:OoJcCdAMFHA.1096@.tk2msftngp13.phx.gbl...
> Hello,
> I have transactional replication in place with @. 450 articles in the main
> publication.
> I need to modify the primary key to include an additional field.
> How can I do this best?
> Thanks.
> ~Lb
>
|||Thank you...
Can I remove the table from the publication easily and then make the
changes... and finally create a new publication with just this table?
|||yes with transactional replication you can do with by using sp_droparticle.
You will have to drop the subscription(s) before you do this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lonnye Blake Bower" <lbower@.excellerx.com> wrote in message
news:%23c3blpAMFHA.3632@.TK2MSFTNGP10.phx.gbl...
> Thank you...
> Can I remove the table from the publication easily and then make the
> changes... and finally create a new publication with just this table?
>
sql

How best to do remote site replication?

It has been suggested that we consider how to replicate our production
server to a remote site that does not have a dedicated connection to our
production environment. Any and all suggestions are appreciated.
TIA
Michael MacGregor
Database Architect
SalesDriver
Michael,
assuming there's no direct connection, this type of setup is usually done
through a VPN.
Here is a reference to help you:
http://support.microsoft.com/?id=321822.
Regards,
Paul Ibison
|||Additionally, I should mention that pull subscriptions would be most
suitable.
Regards,
Paul Ibison
|||Thanks Paul.
Totally new to replication and our IT guy is proposing some really weird and
wacky ideas on how to do this, one of which is low-level bit comparison, and
subsequent updating, of backup files to use to restore a database. Makes me
shake my head in wonder.
Michael MacGregor
Database Architect
SalesDriver
|||Michael,
if this is for disaster recovery you might want to consider log-shipping.
There have been a few threads recently (last 2 weeks) comparing this to
transactional replication which might be helpful, and it is a standard way
of maintaining a database copy. Using propriety solutions like
bit-comparisons would no doubt not be supported by ms, and presumably would
require a comparison of the full database backup. Also, log shipping will
just require that you transmit the changes which are in the log to the
remote site rather than full backups.
HTH,
Paul Ibison
|||To be honest, I'm not too sure what problem this is supposed to address. We
have an active/passive SQL Server cluster which has only been required once
in over three years. In addition there is a fairly standard backup/recovery
plan in effect with regular backups, both full and transactional, performed.
The IT guy has got the idea of having another SQL Server installed at a
remote site, to act as what I'm not sure, but wants to have it as close to
the production server as possible.
I wondered about log-shipping as well, but I'm not even sure that this idea
is going to fly anyway, but thought I'd do a little research of my own
before this issue gets raised on a more formal basis, at which point in time
I can address it better then.
Would you be able to provide me with the subject titles of the threads you
refer to?
Michael MacGregor
|||Michael,
the main thread was entitled "Disaster recovery - Use Replication or Log
shipping".
HTH,
Paul Ibison

Hot Standby/Warm Standby

Hi...can someone please explain the difference between a hot standby and
warm standby...and how replication is a hot standby and log shipping is a
warm standbyIn the way that these terms are generally used, a warm standby is a solution
where you can lose a number of modifications (transaction), where a hot
standby is where you don't lose any transactions at all. I'm talking about
committed transactions, btw. Log shipping is warm as you do the log backup
perhaps every 5 minutes, so you can lose 5 minutes worth of data. Cluster is
hot, as both nodes work against the same data. Replication is rather hot, as
the replication latency is in the vicinity of a few seconds.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Terry" <spankme@.nospam.com> wrote in message
news:e4sKB1UrDHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi...can someone please explain the difference between a hot standby and
> warm standby...and how replication is a hot standby and log shipping is a
> warm standby
>|||This is a multi-part message in MIME format.
--=_NextPart_000_06CC_01C3AD24.2B5C8F20
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
A hot standby will take over from the failed server automatically and in a
short time without human intervention. A clustered install of SQL Server is
an example of a hot standby.
A warm standby takes longer and does require human intervention. Both
methods you described are warm standby's. However, it can be debated as to
whether replication is a standby or not.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Terry" <spankme@.nospam.com> wrote in message
news:e4sKB1UrDHA.2584@.TK2MSFTNGP09.phx.gbl...
Hi...can someone please explain the difference between a hot standby and
warm standby...and how replication is a hot standby and log shipping is a
warm standby
--=_NextPart_000_06CC_01C3AD24.2B5C8F20
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

A hot standby will take over from the =failed server automatically and in a short time without human =intervention. A clustered install of SQL Server is an example of a hot =standby.
A warm standby takes longer and does =require human intervention. Both methods you described are warm standby's. =However, it can be debated as to whether replication is a standby or not.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Terry" wrote in message news:e4sKB1UrDHA.2584=@.TK2MSFTNGP09.phx.gbl...Hi...can someone please explain the difference between a hot standby andwarm standby...and how replication is a hot standby and log shipping is =awarm standby

---

--=_NextPart_000_06CC_01C3AD24.2B5C8F20--|||I might add that hardware level replications (not SQL replication) such as
EMC SRDF in the synchronous mode can also be considered as a hot standby
solution.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Terry" <spankme@.nospam.com> wrote in message
news:e4sKB1UrDHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi...can someone please explain the difference between a hot standby and
> warm standby...and how replication is a hot standby and log shipping is a
> warm standby
>

HostName and ReinitializeSubscription

Hello all,

I'm having trouble with my Merge Replication setup in regards to changing the HostName property of the SqlCeReplication object. As we all know, if you modify the HostName value, you must then call the ReinitializeSubscription(true) method before calling Synchronize(). Unfortunately, this isn't working for me. Following this sequence of steps consistently results in error messages saying that I need to either call ReinitializeSubscription, or change my HostName back to it's original value. Has anyone ever experienced this problem?

Regards,

Rob Tiffany

Hey Rob,

You are correct, once you specify a HostName value for a subscriber, if you want to change that value later, you must call SqlCeReplication.ReinitializeSubscription(True) followed by .Synchronize() to effectively make the change.

I built an app recently for a large field service scenario where the mobile device can change hands from field worker to field worker. I decided to use the field worker's unique identifier as the value for HostName so that I could replicate only the appropriate data for a given worker to a given SQL Mobile database. If the device changes hands, here is the process of making the change to HostName and reinitializing:

Public Function Synchronize() As Boolean

Dim retVal As Boolean = False

Dim repl As SqlCeReplication = GetReplication()

If repl Is Nothing Then
Return False
End If

Try

If Globals.GetInstance().HostNameChanged Then
repl.ReinitializeSubscription(True)
Globals.GetInstance().HostNameChanged = False
End If

repl.Synchronize()

etc etc etc

If you are doing something similar and getting an error, could you post the details on the error itself and Laxmi and I can look into it? Also, some info on your configuration (assume you are talking about SQL Mobile and SS2005?)

Regards,

Darren

|||

Yeah, that's what I'm doing too. My Sync code blocks looks like the following:

private void replicate(bool reInit)

{

SqlCeReplication repl = new SqlCeReplication();

repl.InternetUrl = Settings1.Default.InternetURL;

repl.InternetLogin = Settings1.Default.InternetLogin;

repl.InternetPassword = Settings1.Default.InternetPassword;

repl.Publisher = Settings1.Default.Publisher;

repl.PublisherDatabase = Settings1.Default.PublisherDatabase;

repl.Publication = Settings1.Default.Publication;

repl.PublisherSecurityMode = SecurityType.NTAuthentication;

repl.SubscriberConnectionString = @."Data Source=" + localFolder + "MyDB.sdf;Password=p@.ssw0rd;";

repl.Subscriber = Settings1.Default.Subscriber;

repl.HostName = txtAgentId.Text;

repl.CompressionLevel = Settings1.Default.CompressionLevel;

repl.ExchangeType = ExchangeType.BiDirectional;

try

{

if (!File.Exists(localFolder + "MyDB.sdf"))

{

repl.AddSubscription(AddOption.CreateDatabase);

}

if (reInit == true)

{

repl.ReinitializeSubscription(true);

}

// Synchronize with SQL Server 2005

repl.Synchronize();

}

finally

{

repl.Dispose();

}

}

If the user changes the HostName, then I pass "true" to the method above to call ReinitializeSubscription(true).

Thanks,

Rob

|||

there is a possible code path in your code where the database does not exist and you (are going to create it using AddOption.CreateDatabase on the first replication), but reinit can also be true - this won't work - you cannot mix CreateDatabase and Reinit in the same call to Synchronize.

Could that be what's happening? What error messages are you getting?

Darren

|||

I don't have the exact error message in front of me now, but is says something like:

Error: Changing the HostName requires a call to ReinitializeSubscription() or change your HostName back to the original value. HostName = 2, Original HostName = 1.

Something like that. Keep in mind that it will even throw this same error when I call ReinitializeSubscription().

To answer your other question, I haven't experienced the possible code path error you described where the database doesn't exist. It should also be known that this is a desktop XP project and not on Windows Mobile. Just in case we have subtle differences or issues between the desktop and handheld dlls. I'm trying to do some load testing and the desktop is the only way to create enough clients.

Rob

Hosted SQL and Replication

I have a hosted SQL database.
The hosting company does not offer to setup merge replication for me.
1) Is there a hosting company that offers replication too ?
2) If I can't do replication, is there anyway to merge two databases using a
DTS package or something ?
Thanks.
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com
Hi Michael Tissington,
From your descriptions, I understood that you would like to know whether
there is an workaround when you do not have the authority to make
replication. Have I understood you? If there is anything I misunderstood,
please feel free to let me know
Unfortunately, I dont think DTS could be used to replace Replication. From
BooksOnline or MSDN Online, we could fine the descriptions like these
--DTS--
Data Transformation Services (DTS) can be used to import and export data
between heterogeneous OLE DB and ODBC data sources. A DTS package is
defined that specifies the source and target OLE DB data sources; the
package can then be executed on an as-required basis or at scheduled times
or intervals.
--Replication--
Using replication, you can distribute data to different locations, to
remote or mobile users over a local area network, using a dial-up
connection, and over the Internet. Replication also allows you to enhance
application performance, physically separate data based on how it is used
(for example, to separate online transaction processing (OLTP) and decision
support systems), or distribute database processing across multiple servers.
If there is only a tiny modification in a large database, you will have to
make a whole new Data Transformation, which will must be cost huge of time
and sometimes money. while Replication will use little transactions to make
it
In the meanwhile, we could use DTS to transfer data between different data
source while replication has rather limitations.
Hope it helps and thank you for your patience and cooperation. If you have
any questions or concerns, don't hesitate to let me know. We are here to be
of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Michael,
there are many hosting companies out there who offer hosting with SQL Server
installed. Off the top of my head I don't know of any who offer replication
publishing services but the first one I found in a Google search offers
replication subscriber services:
http://www.1stchoiceinternational.com/sqlhosting.htm.
Using DTS to merge databases is not at all simple, but it is possible - you
can use binary checksums for each table to determine differences. BTW,
there's nothing inbuilt to allow you to do this, so all the code is to be
written in custom executesql tasks.
hth,
Paul Ibison
|||Paul,
Thanks for the link, just what I'm looking for.
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e7Ck3xeVEHA.2700@.TK2MSFTNGP10.phx.gbl...
> Michael,
> there are many hosting companies out there who offer hosting with SQL
Server
> installed. Off the top of my head I don't know of any who offer
replication
> publishing services but the first one I found in a Google search offers
> replication subscriber services:
> http://www.1stchoiceinternational.com/sqlhosting.htm.
> Using DTS to merge databases is not at all simple, but it is possible -
you
> can use binary checksums for each table to determine differences. BTW,
> there's nothing inbuilt to allow you to do this, so all the code is to be
> written in custom executesql tasks.
> hth,
> Paul Ibison
>