Best Practises for Server Sizing for MS Reporting Services

We are planning to deploy the MS Reporting Services. Unfortunately,
there isn't much guidance on how to effectively size a server.

Here are my questions:

1) In an environment with 1 reporting server (hosting the reporting
data layer, application, and management layer) connecting to 1 or more
SQL servers (over 2 trunked 1Gbps switches connections), what is the
constraining resource when running large reports against a database of
say 4GB in size? CPU, RAM, DISK, or Network speed on the Reporting
Server? Or the Data hosting SQL server? Or does it depend on code?
2) If I have a Duel Core CPU, do I buy 1 CPU license. MS had responded
to the HyperThreading as 2 virtual CPUs with the statement that you buy
CPU licenses based on socket count effectively. This would imply that
if I buy a 4 Duel Core AMD Opteron Proliant 585 (8 CPU cores in 4
packages / sockets), I can run MS Reporting Services Standard Edition.
Is that true?

Any input or direction would be appreciated.

Paul V.You might want to post in microsoft.public.sqlserver.reportingsvcs to
see if you get a better response.

For licensing issues, there is an FAQ, but if it doesn't answer your
question it would probably be best to contact Microsoft directly for a
definite answer:

http://www.microsoft.com/sql/howtobuy/faq.mspx

Simon

Best Practice for Report Projects Related To Application Solutions

Hello Reporting Services Gurus!

I'm about to start on my first reporting services project, but before I mess it up, I'm looking for some guidance on how best to achieve my mission. Here's what I'm looking to achieve:

I have a datacentric application (SQL Server 2005 Express w/ Advanced Services backend) in which I want to build about 50 "canned" reports for the end users. I want to build the reports utilizing server mode so I can take advantage of some of Reporting Services advanced features. I'm not sure what the best practice would be to build the reporting services project. Is it better to include the report project as another project within the application solution? Or, should I build the report project independent of the application solution? What are the pros and cons of doing it either way? How does including the report project build if it's included in the application solution? How would a ClickOnce deployment deploy the report project to the report server?

My ultimate goal would be to have an "off-the-shelf" software solution that includes an installation package consisting of the application project and report project. Is it even possible due to the Reporting Services architecture to achieve an install in this manner with ClickOnce, Windows Installer, or Installshield? Or, is building the report project indepedent of the application project and deploying the reports to the report server "manually" (i.e. deploy within the report server project) the only solution?

Any help would be greatly appreciated!

Tony

I haven't received any feedback yet, so I thought I'd try bumping it back to the top of the thread.

Thanks for any help!

Best Practice for MSDE User permissions

Hi All
I am new to MSDE/SQL Server and need some guidance on best practices for
user permissions.
I have a VB6 program running in a bakery factory
The computer network is a peer to peer 3 computer network running WIndowsXP
MSDE runs on computer A and the data entry person runs my program on this
machine to enter daily orders for their customers
A manager needs to access the MSDE data from another computer for reporting
tasks and is not allowed to enter or modify data
I am sure I can't use Windows authentication as it is only a peer network.
Is this correct?
Should I create a new Login and set individual permissions on each table or
is it OK to use the sa account etc?
Any ideas appreciated
Regards
Steve> I am sure I can't use Windows authentication as it is only a peer network.
> Is this correct?
Windows authentication is problematic when you have multiple computers
without a domain. It is possible by mapping a drive on the client to the
SQL Server using a local server account but this is a kluge.

> Should I create a new Login and set individual permissions on each table
> or
> is it OK to use the sa account etc?
I suggest you use SQL authentication and assign permissions to roles. You
can prompt for the user's SQL login and password during application at
startup. Never use the 'sa' login for routine application access.
USE MyDatabase
--setup role-based security
EXEC sp_addrole 'Manager'
EXEC sp_addrole 'Clerk'
GRANT SELECT ON MyTable TO Manager
GRANT SELECT, INSERT, UPDATE, DELETE ON MyOtherTable TO Manager
GRANT SELECT ON MyOtherTable TO Clerk
--create login for managers
EXEC sp_addlogin 'SomeManager', 'SomeManagerPassword', 'MyDatabase'
EXEC sp_grantdbaccess 'SomeManager'
EXEC sp_addrolemember 'Manager', 'SomeManager'
--create login for clerks
EXEC sp_addlogin 'SomeClerk', 'SomeClerkPassword', 'MyDatabase'
EXEC sp_grantdbaccess 'SomeClerk'
EXEC sp_addrolemember 'Clerk', 'SomeClerk'
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:6DE28FBB-20B4-4637-BC82-7BDDD9FDA62B@.microsoft.com...
> Hi All
> I am new to MSDE/SQL Server and need some guidance on best practices for
> user permissions.
> I have a VB6 program running in a bakery factory
> The computer network is a peer to peer 3 computer network running
> WIndowsXP
> MSDE runs on computer A and the data entry person runs my program on this
> machine to enter daily orders for their customers
> A manager needs to access the MSDE data from another computer for
> reporting
> tasks and is not allowed to enter or modify data
> I am sure I can't use Windows authentication as it is only a peer network.
> Is this correct?
> Should I create a new Login and set individual permissions on each table
> or
> is it OK to use the sa account etc?
> Any ideas appreciated
> --
> Regards
> Steve

Best practice for data dictionary in SQL Server 2005

Hi ,all here,

Would please anyone here give me any guidance and advices for best practice of data dictionary in SQL Server 2005?

I have restored a large insurance claims database with up to more than 300 tables, massively, most of them are empty tables, many of them dont have any keys, contrains, indexes, and more difficult, there is no any data dictionary for the database which gets me stuck in the understanding of the data at the moment. Thus I think data dictionary is very important for a database.

Will anyone gives me advices for that? Really need help.

Thank you very much in advance for any help.

With best regards,

Yours sincerely,

Hi, all experts,

Any advices?

Thanks a lot.

With best regards,

Yours sincerely,

|||I think the word data dictionary is not quite sure for us, do you mean data dictionary like in Oracle where you can grap the information of the objects stored in the database ?

HTH, Jens Suessmeyer

http://www.sqlserver2005.de
|||

Hi,Jens,

Thanks. Yes, what I mean here data dictionary is the description of the database objects from tables to all attributes in tables. I mean when other users other than the database designers are using the database data, without any supportive data dictionary for explanation about what the database and all its fields are about , users definitely have difficulty understanding it to further analyze it.

Hope my information above is clear for my question to be understood.

Thank you very much.

With best regards,

Yours sincerely,

|||The simple collection of the database objects can be found in the INFORMATION_SCHEMA Views. If you want to implement custom description, you can use the extended properties for that. If you have the chance to use Visio are another other data modeling program, I would choose that ín your case.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi, Jens,

Thank you very much for your kind and very helpful guidance and advices.

But the INFORMATION-SCHEMA views are generated automatically by the system? not customized by the database designer, the INFORMATION-SCHEMA VIEWS are just the collection of the data types, scale etc. while not user-defined description of each attibutes like what is this attribute used for, what dose the data in this attribute represent etc.

Is it possible for users to alter the view like INFORMATION_SCHEMA.COLUMNS to add an addtional column used to describe the columns used there by using T-SQL query?

Yours further advices and guidances are most appreciated.

With best regards,

Yours sincerely

|||

You can use sys.sp_addextendedproperty to add column description. You could find more detail at BOL, ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/565483ea-875b-4133-b327-d0006d2d7b4c.htm.

EXEC sys.sp_addextendedproperty
@.name=N'Caption'
,@.value=N'This is a table description'
,@.level0type=N'SCHEMA'
,@.level0name=N'dbo'
,@.level1type=N'TABLE'
,@.level1name=N'TableName'
GO
EXEC sys.sp_addextendedproperty
@.name=N'Caption'
,@.value=N'This is a column description'
,@.level0type=N'SCHEMA'
,@.level0name=N'dbo'
,@.level1type=N'TABLE'
,@.level1name=N'TableName'
,@.level2type=N'COLUMN'
,@.level2name=N'Col1'
GO

|||

Hi, Somjai,

Thank you very much for your kind advices.

But I copied the above scripts into the query window, it did not work? Do I need to replay any of the amove information with my specific information? Thanks a lot for your further guidance and advices.

With best regards,

Yours sincerely,

|||

Helen,

Yes, you need to replace with your specific information.

EXEC sys.sp_addextendedproperty
@.name=N'Caption'
,@.value=N'This is a template table' --Your table definition
,@.level0type=N'SCHEMA'
,@.level0name=N'dbo' --Your scheme name (when it is not dbo)
,@.level1type=N'TABLE'
,@.level1name=N'TableName' --Your table name
GO
EXEC sys.sp_addextendedproperty
@.name=N'Caption'
,@.value=N'This is a template column' --Your column definition
,@.level0type=N'SCHEMA'
,@.level0name=N'dbo' --Your scheme name (when it is not dbo)
,@.level1type=N'TABLE'
,@.level1name=N'TableName' --Your table name
,@.level2type=N'COLUMN'
,@.level2name=N'Col1' --Your column name
GO

You will need to repeat second EXEC for all of your columns in this table. I usually create a file for each tables (use table name as file name) and put these statements right after my table creation in the same file. I also put these files in VSS which allow me to version and share with other people.

Regards,

|||

Hi,Somjai, thank you very much for your kind guidance and advices.

But I cant see any advantage by using this system stored procedure to add the descriptions for the columns by repeating so many times for all the columns which jsut result in a caption added to the extened property for the objects.

The above results seems not as easy as directly adding the description for the objects within the object's property dialog.

By the way, what is VSS as you mentioned?

Thanks a lot.

With best regards,

Yours sincerely,

Best practice for data dictionary in SQL Server 2005

Hi ,all here,

Would please anyone here give me any guidance and advices for best practice of data dictionary in SQL Server 2005?

I have restored a large insurance claims database with up to more than 300 tables, massively, most of them are empty tables, many of them dont have any keys, contrains, indexes, and more difficult, there is no any data dictionary for the database which gets me stuck in the understanding of the data at the moment. Thus I think data dictionary is very important for a database.

Will anyone gives me advices for that? Really need help.

Thank you very much in advance for any help.

With best regards,

Yours sincerely,

Hi, all experts,

Any advices?

Thanks a lot.

With best regards,

Yours sincerely,

|||I think the word data dictionary is not quite sure for us, do you mean data dictionary like in Oracle where you can grap the information of the objects stored in the database ?

HTH, Jens Suessmeyer

http://www.sqlserver2005.de
|||

Hi,Jens,

Thanks. Yes, what I mean here data dictionary is the description of the database objects from tables to all attributes in tables. I mean when other users other than the database designers are using the database data, without any supportive data dictionary for explanation about what the database and all its fields are about , users definitely have difficulty understanding it to further analyze it.

Hope my information above is clear for my question to be understood.

Thank you very much.

With best regards,

Yours sincerely,

|||The simple collection of the database objects can be found in the INFORMATION_SCHEMA Views. If you want to implement custom description, you can use the extended properties for that. If you have the chance to use Visio are another other data modeling program, I would choose that ín your case.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi, Jens,

Thank you very much for your kind and very helpful guidance and advices.

But the INFORMATION-SCHEMA views are generated automatically by the system? not customized by the database designer, the INFORMATION-SCHEMA VIEWS are just the collection of the data types, scale etc. while not user-defined description of each attibutes like what is this attribute used for, what dose the data in this attribute represent etc.

Is it possible for users to alter the view like INFORMATION_SCHEMA.COLUMNS to add an addtional column used to describe the columns used there by using T-SQL query?

Yours further advices and guidances are most appreciated.

With best regards,

Yours sincerely

|||

You can use sys.sp_addextendedproperty to add column description. You could find more detail at BOL, ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/565483ea-875b-4133-b327-d0006d2d7b4c.htm.

EXEC sys.sp_addextendedproperty
@.name=N'Caption'
,@.value=N'This is a table description'
,@.level0type=N'SCHEMA'
,@.level0name=N'dbo'
,@.level1type=N'TABLE'
,@.level1name=N'TableName'
GO
EXEC sys.sp_addextendedproperty
@.name=N'Caption'
,@.value=N'This is a column description'
,@.level0type=N'SCHEMA'
,@.level0name=N'dbo'
,@.level1type=N'TABLE'
,@.level1name=N'TableName'
,@.level2type=N'COLUMN'
,@.level2name=N'Col1'
GO

|||

Hi, Somjai,

Thank you very much for your kind advices.

But I copied the above scripts into the query window, it did not work? Do I need to replay any of the amove information with my specific information? Thanks a lot for your further guidance and advices.

With best regards,

Yours sincerely,

|||

Helen,

Yes, you need to replace with your specific information.

EXEC sys.sp_addextendedproperty
@.name=N'Caption'
,@.value=N'This is a template table' --Your table definition
,@.level0type=N'SCHEMA'
,@.level0name=N'dbo' --Your scheme name (when it is not dbo)
,@.level1type=N'TABLE'
,@.level1name=N'TableName' --Your table name
GO
EXEC sys.sp_addextendedproperty
@.name=N'Caption'
,@.value=N'This is a template column' --Your column definition
,@.level0type=N'SCHEMA'
,@.level0name=N'dbo' --Your scheme name (when it is not dbo)
,@.level1type=N'TABLE'
,@.level1name=N'TableName' --Your table name
,@.level2type=N'COLUMN'
,@.level2name=N'Col1' --Your column name
GO

You will need to repeat second EXEC for all of your columns in this table. I usually create a file for each tables (use table name as file name) and put these statements right after my table creation in the same file. I also put these files in VSS which allow me to version and share with other people.

Regards,

|||

Hi,Somjai, thank you very much for your kind guidance and advices.

But I cant see any advantage by using this system stored procedure to add the descriptions for the columns by repeating so many times for all the columns which jsut result in a caption added to the extened property for the objects.

The above results seems not as easy as directly adding the description for the objects within the object's property dialog.

By the way, what is VSS as you mentioned?

Thanks a lot.

With best regards,

Yours sincerely,

Best practice for data dictionary in SQL Server 2005

Hi ,all here,

Would please anyone here give me any guidance and advices for best practice of data dictionary in SQL Server 2005?

I have restored a large insurance claims database with up to more than 300 tables, massively, most of them are empty tables, many of them dont have any keys, contrains, indexes, and more difficult, there is no any data dictionary for the database which gets me stuck in the understanding of the data at the moment. Thus I think data dictionary is very important for a database.

Will anyone gives me advices for that? Really need help.

Thank you very much in advance for any help.

With best regards,

Yours sincerely,

Hi, all experts,

Any advices?

Thanks a lot.

With best regards,

Yours sincerely,

|||I think the word data dictionary is not quite sure for us, do you mean data dictionary like in Oracle where you can grap the information of the objects stored in the database ?

HTH, Jens Suessmeyer

http://www.sqlserver2005.de|||

Hi,Jens,

Thanks. Yes, what I mean here data dictionary is the description of the database objects from tables to all attributes in tables. I mean when other users other than the database designers are using the database data, without any supportive data dictionary for explanation about what the database and all its fields are about , users definitely have difficulty understanding it to further analyze it.

Hope my information above is clear for my question to be understood.

Thank you very much.

With best regards,

Yours sincerely,

|||The simple collection of the database objects can be found in the INFORMATION_SCHEMA Views. If you want to implement custom description, you can use the extended properties for that. If you have the chance to use Visio are another other data modeling program, I would choose that ín your case.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi, Jens,

Thank you very much for your kind and very helpful guidance and advices.

But the INFORMATION-SCHEMA views are generated automatically by the system? not customized by the database designer, the INFORMATION-SCHEMA VIEWS are just the collection of the data types, scale etc. while not user-defined description of each attibutes like what is this attribute used for, what dose the data in this attribute represent etc.

Is it possible for users to alter the view like INFORMATION_SCHEMA.COLUMNS to add an addtional column used to describe the columns used there by using T-SQL query?

Yours further advices and guidances are most appreciated.

With best regards,

Yours sincerely

|||

You can use sys.sp_addextendedproperty to add column description. You could find more detail at BOL, ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/565483ea-875b-4133-b327-d0006d2d7b4c.htm.

EXEC sys.sp_addextendedproperty
@.name=N'Caption'
,@.value=N'This is a table description'
,@.level0type=N'SCHEMA'
,@.level0name=N'dbo'
,@.level1type=N'TABLE'
,@.level1name=N'TableName'
GO
EXEC sys.sp_addextendedproperty
@.name=N'Caption'
,@.value=N'This is a column description'
,@.level0type=N'SCHEMA'
,@.level0name=N'dbo'
,@.level1type=N'TABLE'
,@.level1name=N'TableName'
,@.level2type=N'COLUMN'
,@.level2name=N'Col1'
GO

|||

Hi, Somjai,

Thank you very much for your kind advices.

But I copied the above scripts into the query window, it did not work? Do I need to replay any of the amove information with my specific information? Thanks a lot for your further guidance and advices.

With best regards,

Yours sincerely,

|||

Helen,

Yes, you need to replace with your specific information.

EXEC sys.sp_addextendedproperty
@.name=N'Caption'
,@.value=N'This is a template table' --Your table definition
,@.level0type=N'SCHEMA'
,@.level0name=N'dbo' --Your scheme name (when it is not dbo)
,@.level1type=N'TABLE'
,@.level1name=N'TableName' --Your table name
GO
EXEC sys.sp_addextendedproperty
@.name=N'Caption'
,@.value=N'This is a template column' --Your column definition
,@.level0type=N'SCHEMA'
,@.level0name=N'dbo' --Your scheme name (when it is not dbo)
,@.level1type=N'TABLE'
,@.level1name=N'TableName' --Your table name
,@.level2type=N'COLUMN'
,@.level2name=N'Col1' --Your column name
GO

You will need to repeat second EXEC for all of your columns in this table. I usually create a file for each tables (use table name as file name) and put these statements right after my table creation in the same file. I also put these files in VSS which allow me to version and share with other people.

Regards,

|||

Hi,Somjai, thank you very much for your kind guidance and advices.

But I cant see any advantage by using this system stored procedure to add the descriptions for the columns by repeating so many times for all the columns which jsut result in a caption added to the extened property for the objects.

The above results seems not as easy as directly adding the description for the objects within the object's property dialog.

By the way, what is VSS as you mentioned?

Thanks a lot.

With best regards,

Yours sincerely,

Best Possible Solution

Hi folks, guidance required!
Clients have an MSDE installed and they need all of their data to be dowloaded for the first time from our website. 30 tables r involved.
Can i automate this? I suspect BCP allows only one table to be exported into a text file. Since it's the first time process i don't want to setup replication.

Howdy!What do you mean downloaded from you website?|||Hi, sir.
An offline scheduler type application that could be downloaded from our website. All the data of the clients is at the db server running our website. Now for the first time when the log on to the app they could be offered the facility to download all their data locally from the db. I suspect there could b a better way around to give the data to each provider in an automated way rather than selecting the data for each client; creating a database for each client and shipping it manually with the software.

Howdy!