Development, Staging, Production - best practice

Hi,
I would appreciate your advice in deploying enterprise BI solution. The task
at hand is this: I need to get the data from DW (SQL 2005), process into SSAS
and present with SSRS. I may also need to offer a third party OLAP browser as
a part this of solution. My initial thoughts about the setup for this are:
- SRV1: Development server, with all SQL 2005 components, IIS, and any third
party OLAP tools that we might pick.
-SRV2: Staging/Processing. Schedule and run SSIS to get data from DW, and
refresh and process SSAS cubes.
-SRV3: Production Reporting/Web server: This server would hold processed
cubes from SRV2 (archived and restored on SRV3), SSRS, IIS any any third
party OLAP browser that we may pick.
Initially there will be 2-3 developers, 15-20 cubes and 30-40 reports. We
are targeting to server 20-30 Report users.
Does the above setup make sense? Do I need dedicated web server with or
without SSRS? Please share thoughts or advise of any best practice articles.
Thank you.
ZoranHello Zoran,
The server setup need to be decide with that how large your cube is and how
many data your cube stored.
Yes, you need to seperate a stand alone server to process all the Cube data
and use the Reporting Services as the Front End Server.
I don't think you need to dedicate the web server with Reporting Services
because the Reporting Services is also a web application in the web front
end. Also, considering your report users, your report web quest will not at
a high level.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you Wei,
Currently all our cubes are MOLAP, with database sizes ranging from 30 MB to
300 MB. I read that one of the most efficient ways to move from Staging to
Production is backup and retore. But, is it good practice to keep production
cubes on the same server as SSRS? Technically, there would be no processing
on that server, cubes would only sit there for user's queries...
Zoran
"Wei Lu [MSFT]" wrote:
> Hello Zoran,
> The server setup need to be decide with that how large your cube is and how
> many data your cube stored.
> Yes, you need to seperate a stand alone server to process all the Cube data
> and use the Reporting Services as the Front End Server.
> I don't think you need to dedicate the web server with Reporting Services
> because the Reporting Services is also a web application in the web front
> end. Also, considering your report users, your report web quest will not at
> a high level.
> Hope this helps.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Zoran,
Whether putting the SSRS together with the Cube depends on the size and how
system resource used by the SSAS.
In your scenario, I think it is OK for you to put the SSRS on the same
server at this time. But if your cube grows, you may need to seperate the
SSAS with SSRS.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you Wei.
In your previous post you you mentioned "depends how system resources
(are)used by SSAS". In my scenario, they would be used for user queries
(direct and via SSRS). There would be no processing done on this server.
I can certainly see the number of cubes grow in near future. Is one server
for SSRS and SSAS going to work, or am I setting myself up for upgrade in
near future?
Regards,
Zoran Knezic
"Wei Lu [MSFT]" wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||RS 2005 does all rendering in RAM. As long as you have enough RAM I think
you will be OK. I would suggest starting off on one server. Perhaps let
management know that you will need to monitor the usage and there is a
chance they will need to be split onto separate servers. RS 2008 is going to
be much smarter and with how it renders.
You said the following: Initially there will be 2-3 developers, 15-20 cubes
and 30-40 reports. We
are targeting to server 20-30 Report users.
This is not that many users. I do not have cubes but I have a datamart which
includes a table with 150 million rows (small rows admittedly) plus several
tables that have several million rows. RS 2005 and the datamart are on the
same server without difficulty. Similar number of users (more reports). My
server is old (4 years old) and ready for replacement. I have 4
processors(2.4 GHz), 4 Gigs of Ram (so this is not a huge powerful box by
any means). Performance is very very good for me. Running SQL 2005 Standard
edition.
Since you are just getting started. If you are able to plan on using RS 2008
I would do so. RS 2008 should be able to use a SQL 2005 db for its
metadata/object caching so it is a licensing issue.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"zk_" <zk_@.newsgroup.nospam> wrote in message
news:EB03F7C3-13D3-40D2-8991-7FAF755EEFCE@.microsoft.com...
> Thank you Wei.
> In your previous post you you mentioned "depends how system resources
> (are)used by SSAS". In my scenario, they would be used for user queries
> (direct and via SSRS). There would be no processing done on this server.
> I can certainly see the number of cubes grow in near future. Is one server
> for SSRS and SSAS going to work, or am I setting myself up for upgrade in
> near future?
> Regards,
> Zoran Knezic
> "Wei Lu [MSFT]" wrote:
>> Hi ,
>> How is everything going? Please feel free to let me know if you need any
>> assistance.
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Thank you Bruce.
It is good to hear a first hand experience with DB and SSRS on the same
server.
I am sure that SSAS creates bigger overhead then DB, but judging by your
experience, I might be OK.
My IMIT colleagues came up with 2x 2.8 GHz server, with 16 Gb of RAM.
Alternatively I might be able to suggest two servers, 2x2.8 GHz 8GB of RAM
each.
Once again, thank you for your time.
Zoran Knezic
"Bruce L-C [MVP]" wrote:
> RS 2005 does all rendering in RAM. As long as you have enough RAM I think
> you will be OK. I would suggest starting off on one server. Perhaps let
> management know that you will need to monitor the usage and there is a
> chance they will need to be split onto separate servers. RS 2008 is going to
> be much smarter and with how it renders.
> You said the following: Initially there will be 2-3 developers, 15-20 cubes
> and 30-40 reports. We
> are targeting to server 20-30 Report users.
> This is not that many users. I do not have cubes but I have a datamart which
> includes a table with 150 million rows (small rows admittedly) plus several
> tables that have several million rows. RS 2005 and the datamart are on the
> same server without difficulty. Similar number of users (more reports). My
> server is old (4 years old) and ready for replacement. I have 4
> processors(2.4 GHz), 4 Gigs of Ram (so this is not a huge powerful box by
> any means). Performance is very very good for me. Running SQL 2005 Standard
> edition.
> Since you are just getting started. If you are able to plan on using RS 2008
> I would do so. RS 2008 should be able to use a SQL 2005 db for its
> metadata/object caching so it is a licensing issue.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "zk_" <zk_@.newsgroup.nospam> wrote in message
> news:EB03F7C3-13D3-40D2-8991-7FAF755EEFCE@.microsoft.com...
> > Thank you Wei.
> > In your previous post you you mentioned "depends how system resources
> > (are)used by SSAS". In my scenario, they would be used for user queries
> > (direct and via SSRS). There would be no processing done on this server.
> > I can certainly see the number of cubes grow in near future. Is one server
> > for SSRS and SSAS going to work, or am I setting myself up for upgrade in
> > near future?
> >
> > Regards,
> > Zoran Knezic
> >
> > "Wei Lu [MSFT]" wrote:
> >
> >> Hi ,
> >>
> >> How is everything going? Please feel free to let me know if you need any
> >> assistance.
> >>
> >> Sincerely,
> >>
> >> Wei Lu
> >> Microsoft Online Community Support
> >>
> >> ==================================================> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >>
> >> ==================================================> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
>
>

Developer Edition having issues connecting to server via TCP/IP

Don't know if there is a simple solution for this one or not. When
running SQL server on a machine with 2000 loaded and the complete SQL
package I don't have any issues. Now I'm trying to login using my XP
machine with MSSQL developer edition, and I can not connect to my
remote servers via TCP/IP. It can see the server, and it establishes a
connection, but I can't access my files, and the connection seems
incredibly slow. I have checked my firewall, and its fine. I'm just
wondering why my 2000 system can access it, but my XP can't. Any
ideas?Chris (chris.wisherd@.kenblanchard.com) writes:
> Don't know if there is a simple solution for this one or not. When
> running SQL server on a machine with 2000 loaded and the complete SQL
> package I don't have any issues. Now I'm trying to login using my XP
> machine with MSSQL developer edition, and I can not connect to my
> remote servers via TCP/IP. It can see the server, and it establishes a
> connection, but I can't access my files, and the connection seems
> incredibly slow. I have checked my firewall, and its fine. I'm just
> wondering why my 2000 system can access it, but my XP can't. Any
> ideas?

I'm confused, can't you connect at all, or is the communication just slow?

On which machine does the SQL Server you are connecting to run? Is the XP
machine in the same domain as the SQL Server machine?

What files are you unable to access? You usually don't use SQL Server
to access files.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> Chris (chris.wisherd@.kenblanchard.com) writes:
> > Don't know if there is a simple solution for this one or not. When
> > running SQL server on a machine with 2000 loaded and the complete SQL
> > package I don't have any issues. Now I'm trying to login using my XP
> > machine with MSSQL developer edition, and I can not connect to my
> > remote servers via TCP/IP. It can see the server, and it establishes a
> > connection, but I can't access my files, and the connection seems
> > incredibly slow. I have checked my firewall, and its fine. I'm just
> > wondering why my 2000 system can access it, but my XP can't. Any
> > ideas?
> I'm confused, can't you connect at all, or is the communication just slow?
> On which machine does the SQL Server you are connecting to run? Is the XP
> machine in the same domain as the SQL Server machine?
> What files are you unable to access? You usually don't use SQL Server
> to access files.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

I can connect to the server, but it is slow. But I can't access
information such as the databases. It comes up with no items. I also
can't backup any of the data to a local machine. There wasn't any
issue until we loaded the 2003 Developer Edition on our XP machines.
I've loaded both service packs 3 and 4 hoping that would solve the
problem. I've checked TCP/IP setting, and the firewall.

The server I'm trying to access is a remote server running Windows
2000, with service pack 4, and it is in a different domain.|||Chris (chris.wisherd@.kenblanchard.com) writes:
> I can connect to the server, but it is slow. But I can't access
> information such as the databases. It comes up with no items. I also
> can't backup any of the data to a local machine. There wasn't any
> issue until we loaded the 2003 Developer Edition on our XP machines.
> I've loaded both service packs 3 and 4 hoping that would solve the
> problem. I've checked TCP/IP setting, and the firewall.
> The server I'm trying to access is a remote server running Windows
> 2000, with service pack 4, and it is in a different domain.

I realise that I should have asked what you are connecting with and
with which credentials.

From what you say, it sounds as it you connect with Enterprise Manager
with integrated security, but your Windows login has not been added to
BUILTIN\Administrators and on top of all databases are set to autoclose.
When databases are set to autoclose, and you connect as a non-priv user,
EM has to access every database to see if you are permitted, and this
can take forever.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Dev. Team - Keeping Three Tiers in Sync

This may not be a MSSQL-specific question, but
I wanted to ask it here first, in case there's
a MSSQL and/or SourceSafe solution that will help.

Our dev team is having some difficulty with
keeping the nightly builds in sync with the
stored proc mods. I'm wondering if there are
some good case studies on how to avoid this
"drift". Something like genning a new DB from
checked-in SPs, etc. alongside each regular build,
then always have a paired enterprise app/database
duo that is tagged and added to a history.

FWIW, we have a 3-tier .NET/C# app, and
ADO.NET is throwing exceptions every other
day.

If the suggestion is to whip the DB guys, that
works for me as well. ;-)

Nah, there's much love there.

Thanks in advance,
~swoozWe keep all of our DDL scripts under source control. These are labeled and
built along with the app code as part of an installer custom action. We
promote all stored procedures, views, and functions along with the rest of
the app so the interfaces don't get out of sync as long as the checked-in
code is correct.

Our database installer allows either a new our upgrade installation. Even
if the 'new' install isn't needed for production, it facilitates testing
since it's the 'gold' database schema. We also include upgrade capability
as needed for production releases. The bottom line is that new and upgraded
schema are identical.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"swoozie" <swooziep@.yahoo.com> wrote in message
news:1112826148.798845.123840@.g14g2000cwa.googlegr oups.com...
> This may not be a MSSQL-specific question, but
> I wanted to ask it here first, in case there's
> a MSSQL and/or SourceSafe solution that will help.
> Our dev team is having some difficulty with
> keeping the nightly builds in sync with the
> stored proc mods. I'm wondering if there are
> some good case studies on how to avoid this
> "drift". Something like genning a new DB from
> checked-in SPs, etc. alongside each regular build,
> then always have a paired enterprise app/database
> duo that is tagged and added to a history.
> FWIW, we have a 3-tier .NET/C# app, and
> ADO.NET is throwing exceptions every other
> day.
> If the suggestion is to whip the DB guys, that
> works for me as well. ;-)
> Nah, there's much love there.
> Thanks in advance,
> ~swooz|||We use a product called DB Ghost to build our SQL Server databases
directly from SourceSafe every 10 minutes. A script checks the report
it produces for any errors and automatically emails all of the DBAs and
Development team if any are found. This covers any syntax or
dependency issues with checked in code.

If everything builds OK DB Ghost is then used to produce an upgrade
script against a restored copy of our test database. If this fails the
problem is emailed out and fixed there and then. The test database
copy is then restored again and the compare rerun - if it works then we
have a delta script that we can run in Query Analyzer against our real
test database. This same approach is used all the way to production.
The script produced also works first time - I've used other products
before but none of them come close to producing a working delta script
in the first run - I always had to correct object dependency problems
manually in Query Analyzer first.

The real beauty of this is that we only keep the delta script produced
by our overnight build which starts from a labelled baseline of all
code in VSS. This means that our SQL Code is 100% in line with our
VB6/.NET and ASP code every single time we do a release. Sure, it
doesn't get around the problem of developers not checking their changes
in properly but it does give us a great audit trail which means we get
to the root causes of those tricky test database problems real quick.

We are even thinking of losing the delta script altogether and just
letting DB Ghost do the upgrade dynamically, that way our baselined,
labelled scripts in VSS are the real 'source database' - this is
totally cool stuff.

Determining when to send the report based on information inside the report?!?

Hello

Here is a tricky problem to which I didn't find any "good" solution:

I have a report which should be sent to the subscriber only when there is something to report. Mostly the report will be empty, because it will only report emergency-type-of-stuff. Now I have implemented the report logic twice, once in the report and once in a data driven subscription which returns a fixed email address when the report data query returns data.

Ideal solution would be one where you can decide in the report generation is it sent or not. Has anyone any ideas / solutions conserning this problem? How could you decide during the report generation that is report sent or not? Any help is highly appreciated...

Thanks!

- rusi

Well, that seems a bit interesting...one problem I see is that the report generation could be performed anytime...i.e. on a schedule, or also anytime a user interactivelly requests to view the report. So, if for example you did include logic to email the report anytime the report generation included data, would you want to get multiple emails time after time if a user kept viewing the report every 10-15 minutes or something like that?

Seems to me that your best bet would be to encapsulate the data-portion of the report (i.e. the queries/data sets) within a stored procedure that you can call from multiple places, and if the sp returns any results, fire off an email from your automated systems when you would like under controlled circumstances.

As it is, I'm not entirely sure that you can even fire off an email based on report content...maybe someone else can chime in on this part...

|||Thanks for the answer!
That is true that the report is ran every time, in this case twice a day, and it sends a report of orders that have not been confirmed. But there is no need to send the report if there are no unconfirmed orders eg. nothing to report...
It is possible to solve this putting the logic in one sp, but still have to implement to logic somewhere else then in the report. I'm searching for the best solution in which the report includes all the logic.
|||This is a fairly common request. Your current solution is the best work around that we have right now. We are looking at the possibility of adding this feature in future versions.