MSDE 2000A Upgrade to SQL Express SP1 with Merge Replication

Hello,

I have the following scenario. An application using SQL Server 2000 SP3 on the server side and clients using MSDE 2000A (SP3a) on the client machine. Since the client is offline quite often merge replication is used to keep the clients in sync.

Now we try to upgrade to SQL Server 2005 SP1. The publisher and distributor upgrade (on the same box) worked fine and all clients could still synchronize. Fine :-)

Now we try to upgrade the clients to SQL Express SP1. Now the problems start :-(

1) After the upgrade the entry within the Synchronization Manager is gone (we can overcome this by using sp_MSregistersubscription or by manually disable and enable Synchronization manager on the subscription properties)

2) Initial Synchronization (takes a long time but) works fine, if I apply changes to the subscriber or force a reinitialize all following synchronization will fail. With the following messages:

Error messages:
The merge process could not clean up the subscription to 'tstvmw23':'TestBase:'TestBase'. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200965)
Get help: http://help/MSSQL_REPL-2147200965
New request is not allowed to start because it should come with valid transaction descriptor. (Source: MSSQLServer, Error number: 3989)
Get help: http://help/3989

Remark the test system I use is a nearly empty db (only with full schema and a few lookup tables) with 15MB. The same error occures if I create a new database an subscribe to the same publication.

Please help otherwise I'm forced to step back to MSDE.

Thanks in advance,

Thomas Hotz

Hi,

just to make sure you do not want SQL Server Express to the a merge replicant...

http://msdn2.microsoft.com/en-us/library/ms165616.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||What this table on the BOL means is that SQL Server Express cannot act as a Distributor/Publisher on a merge replication and the main cause is that it does not have an Agent.
http://technet.microsoft.com/en-us/library/ms143470.aspx
See the topic SQL Server does not include an agent on that reference.

SQL Server Express can act as a subscriber on Merge Replication though. If you have a type of subscription Pull, as the Agent runs on the subscribers you have to use RMO on your code to instantiate an agent or use Windows Synchronization Manager (Start Menu-> Accessories -> Synchronize)

We have SQLE running as subscribers on a pull merge replication topology...sql

MSDE 2000A Upgrade to SQL Express SP1 with Merge Replication

Hello,

I have the following scenario. An application using SQL Server 2000 SP3 on the server side and clients using MSDE 2000A (SP3a) on the client machine. Since the client is offline quite often merge replication is used to keep the clients in sync.

Now we try to upgrade to SQL Server 2005 SP1. The publisher and distributor upgrade (on the same box) worked fine and all clients could still synchronize. Fine :-)

Now we try to upgrade the clients to SQL Express SP1. Now the problems start :-(

1) After the upgrade the entry within the Synchronization Manager is gone (we can overcome this by using sp_MSregistersubscription or by manually disable and enable Synchronization manager on the subscription properties)

2) Initial Synchronization (takes a long time but) works fine, if I apply changes to the subscriber or force a reinitialize all following synchronization will fail. With the following messages:

Error messages:
The merge process could not clean up the subscription to 'tstvmw23':'TestBase:'TestBase'. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200965)
Get help: http://help/MSSQL_REPL-2147200965
New request is not allowed to start because it should come with valid transaction descriptor. (Source: MSSQLServer, Error number: 3989)
Get help: http://help/3989

Remark the test system I use is a nearly empty db (only with full schema and a few lookup tables) with 15MB. The same error occures if I create a new database an subscribe to the same publication.

Please help otherwise I'm forced to step back to MSDE.

Thanks in advance,

Thomas Hotz

Hi,

just to make sure you do not want SQL Server Express to the a merge replicant...

http://msdn2.microsoft.com/en-us/library/ms165616.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||What this table on the BOL means is that SQL Server Express cannot act as a Distributor/Publisher on a merge replication and the main cause is that it does not have an Agent.
http://technet.microsoft.com/en-us/library/ms143470.aspx
See the topic SQL Server does not include an agent on that reference.

SQL Server Express can act as a subscriber on Merge Replication though. If you have a type of subscription Pull, as the Agent runs on the subscribers you have to use RMO on your code to instantiate an agent or use Windows Synchronization Manager (Start Menu-> Accessories -> Synchronize)

We have SQLE running as subscribers on a pull merge replication topology...

MSDE & EM Tools

Hi,
I intend using MSDE 2000 in conjunction with a fully licensed version of SQL
Server 2000. MSDE will be installed on several clients.
My understanding is as follows :
I can use Enterprise Manager on any or all of those client instances of MSDE
because I have a fully licensed copy of SQL Server 2000 with which they will
be replicating.
MSDN:
You can only use SQL Server tools and services in conjunction with MSDE if
you acquired MSDE through SQL Server 2000 (Developer Edition, Standard
Edition, or Enterprise Edition), and if you are using MSDE in conjunction
with a properly licensed copy of SQL Server 2000.
Am I in the clear?
Can I use any of the GUI tools?
Thanks in advance,
DOM.hi Dom,
Dom wrote:
> Hi,
> I intend using MSDE 2000 in conjunction with a fully licensed version
> of SQL Server 2000. MSDE will be installed on several clients.
> My understanding is as follows :
> I can use Enterprise Manager on any or all of those client instances
> of MSDE because I have a fully licensed copy of SQL Server 2000 with
> which they will be replicating.
> MSDN:
> You can only use SQL Server tools and services in conjunction with
> MSDE if you acquired MSDE through SQL Server 2000 (Developer Edition,
> Standard Edition, or Enterprise Edition), and if you are using MSDE
> in conjunction with a properly licensed copy of SQL Server 2000.
> Am I in the clear?
> Can I use any of the GUI tools?
> Thanks in advance,
> DOM
please have a look at Steve Dybing answer in http://tinyurl.com/d2qpa .. it
does not seem this is a legal solution...
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||Thanks Andrea,
I followed the link you mentioned and on that page was directed to the
following site:
http://www.aspfaq.com/show.asp?id=2442
which states :
If you have an existing machine with SQL Server client tools installed, you
can manage the MSDE instance through these... as long as you have a valid an
d
legal Client Access License (CAL).
You can take the SQL Server install CD, run it on the client(s), and choose
"Install client tools only" during setup. This will install Query Analyzer,
Enterprise Manager, and other useful tools—but will not install the databa
se
engine.
This was my understanding.
With a fully licensed MSSQL 2000 you can legally use the client tools on
MSDE 2000 clients'
DOM|||hi Dom,
Dom wrote:
> Thanks Andrea,
> I followed the link you mentioned and on that page was directed to the
> following site:
> http://www.aspfaq.com/show.asp?id=2442
> which states :
> If you have an existing machine with SQL Server client tools
> installed, you can manage the MSDE instance through these... as long
> as you have a valid and legal Client Access License (CAL).
> You can take the SQL Server install CD, run it on the client(s), and
> choose "Install client tools only" during setup. This will install
> Query Analyzer, Enterprise Manager, and other useful tools-but will
> not install the database engine.
> This was my understanding.
> With a fully licensed MSSQL 2000 you can legally use the client tools
> on MSDE 2000 clients'
I had a private conversation with Steve some time ago about this matter...
he is not a "legal" guy too... but he has been told by legal representatives
that EM (QA and so on) is not legitimated to be used against production MSDE
instances... this is the reason he posted that way...
so, in my understanding, you never (or quite never, but I do really have no
idea when it could be legal) are legitimated to use SQL Server Client Tools
to manage MSDE...
my $0.02
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MSDE & EM Tools

Hi,
I intend using MSDE 2000 in conjunction with a fully licensed version of SQL
Server 2000. MSDE will be installed on several clients.
My understanding is as follows :
I can use Enterprise Manager on any or all of those client instances of MSDE
because I have a fully licensed copy of SQL Server 2000 with which they will
be replicating.
MSDN:
You can only use SQL Server tools and services in conjunction with MSDE if
you acquired MSDE through SQL Server 2000 (Developer Edition, Standard
Edition, or Enterprise Edition), and if you are using MSDE in conjunction
with a properly licensed copy of SQL Server 2000.
Am I in the clear?
Can I use any of the GUI tools?
Thanks in advance,
DOM.
hi Dom,
Dom wrote:
> Hi,
> I intend using MSDE 2000 in conjunction with a fully licensed version
> of SQL Server 2000. MSDE will be installed on several clients.
> My understanding is as follows :
> I can use Enterprise Manager on any or all of those client instances
> of MSDE because I have a fully licensed copy of SQL Server 2000 with
> which they will be replicating.
> MSDN:
> You can only use SQL Server tools and services in conjunction with
> MSDE if you acquired MSDE through SQL Server 2000 (Developer Edition,
> Standard Edition, or Enterprise Edition), and if you are using MSDE
> in conjunction with a properly licensed copy of SQL Server 2000.
> Am I in the clear?
> Can I use any of the GUI tools?
> Thanks in advance,
> DOM
please have a look at Steve Dybing answer in http://tinyurl.com/d2qpa .. it
does not seem this is a legal solution...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea,
I followed the link you mentioned and on that page was directed to the
following site:
http://www.aspfaq.com/show.asp?id=2442
which states :
If you have an existing machine with SQL Server client tools installed, you
can manage the MSDE instance through these... as long as you have a valid and
legal Client Access License (CAL).
You can take the SQL Server install CD, run it on the client(s), and choose
"Install client tools only" during setup. This will install Query Analyzer,
Enterprise Manager, and other useful tools—but will not install the database
engine.
This was my understanding.
With a fully licensed MSSQL 2000 you can legally use the client tools on
MSDE 2000 clients?
DOM
|||hi Dom,
Dom wrote:
> Thanks Andrea,
> I followed the link you mentioned and on that page was directed to the
> following site:
> http://www.aspfaq.com/show.asp?id=2442
> which states :
> If you have an existing machine with SQL Server client tools
> installed, you can manage the MSDE instance through these... as long
> as you have a valid and legal Client Access License (CAL).
> You can take the SQL Server install CD, run it on the client(s), and
> choose "Install client tools only" during setup. This will install
> Query Analyzer, Enterprise Manager, and other useful tools-but will
> not install the database engine.
> This was my understanding.
> With a fully licensed MSSQL 2000 you can legally use the client tools
> on MSDE 2000 clients?
I had a private conversation with Steve some time ago about this matter...
he is not a "legal" guy too... but he has been told by legal representatives
that EM (QA and so on) is not legitimated to be used against production MSDE
instances... this is the reason he posted that way...
so, in my understanding, you never (or quite never, but I do really have no
idea when it could be legal) are legitimated to use SQL Server Client Tools
to manage MSDE...
my $0.02
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MSDE & EM Tools

Hi,
I intend using MSDE 2000 in conjunction with a fully licensed version of SQL
Server 2000. MSDE will be installed on several clients.
My understanding is as follows :
I can use Enterprise Manager on any or all of those client instances of MSDE
because I have a fully licensed copy of SQL Server 2000 with which they will
be replicating.
MSDN:
You can only use SQL Server tools and services in conjunction with MSDE if
you acquired MSDE through SQL Server 2000 (Developer Edition, Standard
Edition, or Enterprise Edition), and if you are using MSDE in conjunction
with a properly licensed copy of SQL Server 2000.
Am I in the clear?
Can I use any of the GUI tools?
Thanks in advance,
DOM.hi Dom,
Dom wrote:
> Hi,
> I intend using MSDE 2000 in conjunction with a fully licensed version
> of SQL Server 2000. MSDE will be installed on several clients.
> My understanding is as follows :
> I can use Enterprise Manager on any or all of those client instances
> of MSDE because I have a fully licensed copy of SQL Server 2000 with
> which they will be replicating.
> MSDN:
> You can only use SQL Server tools and services in conjunction with
> MSDE if you acquired MSDE through SQL Server 2000 (Developer Edition,
> Standard Edition, or Enterprise Edition), and if you are using MSDE
> in conjunction with a properly licensed copy of SQL Server 2000.
> Am I in the clear?
> Can I use any of the GUI tools?
> Thanks in advance,
> DOM
please have a look at Steve Dybing answer in http://tinyurl.com/d2qpa .. it
does not seem this is a legal solution...
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||Thanks Andrea,
I followed the link you mentioned and on that page was directed to the
following site:
http://www.aspfaq.com/show.asp?id=2442
which states :
If you have an existing machine with SQL Server client tools installed, you
can manage the MSDE instance through these... as long as you have a valid and
legal Client Access License (CAL).
You can take the SQL Server install CD, run it on the client(s), and choose
"Install client tools only" during setup. This will install Query Analyzer,
Enterprise Manager, and other useful toolsâ'but will not install the database
engine.
This was my understanding.
With a fully licensed MSSQL 2000 you can legally use the client tools on
MSDE 2000 clients'
DOM|||hi Dom,
Dom wrote:
> Thanks Andrea,
> I followed the link you mentioned and on that page was directed to the
> following site:
> http://www.aspfaq.com/show.asp?id=2442
> which states :
> If you have an existing machine with SQL Server client tools
> installed, you can manage the MSDE instance through these... as long
> as you have a valid and legal Client Access License (CAL).
> You can take the SQL Server install CD, run it on the client(s), and
> choose "Install client tools only" during setup. This will install
> Query Analyzer, Enterprise Manager, and other useful tools-but will
> not install the database engine.
> This was my understanding.
> With a fully licensed MSSQL 2000 you can legally use the client tools
> on MSDE 2000 clients'
I had a private conversation with Steve some time ago about this matter...
he is not a "legal" guy too... but he has been told by legal representatives
that EM (QA and so on) is not legitimated to be used against production MSDE
instances... this is the reason he posted that way...
so, in my understanding, you never (or quite never, but I do really have no
idea when it could be legal) are legitimated to use SQL Server Client Tools
to manage MSDE...
my $0.02
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MSDE & datadir

Is it possible that on the client's machine I install my MSDE instance in the default location (C:\Program Files\Microsoft SQL Server), i.e. do not specify any datadir or Targetdir location in the setup BUT still create my app's database (during installat
ion) in my app's folder, for e.g. in C:\Program Files\MyCompany\Data\ folder and then attach this database's .mdf file to my instance which I installed. Is it necessary that the database has to be in the data folder under the MSDE install location...
Please suggest.
I am asking because I want to have nothing but database in the my app's data folder.
>> still create my app's database (during installation) in my app's folder
Yes. This is possible. It is NOT mandated that the specific databases that
you create should also fall inside the default location. In event that you
create database without any specific paths then they are created in the
default location.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"qa" <anonymous@.discussions.microsoft.com> wrote in message
news:CC9D5500-4C13-45B6-9040-F4A908DAEB26@.microsoft.com...
> Is it possible that on the client's machine I install my MSDE instance in
the default location (C:\Program Files\Microsoft SQL Server), i.e. do not
specify any datadir or Targetdir location in the setup BUT still create my
app's database (during installation) in my app's folder, for e.g. in
C:\Program Files\MyCompany\Data\ folder and then attach this database's .mdf
file to my instance which I installed. Is it necessary that the database
has to be in the data folder under the MSDE install location...
> Please suggest.
> I am asking because I want to have nothing but database in the my app's
data folder.
|||Thanks Vinod for your reply. How can I specify path for the database (not the entire data folder) as C:\Program Files\MyCompany\Data\ while creating the database using scripts during the installation program. And is keeping the database away in myapp's
folder a recommended way of working..
qa
|||You have to specify the path to the database and log files in the CREATE
DATABASE statement of the script file. Example for a database called "HAS":
CREATE DATABASE [HAS] ON (NAME = N'HAS_Data', FILENAME = N'f:\Microsoft SQL
Server\MSSQL\data\HAS_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME =
N'HAS_Log', FILENAME = N'f:\Microsoft SQL Server\MSSQL\data\HAS_Log.LDF' ,
SIZE = 1, FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AS
GO
Willem
"qa" <anonymous@.discussions.microsoft.com> schreef in bericht
news:A7253E41-0E31-4D5C-967F-E7483AE75304@.microsoft.com...
> Thanks Vinod for your reply. How can I specify path for the database (not
the entire data folder) as C:\Program Files\MyCompany\Data\ while creating
the database using scripts during the installation program. And is keeping
the database away in myapp's folder a recommended way of working..
> qa

MSDE # connections

Hello All-
We are doing some performance monitoring of clients. We are running merge
replication ( push) on SQL Server 2000. Clients use MSDE.
We notice that merge replication is taking 5 connections to the client. I
thought that MSDE had a max 5 concurrent connections and then adds a
performance penalty when above 5. Our concern is that when our app connects
to MSDE, we are getting a big performance hit.
Is it valid for merge replication to take 5 connections? We verified this
number by monitoring and then stopping & restarting merge agent. The number
went from 7 ( merge agent at central db host enabled ) to 2 connections (
merge agent stopped).
Thanks,
bruce
actually its 8 simultaneous connections. Subsequent connections are
throttled. However if you run sp_who2, you will find that many of these
connections are sleeping, which means that they are not doing work.
So the short answer to your question is that you should not experience a
significant performance hit as long as you have only a few clients
connecting.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"rr" <bruceradtke@.REMOVEspamREMOVE.earthlink.net> wrote in message
news:b79Nc.104089$a92.87050@.twister.nyc.rr.com...
> Hello All-
> We are doing some performance monitoring of clients. We are running merge
> replication ( push) on SQL Server 2000. Clients use MSDE.
> We notice that merge replication is taking 5 connections to the client. I
> thought that MSDE had a max 5 concurrent connections and then adds a
> performance penalty when above 5. Our concern is that when our app
connects
> to MSDE, we are getting a big performance hit.
> Is it valid for merge replication to take 5 connections? We verified
this
> number by monitoring and then stopping & restarting merge agent. The
number
> went from 7 ( merge agent at central db host enabled ) to 2 connections (
> merge agent stopped).
> Thanks,
> bruce
>
>