Alternate snapshot location for merge replication subscriber

Hi,
I'm trying to set up a subscriber for merge replication over https. The
initial snapshot file is about 50 Gigs and I'm wondering if its
possible to download & store this snapshot somewhere other than on my
database drive (I have size contraints). Any ideas or direction?
Thanks,
JC
Hi
Yes this feature is available for merge replication, there is some more
information here about alternate snapshot locations.
http://msdn.microsoft.com/library/de...limpl_3vcj.asp
Nabila Lacey
<jbzcooper@.gmail.com> wrote in message
news:1139947715.833574.258470@.g47g2000cwa.googlegr oups.com...
> Hi,
> I'm trying to set up a subscriber for merge replication over https. The
> initial snapshot file is about 50 Gigs and I'm wondering if its
> possible to download & store this snapshot somewhere other than on my
> database drive (I have size contraints). Any ideas or direction?
> Thanks,
> JC
>
|||As well as Nabila's advice, you might want to consider using winzip 9.0 or
winrar to speed up the data transfer. Compression is available in SQL Server
but is limited to the 2GB limitation of a CAB file.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you both. I suppose I should have been more clear. I have no
control over the publication itself and thus cannot specify an
alternate location on the publisher. I was told that the drive I was
replicating to needed at least 100GB free to house both the snapshot
and the database it would be loaded into. Is it possible for me to zip
and download the snapshot to my subscriber (assuming they will let me)
on an alternate drive and point my subscription to that?
I do appreciate the help,
Jeremiah
|||Jeremiah,
the alternative snapshot location I was referring to is basically a
subscriber setting. the method you propose is exactly what I have done in
the past for large publications.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Alternate snapshot for push subscribtion

Can I define an alternate snapshot file location for a push subscriber?
How?
Have a look at the agent profile parameters - http://msdn.microsoft.com/library/de...trib_2f09.asp.
EG for the distribution agent there is a property -AltSnapshotFolder
Regards,
Paul Ibison
|||right click on your publication, select publication properties, select
snapshot location. Then select on the Generate snapshots in the following
location, and enter a new snapshot location. Then restart your snapshot
agent, and your distribution agent.
"Humam" <anonymous@.discussions.microsoft.com> wrote in message
news:490BF15E-9BC5-4042-BEC4-A98383BBFF18@.microsoft.com...
> Can I define an alternate snapshot file location for a push subscriber?
> How?
>
|||That is right, but I need to transfer the snapshot files
to where the subscriber resides (on CD's).
How can I configure the push subscriber to use the
snapshot files located at the subscriber?
Thanks for your help.

>--Original Message--
>right click on your publication, select publication
properties, select
>snapshot location. Then select on the Generate snapshots
in the following
>location, and enter a new snapshot location. Then restart
your snapshot
>agent, and your distribution agent.
>"Humam" <anonymous@.discussions.microsoft.com> wrote in
message
>news:490BF15E-9BC5-4042-BEC4-A98383BBFF18@.microsoft.com...
push subscriber?
>
>.
>
|||Run your snapshot agent. Copy the snapshot path from repldata on down to
your subscriber.
Then when you pull your subscriber go through the prompts using the wizard
until you get to the Snapshot Delivery dialog. Select the Use snapshot files
from the following folder and point to the repldata folder you have copied
from your publisher. Then click on next and continue to build your pull
subscription.
"Humam" <anonymous@.discussions.microsoft.com> wrote in message
news:1621e01c41711$30106440$a301280a@.phx.gbl...
> That is right, but I need to transfer the snapshot files
> to where the subscriber resides (on CD's).
> How can I configure the push subscriber to use the
> snapshot files located at the subscriber?
> Thanks for your help.
> properties, select
> in the following
> your snapshot
> message
> push subscriber?

Alternate Snapshot Folder during Setup in 2005

One of the things that annoys me in 2005 is that you cannot change the snapshot location during the setup of the replication. Does anyone know if I missed something or if they are going to change that in a future patch?

Hi Rich,

Although you can't set alternate snapshot folder locations in publication setup wizard, you can change it immediately in "Publication Properties" dialog. Please see http://msdn2.microsoft.com/en-us/library/ms151745.aspx.

If you would like to specify default snapshot location, you can set it in distributor property dialog (http://msdn2.microsoft.com/en-us/library/ms151258.aspx)

Peng

|||This is true, but if you are pulling a subsription and you use the ability to setup multiple subscribers all at once then I have to go to the properties of each subscription and change the alternate folder location which is a pain.|||

What is the purpose of using alternate snapshot location, are you no longer in need of using the default snapshot location? You're not required to need both, you can set the default to be the location of the alternate, then you don't have to worry about specifying altnernate snapshot location. See books online topic "Alternate Snapshot Folder Locations". ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/437553b0-19df-4522-8f27-06b5bc747c69.htm.

|||

In this case I do require both. There is a publisher in Toronto that I have limited control over because it is owned y a different company. My distributor is also in Toronto (different facility) where the snapshots are storedin RAW format for my subscribers in Toronto. I then have a process where I compress the snapshots and FTP them to a server I have in Vancouver and ucompress them for my subscribers there. So in this case I need both the default and alternate location. I have about 16 publications like that and about 8 go to each subsciber that I have and I have about 13 subscribers currently.

I'll check out BOL on that matter as you suggest.

Thanks

Rich

Alter Table - Add a field to a table in a specific location

I'm using the command to add a field to my table which has 20 fields.

ALTER TABLE table_name ADD column_name datatype

This adds the new field to the bottom of the table as the 21st first. How can I make it so it shows up as the 5th field in the table?

Wow you would have to do some work to get that to happen. It can be done but why?

The quick and dirty way is to copy all records into a temp table. DROP and reCREATE the table with the fields in the order of your preference. Then import the records from the temp table.

Adamus

|||The reason why I want to add it to a specific spot is to keep my table organized. The field that I am adding is a status field and I want it to be next to the other status fields and not just put it randomly at the bottom.

When you modify tables in Enterprise Manager it's real easy to change the location of fields...simply by drag and drop. This leads me to believe that there's a sql command that will do the same thing I'm just not sure what the command is.|||

Behind the 'scenes', Enterprise Manager does just like Adamus indicated. It creates a temp table, transfers the data, drops the old table, and renames the temp table.

There is no 'magic' to Enterprise Manager -it just writes the code for you -and sometimes not the best code either...

|||

Bank5,

this order is actually important only for the human user, as applications do not care so much about it (at least SHOULD NOT). Why don't you just create a view with correct column order which you can later use instead of table? Garnet Chaney posted an article about this, you can read it here

Also, "ALTER TABLE syntax for changing column order" feature is considered for the next release - if you think it would be useful you can vote here.

Cheers,

michalz

ALTER PK

As I'm in the process of planning replication of two
tables to from one database in one location (country) to
another location (another country) I need to change the
tables using an ALTER TABLE statement so that the UNIQUE
index acting as the primary key, changed to a declared
primary key.
Since I will be applying this change to a live production
environment will there be any issues when applying this
change.
The key column is defined currently as:
data type: uniqueidentifier
allow nulls: no
default value: newid()
Thanks,
AnjelinaAnjelina
I don't see any negative issues if you are going to apply this change. If a
table is large then it will take time to arrange keys with ascending order.
BOL says:
"Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique
index on the specified columns in the "table
"anjelina" <ajturner@.canada.com> wrote in message
news:078701c38e21$cd144af0$a401280a@.phx.gbl...
> As I'm in the process of planning replication of two
> tables to from one database in one location (country) to
> another location (another country) I need to change the
> tables using an ALTER TABLE statement so that the UNIQUE
> index acting as the primary key, changed to a declared
> primary key.
> Since I will be applying this change to a live production
> environment will there be any issues when applying this
> change.
> The key column is defined currently as:
> data type: uniqueidentifier
> allow nulls: no
> default value: newid()
> Thanks,
> Anjelina
>|||If the primary key constraint will be a clustered index, all of the
non-clustered indexes will automatically be re-built. This can take some
time on a large table, and users will not be able to access the table
during this time.
Kick everyone off of the system prior to beginning.
drop the unique constraint.
then create the primary key constraint.
When complete, be sure to back up the database with a full or differential
backup.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"anjelina" <ajturner@.canada.com> wrote in message
news:078701c38e21$cd144af0$a401280a@.phx.gbl...
> As I'm in the process of planning replication of two
> tables to from one database in one location (country) to
> another location (another country) I need to change the
> tables using an ALTER TABLE statement so that the UNIQUE
> index acting as the primary key, changed to a declared
> primary key.
> Since I will be applying this change to a live production
> environment will there be any issues when applying this
> change.
> The key column is defined currently as:
> data type: uniqueidentifier
> allow nulls: no
> default value: newid()
> Thanks,
> Anjelina
>

ALTER DATABASE MODIFY NAME but

Leave the file location/directory the heck alone!

How do I do this?

Just issuing:

ALTER DATABASE Old_Name MODIFY NAME = New_Name

moves the mdf and ldf files to a new, unwanted location (apparently the SQL Server default as it's under program files) with the new name.

Is this possible or do I have to issue the additional ALTER DATABASE MODIFY FILE statements for this?modify name does not move the file(s).

create database [test]
on(name=test,filename='c:\test.mdf')
log on(name=test_log,filename='c:\test.ldf')
go
alter database [test] modify name=newtest
go
select *
from [newtest]..sysfiles
go
drop database [newtest]
go

==result==
1 test c:\test.mdf
2 test_log c:\test.ldf|||

Hi,

ALTER DATABASE ... MODIFY FILE command modifies the names of data files of the related database.

Please check the following article for also a sample on changing logical file names of SQL databases http://www.kodyaz.com/articles/change-sql-server-database-file-names.aspx

Eralper

|||Guess you didn't read my full post. I'm aware of this command.|||did you try my demo script. do you get the expected result?|||

Yeah, turns out it's not that part of the script but rather the Copy Database wizard that is to blame.

(I've got a post in tools on it bu gtno replies yet.)