Push or Pull

I've always had a hard time getting my head around Push and Pull
Subscriptions.
Ive read that it really doesnt have a huge impact on a box, and that what
really matters is where the Distributor is. That Push or Pull is more for
Administrative type of stuff. Just not sure about this.
I just started at a company that uses quite a bit of replication. So far Ive
come up with two Publishers and propably or 6 or 8 Subscribers. The
Distributors are both Local and the Subscriptions are all Pull. It is all
Transactional Replication, some of it continuos, some every 15 minutes.
So my questions are:
Would I be better off to use a dedicated Remote Distributor? One of my main
objectives here is to speed up these boxes.
If I did use a Remote Distributor, would it matter if I went to Push
Subscriptions for centralized Administration? Or would that defeat my goal
of speeding up the Publisher?
I don't like remote distributors and hesitate before using them. If your
remote distributor goes down you can end up with ballooning tlogs on your
publication databases and you can have problems getting your log reader
agent working again.
If you have high throughput and are experiencing locking you might want to
look at them.
Pull subscriptions do lessen the load on the publisher. They are the best
solution when you have a lot of subscribers, are replicating across the
internet or when your subscribers are not well connected or not always
connected, you can live with no central point of administration (other than
replication monitor group).
the rest of the answers are inline.
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
"ChrisR" <noemail@.bla.com> wrote in message
news:%23ydW$eHNFHA.3668@.TK2MSFTNGP14.phx.gbl...
> I've always had a hard time getting my head around Push and Pull
> Subscriptions.
> Ive read that it really doesnt have a huge impact on a box, and that what
> really matters is where the Distributor is. That Push or Pull is more for
> Administrative type of stuff. Just not sure about this.
> I just started at a company that uses quite a bit of replication. So far
Ive
> come up with two Publishers and propably or 6 or 8 Subscribers. The
> Distributors are both Local and the Subscriptions are all Pull. It is all
> Transactional Replication, some of it continuos, some every 15 minutes.
> So my questions are:
> Would I be better off to use a dedicated Remote Distributor? One of my
main
> objectives here is to speed up these boxes.
I would only use this if you are experiencing considerable locking in your
distribution database.

> If I did use a Remote Distributor, would it matter if I went to Push
> Subscriptions for centralized Administration?
No, not for a few always connected subscribers.
Or would that defeat my goal
> of speeding up the Publisher?
Stop your log reader and distribution agent. Do you get a significant
increase in performance. If so, migrate; if not (and suspect not) stay with
the local distributor.
>

Purchasing SQL

I've looked through a number of sites trying to find the difference between
the different "types" of SQL Server. We have a company that has
approximately 50 internal website users and approximately 2-3000 external
website users (no idea how many concurrent).
What type of SQL Server software will we need? My last company had SQL
2000 Enterprise, but they had a legitimate budget. This company is
searching on pricegrabber for Standard Edition 1 processor licenses and
trying to find the cheapest deal (you wouldn't believe this if you saw it).
Anyway, I'd like to provide some insight...as I know they're about to get
ripped off, but I figured I'd do some homework first. Any info would be
fantastic. Thanks.
James
"James Baker" <cppjames@.hotmail.com> wrote in message
news:u7lhHaSXEHA.2408@.tk2msftngp13.phx.gbl...
> I've looked through a number of sites trying to find the difference
between
> the different "types" of SQL Server. We have a company that has
> approximately 50 internal website users and approximately 2-3000 external
> website users (no idea how many concurrent).
> What type of SQL Server software will we need? My last company had SQL
> 2000 Enterprise, but they had a legitimate budget. This company is
> searching on pricegrabber for Standard Edition 1 processor licenses and
> trying to find the cheapest deal (you wouldn't believe this if you saw
it).
> Anyway, I'd like to provide some insight...as I know they're about to get
> ripped off, but I figured I'd do some homework first. Any info would be
> fantastic. Thanks.
One of the biggest drawbacks of standard vs. enterprise is the 2 gig memory
limit on standard. Of course, there are other features/benefits of
enterprise, but that is a deciding factor for a lot of people.
-John Oakes

> James
>
|||"James Baker" <cppjames@.hotmail.com> wrote in message
news:u7lhHaSXEHA.2408@.tk2msftngp13.phx.gbl...
> I've looked through a number of sites trying to find the difference
between
> the different "types" of SQL Server. We have a company that has
> approximately 50 internal website users and approximately 2-3000 external
> website users (no idea how many concurrent).
> What type of SQL Server software will we need? My last company had SQL
> 2000 Enterprise, but they had a legitimate budget. This company is
> searching on pricegrabber for Standard Edition 1 processor licenses and
> trying to find the cheapest deal (you wouldn't believe this if you saw
it).
> Anyway, I'd like to provide some insight...as I know they're about to get
> ripped off, but I figured I'd do some homework first. Any info would be
> fantastic. Thanks.
One of the biggest drawbacks of standard vs. enterprise is the 2 gig memory
limit on standard. Of course, there are other features/benefits of
enterprise, but that is a deciding factor for a lot of people.
-John Oakes

> James
>
|||How many SQL Server boxes are you going to have? You could have thousands
of websites pointing to one SQL Server if you wanted. Or you could have one
SQL Server box per web site.
You mention that "they're about to get ripped off." What does that mean?
Are "they" spending too much? Do "they" think that they need to purchase
one copy of SQL Server for every web server?
You are correct in searching for the Per Processor license, as this is what
you will need in order to use it to drive a web site (or sites).
The standard Edition of SQL Server is usually sufficient for most people.
Enterprise does have some additional high availability features (clustering)
and some other features that can improve performance (indexed views, greater
memory support), but those features come at a cost.
Only you can determine what features you require. These features will
determine what version of SQL Server to purchase.
Keith
"James Baker" <cppjames@.hotmail.com> wrote in message
news:u7lhHaSXEHA.2408@.tk2msftngp13.phx.gbl...
> I've looked through a number of sites trying to find the difference
between
> the different "types" of SQL Server. We have a company that has
> approximately 50 internal website users and approximately 2-3000 external
> website users (no idea how many concurrent).
> What type of SQL Server software will we need? My last company had SQL
> 2000 Enterprise, but they had a legitimate budget. This company is
> searching on pricegrabber for Standard Edition 1 processor licenses and
> trying to find the cheapest deal (you wouldn't believe this if you saw
it).
> Anyway, I'd like to provide some insight...as I know they're about to get
> ripped off, but I figured I'd do some homework first. Any info would be
> fantastic. Thanks.
> James
>
|||Thanks for the reply. We'll only have one SQL Server box. When I said
they're about to get ripped off...I meant that they're searching for the
software on sketchy sites with rather large discounts. Microsoft is selling
the processor license for $5000 if I'm reading this anywhere near right, and
they're looking at copies that are barely over $1000. It just doesn't seem
"kosher" to me. The 2 Gig limit...that refers to the amount of memory the
box itself can have? I don't know that this would be a problem or not.
I'll have to look into that.
Thanks,
James
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OGZW8nSXEHA.1000@.TK2MSFTNGP12.phx.gbl...
> How many SQL Server boxes are you going to have? You could have thousands
> of websites pointing to one SQL Server if you wanted. Or you could have
one
> SQL Server box per web site.
> You mention that "they're about to get ripped off." What does that mean?
> Are "they" spending too much? Do "they" think that they need to purchase
> one copy of SQL Server for every web server?
> You are correct in searching for the Per Processor license, as this is
what
> you will need in order to use it to drive a web site (or sites).
> The standard Edition of SQL Server is usually sufficient for most people.
> Enterprise does have some additional high availability features
(clustering)
> and some other features that can improve performance (indexed views,
greater[vbcol=seagreen]
> memory support), but those features come at a cost.
> Only you can determine what features you require. These features will
> determine what version of SQL Server to purchase.
> --
> Keith
>
> "James Baker" <cppjames@.hotmail.com> wrote in message
> news:u7lhHaSXEHA.2408@.tk2msftngp13.phx.gbl...
> between
external[vbcol=seagreen]
> it).
get
>
|||How many SQL Server boxes are you going to have? You could have thousands
of websites pointing to one SQL Server if you wanted. Or you could have one
SQL Server box per web site.
You mention that "they're about to get ripped off." What does that mean?
Are "they" spending too much? Do "they" think that they need to purchase
one copy of SQL Server for every web server?
You are correct in searching for the Per Processor license, as this is what
you will need in order to use it to drive a web site (or sites).
The standard Edition of SQL Server is usually sufficient for most people.
Enterprise does have some additional high availability features (clustering)
and some other features that can improve performance (indexed views, greater
memory support), but those features come at a cost.
Only you can determine what features you require. These features will
determine what version of SQL Server to purchase.
Keith
"James Baker" <cppjames@.hotmail.com> wrote in message
news:u7lhHaSXEHA.2408@.tk2msftngp13.phx.gbl...
> I've looked through a number of sites trying to find the difference
between
> the different "types" of SQL Server. We have a company that has
> approximately 50 internal website users and approximately 2-3000 external
> website users (no idea how many concurrent).
> What type of SQL Server software will we need? My last company had SQL
> 2000 Enterprise, but they had a legitimate budget. This company is
> searching on pricegrabber for Standard Edition 1 processor licenses and
> trying to find the cheapest deal (you wouldn't believe this if you saw
it).
> Anyway, I'd like to provide some insight...as I know they're about to get
> ripped off, but I figured I'd do some homework first. Any info would be
> fantastic. Thanks.
> James
>
|||Thanks for the reply. We'll only have one SQL Server box. When I said
they're about to get ripped off...I meant that they're searching for the
software on sketchy sites with rather large discounts. Microsoft is selling
the processor license for $5000 if I'm reading this anywhere near right, and
they're looking at copies that are barely over $1000. It just doesn't seem
"kosher" to me. The 2 Gig limit...that refers to the amount of memory the
box itself can have? I don't know that this would be a problem or not.
I'll have to look into that.
Thanks,
James
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OGZW8nSXEHA.1000@.TK2MSFTNGP12.phx.gbl...
> How many SQL Server boxes are you going to have? You could have thousands
> of websites pointing to one SQL Server if you wanted. Or you could have
one
> SQL Server box per web site.
> You mention that "they're about to get ripped off." What does that mean?
> Are "they" spending too much? Do "they" think that they need to purchase
> one copy of SQL Server for every web server?
> You are correct in searching for the Per Processor license, as this is
what
> you will need in order to use it to drive a web site (or sites).
> The standard Edition of SQL Server is usually sufficient for most people.
> Enterprise does have some additional high availability features
(clustering)
> and some other features that can improve performance (indexed views,
greater[vbcol=seagreen]
> memory support), but those features come at a cost.
> Only you can determine what features you require. These features will
> determine what version of SQL Server to purchase.
> --
> Keith
>
> "James Baker" <cppjames@.hotmail.com> wrote in message
> news:u7lhHaSXEHA.2408@.tk2msftngp13.phx.gbl...
> between
external[vbcol=seagreen]
> it).
get
>

pubs in Desktop Version of SQL2ksp3

I'm using VS and I've been trying to do the examples on
the tutorial. As topic title suggested, I'm using a
desktop version of the SQL2k service pack 3. However, I
can't seem to make a connection to the pubs database. The
only database I get are master, model, msdb and tempdb.
What exactly should I do to get this working?
Thanks in advance. ^_^
hi,
<anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:95e001c4338b$ba009e90$a501280a@.phx.gbl...
> I'm using VS and I've been trying to do the examples on
> the tutorial. As topic title suggested, I'm using a
> desktop version of the SQL2k service pack 3. However, I
> can't seem to make a connection to the pubs database. The
> only database I get are master, model, msdb and tempdb.
> What exactly should I do to get this working?
> Thanks in advance. ^_^
Pubs and Northwind sample databases are not present in the MSDE packages, in
order to limit thery deployment size...
you can actually get the database package at
http://www.microsoft.com/downloads/d...DisplayLang=en
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Publishing Wizard - Bad Magic?

I've been working with the Database Publishing Wizard and have found what I initially thought was a bug but have decided is a rather nasty "feature". Or am I missing something?

I have membership implemented in a live database. I took a backup and loaded it into a local database. I created a new table locally that is foreign-keyed off aspnet_roles, and added some data.

Using the Publishing Wizard for a partial deployment, I followed the steps which seem perfectly clear to script out ONLY the new table and its data. The screens provide a nice GUI for selecting specific database objects, and I carefully chose the one new table.

From good habits I looked at the script before deploying it. Itdrops and recreates aspnet_roles and aspnet_applications as well (and inserts the data from the development tables).

Presumably this is because of the foreign key relationships. But there's no need to do so. What if I or another developer had added in some roles to the live table in the meantime? This script would blow them away.

Am I missing an option with the Wizard that would script out only what is specified? I've tidied up the script (i.e. removed the undesirable sections) - but this seems to be a naughty wizard. Any thoughts?

What publishing wizard are you using for your database? It is a bit expensive but I believe Red Gate has the best db tools available. It contains separate data comparison and sql structure (table changes) tools.

|||

We use Red Gate when we have a choice. But one of our clients uses the Database Publishing Wizard, which is a free download from Microsoft.

I was curious so was checking it out. It seems to me that it should pop up a list of tables affected by the user choice, particularly when the user has selected only one table. Anyway, I'll continue pursuing this elsewhere, thanks for the response.

|||

If that's the case is it possible to just modify the sql by removing the data insert statements?

|||

At the risk of stating the obvious, did you try changing the "Drop existing objects in script" option to false? This will prevent the script from adding the if exists-drop statements when publsihing and when writing scripts.

This option is on the "Select Publishing Options" page.

|||

Yes, but take this scenario - I deploy a single new table fk-ed to aspnet_roles and uncheck "drop existing objects". Everything is hunky-dory first time round. But then I want to add a single new column to this recent table? It makes sense to check "drop existing objects" and then select the changed table - behind the scenes, aspnet_roles is dropped and recreated alongside the new table.

And regardless of whether the "drop existing objects" is unchecked - the Wizard will still try to insert the data from the development database into aspnet_roles. This will fail, because it turns identity insert on - so will bomb out on the primary key.

I seem to remember looking at the Wizard when it first was released and it didn't have partial deployment options - only full deployment. So partial deployment has been added later - which is a nice feature. But I think if the Wizard "decides" that it knows best and will also work with foreign-keyed tables for a chosen table in the GUI - it should warn the user of exactly which tables are affected.

Yes - the user can edit the script - but this Wizard isn't aimed at DBAs who will tend to want to roll their own scripts anyway - I think it's aimed at developers who are proficient in coding but don't want to get bogged down in the database side.

Publish Subscription replication - Side effects?

Hi Guys,
I've been given the task of creating a disaster recovery "replica" of a
production SQL server and keeping it current.
(SQL 2000)
At first we thought of using scripts to do a backup/restore method.
But a couple of the databases are upwards of a hundred gigabytes...
A bit hard on drive space.
I thought a better method would be to replicate the databases using the
Publish and "push subscriptions".
I'm not very knowledgeable about SQL management, However with the help of
Google and a little time,
I mamnaged to make it work experimentally with the Northwind database.
But that's tiny compared to the live DBs
My question is:
Am I likely to see any 'unforseen' side effects of replicating by this
method?
Thanks,
Jim
To keep maximum performance on your main SQL Server I would consider
"pulling" the transactionally replicated data.
This article from Microsoft has alot of great tips on how to maximize
replication performance.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx
If you have money for a nice SAN unit you can check out Split Mirroring.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/spltmirr.mspx

/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:epEJFv0QIHA.2376@.TK2MSFTNGP02.phx.gbl...
> Hi Guys,
> I've been given the task of creating a disaster recovery "replica" of a
> production SQL server and keeping it current.
> (SQL 2000)
> At first we thought of using scripts to do a backup/restore method.
> But a couple of the databases are upwards of a hundred gigabytes...
> A bit hard on drive space.
> I thought a better method would be to replicate the databases using the
> Publish and "push subscriptions".
> I'm not very knowledgeable about SQL management, However with the help of
> Google and a little time,
> I mamnaged to make it work experimentally with the Northwind database.
> But that's tiny compared to the live DBs
> My question is:
> Am I likely to see any 'unforseen' side effects of replicating by this
> method?
> Thanks,
> Jim
>
|||Thanks Warren!
I'll give it a go.
Unfortunately the client is 'cheap' and wants to spend as little as
possible.
Getting to be par for the course these days...
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:elyRBZ5QIHA.4180@.TK2MSFTNGP06.phx.gbl...
> To keep maximum performance on your main SQL Server I would consider
> "pulling" the transactionally replicated data.
> This article from Microsoft has alot of great tips on how to maximize
> replication performance.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx
> If you have money for a nice SAN unit you can check out Split Mirroring.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/spltmirr.mspx
>
> --
> /*
> Warren Brunk - MCITP,MCTS,MCDBA
> www.techintsolutions.com
> */
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:epEJFv0QIHA.2376@.TK2MSFTNGP02.phx.gbl...
>

Publication not enabled to use an independent agent

I've created a snapshot replication between 2 databases on 2 different
servers to run once a day. The snapshot has worked but when the
distribution job runs it fails within a few seconds. I've looked at the
job history and the following error message is recorded:
Publication 'EmailDB' is not enabled to use an independent agent. The
step failed.
I think this may be because I've defined the publication so that
anonymous pull subscriptions are not allowed. If not does anyone know
what the problem could be. If it is how do I alter the publication so
that they are allowed - the option doesn't appear to be changeable with
the publication properties window in EM.
TIA
Laurence Breeze
drop your subscriptions and try this
sp_changepublication 'pubs4','independent_agent','true'
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
"Laurence Breeze" <laurence_breeze@.yahoo.co.uk> wrote in message
news:4289AD77.5070305@.yahoo.co.uk...
> I've created a snapshot replication between 2 databases on 2 different
> servers to run once a day. The snapshot has worked but when the
> distribution job runs it fails within a few seconds. I've looked at the
> job history and the following error message is recorded:
> Publication 'EmailDB' is not enabled to use an independent agent. The
> step failed.
> I think this may be because I've defined the publication so that
> anonymous pull subscriptions are not allowed. If not does anyone know
> what the problem could be. If it is how do I alter the publication so
> that they are allowed - the option doesn't appear to be changeable with
> the publication properties window in EM.
> TIA
> Laurence Breeze
>

Publication Filter Clause changes

Hello,
I've merge replication set between SQL Server 2000 & SQL CE.
Whenever I try to add filter clause to the existing publication, it gives
error mentioning the column name used in the filter clause.
Ex. Let's say I've 5 publications - Pub1, Pub2, Pub3, Pub4, Pub5.
If I add filter clause say 'Employee_Status <> 0' then it throws the error
as 'Invalid Column Name 'Employee_Status'' even if that column is present in
the table.
But, if I drop all publications (pub1 to pub5), then the whole thing works
perfectly fine. But it is not feasible to drop ALL publications (even the
ones not related to this change) to change the filter clause of 1 article for
just 1 publication. Is there any way out other than dropping the publications?
Thanks in advance for help.
Regards,
Shweta
exactly how are you deploying your publication to your SQL CE clients?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Shweta" <Shweta@.discussions.microsoft.com> wrote in message
news:697E3FFF-8A77-4FC1-ABCE-747679F84DF3@.microsoft.com...
> Hello,
> I've merge replication set between SQL Server 2000 & SQL CE.
> Whenever I try to add filter clause to the existing publication, it gives
> error mentioning the column name used in the filter clause.
> Ex. Let's say I've 5 publications - Pub1, Pub2, Pub3, Pub4, Pub5.
> If I add filter clause say 'Employee_Status <> 0' then it throws the error
> as 'Invalid Column Name 'Employee_Status'' even if that column is present
in
> the table.
> But, if I drop all publications (pub1 to pub5), then the whole thing works
> perfectly fine. But it is not feasible to drop ALL publications (even the
> ones not related to this change) to change the filter clause of 1 article
for
> just 1 publication. Is there any way out other than dropping the
publications?
> Thanks in advance for help.
> Regards,
> Shweta