Best practices for an installation of SQL Server 2000 Reporting Se

Hi,
Where would one install SQL Service 2000 Reporting Services? I though about
installing them on a Web Server (with NO local SQL Server installed on) and
connection remote to a SQL Server 2000. Does this approach make sense?
I donâ't have a good feeling installing the Web Services on a Database Server
where I have to install IIS and the .NET Framework as well.
Best regards,
DanielThis will work fine. One point to keep in mind is that you have to have a
SQL Server license on the box with RS.
What I do is have SQL Server just for RS on the box. The data the reports
are based on reside on a different box. My feeling is why not have SQL
Server for the object caching that RS uses it for be on the same box. I have
to pay for it anyway.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in message
news:A0462F4B-9A38-4B88-B2DB-C42B6514EF8B@.microsoft.com...
> Hi,
> Where would one install SQL Service 2000 Reporting Services? I though
> about
> installing them on a Web Server (with NO local SQL Server installed on)
> and
> connection remote to a SQL Server 2000. Does this approach make sense?
> I don't have a good feeling installing the Web Services on a Database
> Server
> where I have to install IIS and the .NET Framework as well.
> Best regards,
> Daniel|||Bruce,
Thanks for your answer! Just to make sure I got your point. You suggest to
have both (SQL Server and RS) installed on the same computer? Am I also right
that this constellation would only require on SQL Server license?
And one more question: Can RS be installed on a computer with NO SQL Server
installed? I had some troulbe installing RS on my Web Server where on SQL
Server is installed on. The installation skipped the part where I am supposed
to select the SQL Server but told me all is fine :-( This problem though
could have been caused by the Terminal Services which I had not disabled
(Will try tomorrow).
Thanks!
Daniel
"Bruce L-C [MVP]" wrote:
> This will work fine. One point to keep in mind is that you have to have a
> SQL Server license on the box with RS.
> What I do is have SQL Server just for RS on the box. The data the reports
> are based on reside on a different box. My feeling is why not have SQL
> Server for the object caching that RS uses it for be on the same box. I have
> to pay for it anyway.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in message
> news:A0462F4B-9A38-4B88-B2DB-C42B6514EF8B@.microsoft.com...
> > Hi,
> >
> > Where would one install SQL Service 2000 Reporting Services? I though
> > about
> > installing them on a Web Server (with NO local SQL Server installed on)
> > and
> > connection remote to a SQL Server 2000. Does this approach make sense?
> >
> > I don't have a good feeling installing the Web Services on a Database
> > Server
> > where I have to install IIS and the .NET Framework as well.
> >
> > Best regards,
> >
> > Daniel
>
>|||From a licensing perspective there is no difference. You have to have a SQL
Server license for the box that has Reporting Services. Reporting Services
uses SQL Server for it's object storage. For instance, all reports are
stored in the database. You will not see any rdl files on the server. It
uses SQL Server for other objects as well. So, RS has to have SQL Server
somewhere. If you use a SQL Server that is somewhere other than the box you
are installing RS on you have to use the command line install and specify
where SQL Server can be found. I have not done this myself. I assume the
info on how to do this is in the readme. If you use the setup program it
plans that SQL Server is on the box you are installing RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in message
news:E02F3FE4-EDD4-4A35-AD4D-D772A811424A@.microsoft.com...
> Bruce,
> Thanks for your answer! Just to make sure I got your point. You suggest to
> have both (SQL Server and RS) installed on the same computer? Am I also
> right
> that this constellation would only require on SQL Server license?
> And one more question: Can RS be installed on a computer with NO SQL
> Server
> installed? I had some troulbe installing RS on my Web Server where on SQL
> Server is installed on. The installation skipped the part where I am
> supposed
> to select the SQL Server but told me all is fine :-( This problem though
> could have been caused by the Terminal Services which I had not disabled
> (Will try tomorrow).
> Thanks!
> Daniel
>
> "Bruce L-C [MVP]" wrote:
>> This will work fine. One point to keep in mind is that you have to have a
>> SQL Server license on the box with RS.
>> What I do is have SQL Server just for RS on the box. The data the reports
>> are based on reside on a different box. My feeling is why not have SQL
>> Server for the object caching that RS uses it for be on the same box. I
>> have
>> to pay for it anyway.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in message
>> news:A0462F4B-9A38-4B88-B2DB-C42B6514EF8B@.microsoft.com...
>> > Hi,
>> >
>> > Where would one install SQL Service 2000 Reporting Services? I though
>> > about
>> > installing them on a Web Server (with NO local SQL Server installed on)
>> > and
>> > connection remote to a SQL Server 2000. Does this approach make sense?
>> >
>> > I don't have a good feeling installing the Web Services on a Database
>> > Server
>> > where I have to install IIS and the .NET Framework as well.
>> >
>> > Best regards,
>> >
>> > Daniel
>>|||Thank you Bruce,
this is good to know!
Daniel
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> schrieb im Newsbeitrag
news:%238AtkHJYFHA.2796@.TK2MSFTNGP09.phx.gbl...
> From a licensing perspective there is no difference. You have to have a
> SQL Server license for the box that has Reporting Services. Reporting
> Services uses SQL Server for it's object storage. For instance, all
> reports are stored in the database. You will not see any rdl files on the
> server. It uses SQL Server for other objects as well. So, RS has to have
> SQL Server somewhere. If you use a SQL Server that is somewhere other than
> the box you are installing RS on you have to use the command line install
> and specify where SQL Server can be found. I have not done this myself. I
> assume the info on how to do this is in the readme. If you use the setup
> program it plans that SQL Server is on the box you are installing RS.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in message
> news:E02F3FE4-EDD4-4A35-AD4D-D772A811424A@.microsoft.com...
>> Bruce,
>> Thanks for your answer! Just to make sure I got your point. You suggest
>> to
>> have both (SQL Server and RS) installed on the same computer? Am I also
>> right
>> that this constellation would only require on SQL Server license?
>> And one more question: Can RS be installed on a computer with NO SQL
>> Server
>> installed? I had some troulbe installing RS on my Web Server where on SQL
>> Server is installed on. The installation skipped the part where I am
>> supposed
>> to select the SQL Server but told me all is fine :-( This problem though
>> could have been caused by the Terminal Services which I had not disabled
>> (Will try tomorrow).
>> Thanks!
>> Daniel
>>
>> "Bruce L-C [MVP]" wrote:
>> This will work fine. One point to keep in mind is that you have to have
>> a
>> SQL Server license on the box with RS.
>> What I do is have SQL Server just for RS on the box. The data the
>> reports
>> are based on reside on a different box. My feeling is why not have SQL
>> Server for the object caching that RS uses it for be on the same box. I
>> have
>> to pay for it anyway.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Daniel Walzenbach" <daniel.walzenbach@.newsgroup.nospam> wrote in
>> message
>> news:A0462F4B-9A38-4B88-B2DB-C42B6514EF8B@.microsoft.com...
>> > Hi,
>> >
>> > Where would one install SQL Service 2000 Reporting Services? I though
>> > about
>> > installing them on a Web Server (with NO local SQL Server installed
>> > on)
>> > and
>> > connection remote to a SQL Server 2000. Does this approach make sense?
>> >
>> > I don't have a good feeling installing the Web Services on a Database
>> > Server
>> > where I have to install IIS and the .NET Framework as well.
>> >
>> > Best regards,
>> >
>> > Daniel
>>
>|||" If you use a SQL Server that is somewhere other than the box you
are installing RS on you have to use the command line install and specify
where SQL Server can be found."-Bruce
Are you absolutely 100% certain this is the case?
I'm certainly stuck trying to install enterprise RS on the web server
pointing to a SQL server DB on another sql server(2000). Problem is the
install ends "successfully" but then throws an activivation error. No
resolution for this error in sight.
However if I MUST use the commend line too to configure RS across servers
this may explain the activiation error. I don't remember finding anything
documented telling me I must use this command line approach.
Please verify this is true. Thanks! Could save a lot of headaches...

Best approach to updating remote server?

I have a local production server, and a web server at a remote ISP connected
via a VPN. Both servers are running MS SQL 2000. I wish to do an extract
of data on the production server and update it to the web server daily. The
web server only gets a subset of the information on the production server.
I know I can't be the first to need this so what I am searching for is the
best way to accomplish this. Do I refresh the web server daily (about 500MB
of data)? Should I send each table with an indicator as to
Add/Change/Delete, and write a procedure to import? or is there just another
solution I am overlooking.
Any suggestions would be appreciated.
TIA, MarkHi Mark.
A common approach to achieving this is to create a DTS package that
encapsulates the process, applies business rules to the data etc & then
schedule it to run from the local production server. I've done this with
various ISP hosted SQL databases with fairly good success.
Is your ISP hosted SQL Server dedicated or shared? If dedicated, many
options may open up to you that a shared server might have available.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:enU%238d8DEHA.2932@.tk2msftngp13.phx.gbl...
> I have a local production server, and a web server at a remote ISP
connected
> via a VPN. Both servers are running MS SQL 2000. I wish to do an extract
> of data on the production server and update it to the web server daily.
The
> web server only gets a subset of the information on the production server.
> I know I can't be the first to need this so what I am searching for is the
> best way to accomplish this. Do I refresh the web server daily (about
500MB
> of data)? Should I send each table with an indicator as to
> Add/Change/Delete, and write a procedure to import? or is there just
another
> solution I am overlooking.
> Any suggestions would be appreciated.
> TIA, Mark
>

Behaviour issue with remote inserts

I have an issue doing remote inserts and don't understand why there is a behaviour difference between local and remote inserts. So far i have not been able to find an

answer. It may be something to do with parametised query execution

but i`m not sure yet.

Below is the scenario

If i do a

insert into server.db.dbo.remotetable

select * from dbo.localtable

and the local select returns say 3 values, 3 inserts will occur on the

destination server where as if the insert into is local only 1 insert would occur!

Why? Is it possible to get the remote query to behave like a local and do the 3 records in 1 insert? Its currently playing havoc with a trigger i have on a production box.

To test this i've supplied some very simple code. Setup instructions are commented into the code. Have not coded a linked server creation though.

At the end look at the tbllog and you will see what i mean.

All advise gratefully received!

Cheers
Andrew

Sample Code

---

--CREATE this table on Source server and Destination server

CREATE TABLE [dbo].[Input] (
[server] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[dt] [datetime] NULL
) ON [PRIMARY]
GO

--Create these on the destination server
CREATE TABLE [dbo].[tbllog] (
[Server] [char] (100) COLLATE Latin1_General_CI_AS NULL ,
[tst_Count] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[destination] (
[server] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[dt] [datetime] NULL
) ON [PRIMARY]
GO

--Create this table on the destination table on destination server
CREATE TRIGGER [destination_ins] ON [dbo].[destination]
FOR INSERT, UPDATE, DELETE
AS
insert into dbo.tbllog
select server,count(server) from inserted group by server
GO

--Insert some sample data into the input table on Source server
insert into input values ('Remote','1/1/2000')
insert into input values ('Remote','1/2/2000')
insert into input values ('Remote','1/3/2000')

--Insert some sample data into the input table on Destination server
insert into input values ('Local','1/1/2000')
insert into input values ('Local','1/2/2000')
insert into input values ('Local','1/3/2000')

--Run the insert from the source server
insert into destinationsrv.destdb.dbo.destination
select * from input

--Run the insert from the destination server (as a local query)
insert into dbo.destination
select * from input

--On the destination server, run select from the log table which was populated by trigger.
--You will see remote insert has 3 rows but local only has 1!
select * from tbllogHmmm. Looks like SQL Server uses cursors to run any remote query, even if it is run via OPENROWSET (just tested it). I wonder if there is a way to avoid this, except moronic workarounds like temp table & remote SP.|||I've been hunting high and low for a way to get it to behave the same way as a local insert and not do multiple inserts without resorting to temp tables and remote sp's. Was hoping there might even have been a reg setting but not found one.

I'd also like to understand why it has to break down into multiple inserts!

Still looking for the answer but no joy. |||

I'm dealing with the same problem. Has anyone found a solution? I'd hate to resort to calling a remote stored procedure just to pull data across the link.

Surely people have encountered this problem before - I'm able to reproduce it in both Sql Server 2000 and 2005. Or do people normally only use linkedservers for queries?