Queries in parallel

I have some users running an application against a SQL 2k enterprise
database with 4 CPU's. When multiple users execute the same task in the
application at the same time, it appears from a duration standpoint that the
process is running serially against the database, where user #2's task
finishes after User #1, and User #3 finishes after User #2.
The configuration of the database has the following values:
name minimum maximum config_value
run_value
-- -- -- -- --
--
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 1 1
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 6079 6079
max text repl size (B) 0 2147483647 65536
65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 6079 6079
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
Is there anyway to prove that the queries are running in parallel rather than
serially?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1Robert R via SQLMonster.com wrote:
> I have some users running an application against a SQL 2k enterprise
> database with 4 CPU's. When multiple users execute the same task in
> the application at the same time, it appears from a duration
> standpoint that the process is running serially against the database,
> where user #2's task finishes after User #1, and User #3 finishes
> after User #2.
> <snip>
Sounds like you are having blocking issues. That is, one query is blocking
other queries and because of that they are executing in a serial fashion.
Check the execution plans of the queries in the "task" to make sure they are
properly using indexes and make sure you avoid long running transactions.
Feel free to post the table DDL, indexes, queries in question, and the
execution plans for each if you have any questions. You can check much of
this information from Profiler. Use the SQL:BatchCompleted and RPC:Completed
events for a high-level performance overview. If you want more detail add
SQL:StmtCompleted and SP:StmtCompleted events. Look over the cpu, duration,
and reads columns.
David Gugick
Quest Software|||This is a sample of one of the long running queries. Keep in mind the sample
I have included here is only about a quarter of the entire statement. Care to
explain how this might block other queries so that a newbie like me can
understand?
(
SELECT
ol.KY_OPINION_CD_ID AS KY_OPINION_CD_ID,
min(SKU_NOTE_2) AS SKU_NOTE_2,
min(SKU_NOTE) AS SKU_NOTE,
SKU_01 AS CURR_GROUP_CD,
SKU_01 AS CURR_GROUP_TEXT,
'SKU_01' AS GROUP_ORDER_CD,
COUNT(*) AS NUM_CHILDREN,
'2559946|SKU' AS AGGR_NAME,
2626210 AS AGGR_ID,
Min(cs.KY_SKU_ID) AS KY_SKU_ID,
Min(cs.KY_CUST_ID) as KY_CUST_ID,
min(CUST_CD) AS CUST_CD,
min(SKU_CD) AS SKU_CD,
min(BUS_UNIT_CD) AS BUS_UNIT_CD,
min(CUST_01) as CUST_01,
min(CUST_02) as CUST_02,
min(CUST_03) as CUST_03,
min(CUST_04) as CUST_04,
min(CUST_05) as CUST_05,
min(CUST_06) as CUST_06,
min(CUST_07) as CUST_07,
min(CUST_08) as CUST_08,
min(SKU_01) as SKU_01,
min(SKU_02) as SKU_02,
min(SKU_03) as SKU_03,
min(SKU_04) as SKU_04,
min(SKU_05) as SKU_05,
min(SKU_06) as SKU_06,
min(SKU_07) as SKU_07,
min(SKU_08) as SKU_08,
min(SKU_09) as SKU_09,
min(SKU_10) as SKU_10,
min(SKU_11) as SKU_11,
min(SKU_12) as SKU_12,
min(SKU_13) as SKU_13,
min(SKU_14) as SKU_14,
min(SKU_15) as SKU_15,
min(SKU_16) as SKU_16,
min(SKU_17) as SKU_17,
min(SKU_18) as SKU_18,
min(SKU_19) as SKU_19,
min(SKU_20) as SKU_20,
min(SKU_21) as SKU_21,
min(SKU_22) as SKU_22,
min(SKU_23) as SKU_23,
min(SKU_24) as SKU_24,
min(SKU_25) as SKU_25,
min(SKU_26) as SKU_26,
min(SKU_27) as SKU_27,
min(SKU_28) as SKU_28,
min(SKU_29) as SKU_29,
min(SKU_30) as SKU_30,
min(SKU_31) as SKU_31,
min(SKU_32) as SKU_32,
min(SKU_33) as SKU_33,
min(SKU_34) as SKU_34,
avg(SKU_AMT_01) as SKU_AMT_01,
avg(SKU_AMT_02) as SKU_AMT_02,
avg(SKU_AMT_03) as SKU_AMT_03,
min(SKU_AMT_04) as SKU_AMT_04,
avg(SKU_AMT_05) as SKU_AMT_05,
min(SKU_DATE_01) as SKU_DATE_01,
min(SKU_DATE_02) as SKU_DATE_02,
min(SKU_DATE_03) as SKU_DATE_03,
max(CUST_SKU_01) as CUST_SKU_01,
min(CUST_SKU_02) as CUST_SKU_02,
min(CUST_SKU_03) as CUST_SKU_03,
min(CUST_SKU_04) as CUST_SKU_04,
max(CUST_SKU_05) as CUST_SKU_05,
min(CUST_SKU_06) as CUST_SKU_06,
min(CUST_SKU_07) as CUST_SKU_07,
min(CUST_SKU_08) as CUST_SKU_08,
min(CUST_SKU_09) as CUST_SKU_09,
min(CUST_SKU_10) as CUST_SKU_10,
min(CUST_SKU_11) as CUST_SKU_11,
min(CUST_SKU_12) as CUST_SKU_12,
min(CUST_SKU_13) as CUST_SKU_13,
min(CUST_SKU_14) as CUST_SKU_14,
min(CUST_SKU_15) as CUST_SKU_15,
max(CUST_SKU_16) as CUST_SKU_16,
max(CUST_SKU_17) as CUST_SKU_17,
max(CUST_SKU_18) as CUST_SKU_18,
max(CUST_SKU_19) as CUST_SKU_19,
max(CUST_SKU_20) as CUST_SKU_20,
max(CUST_SKU_21) as CUST_SKU_21,
max(CUST_SKU_22) as CUST_SKU_22,
max(CUST_SKU_23) as CUST_SKU_23,
max(CUST_SKU_24) as CUST_SKU_24,
max(CUST_SKU_25) as CUST_SKU_25,
max(CUST_SKU_26) as CUST_SKU_26,
max(CUST_SKU_27) as CUST_SKU_27,
max(CUST_SKU_30) as CUST_SKU_30,
avg(CUST_SKU_AMT_01) as CUST_SKU_AMT_01,
avg(CUST_SKU_AMT_02) as CUST_SKU_AMT_02,
avg(CUST_SKU_AMT_03) as CUST_SKU_AMT_03,
avg(CUST_SKU_AMT_04) as CUST_SKU_AMT_04,
avg(CUST_SKU_AMT_05) as CUST_SKU_AMT_05,
avg(CUST_SKU_AMT_06) as CUST_SKU_AMT_06,
avg(CUST_SKU_AMT_07) as CUST_SKU_AMT_07,
avg(CUST_SKU_AMT_08) as CUST_SKU_AMT_08,
sum(CUST_SKU_AMT_09) as CUST_SKU_AMT_09,
sum(CUST_SKU_AMT_10) as CUST_SKU_AMT_10,
sum(CUST_SKU_AMT_11) as CUST_SKU_AMT_11,
sum(CUST_SKU_AMT_11*p1.OPINION_QTY_01) as CUST_SKU_AMT_12,
'UOM' AS MIN_UM,
'UOM' AS MAX_UM,
(SELECT COUNT(*) from tblNotes n with(rowlock)
WHERE n.KY_SKU_ID = min(cs.KY_SKU_ID)
and NOTE_TYPE IS NOT NULL) AS HAS_NOTE,
0 AS DIRTY_OP,
SPACE(100) AS UPDATE_FLDS,
(SUM(ol.OPINION_QTY_N12)) as OPINION_QTY_N12,
(SUM(ol.OPINION_QTY_N11)) as OPINION_QTY_N11,
(SUM(ol.OPINION_QTY_N10)) as OPINION_QTY_N10,
(SUM(ol.OPINION_QTY_N09)) as OPINION_QTY_N09,
(SUM(ol.OPINION_QTY_N08)) as OPINION_QTY_N08,
(SUM(ol.OPINION_QTY_N07)) as OPINION_QTY_N07,
(SUM(ol.OPINION_QTY_N06)) as OPINION_QTY_N06,
(SUM(ol.OPINION_QTY_N05)) as OPINION_QTY_N05,
(SUM(ol.OPINION_QTY_N04)) as OPINION_QTY_N04,
(SUM(ol.OPINION_QTY_N03)) as OPINION_QTY_N03,
(SUM(ol.OPINION_QTY_N02)) as OPINION_QTY_N02,
(SUM(ol.OPINION_QTY_N01)) as OPINION_QTY_N01,
(SUM(ol.OPINION_QTY_01)) as OPINION_QTY_01,
(SUM(ol.OPINION_QTY_02)) as OPINION_QTY_02,
(SUM(ol.OPINION_QTY_03)) as OPINION_QTY_03,
(SUM(ol.OPINION_QTY_04)) as OPINION_QTY_04,
(SUM(ol.OPINION_QTY_05)) as OPINION_QTY_05,
(SUM(ol.OPINION_QTY_06)) as OPINION_QTY_06,
(SUM(ol.OPINION_QTY_07)) as OPINION_QTY_07,
(SUM(ol.OPINION_QTY_08)) as OPINION_QTY_08,
(SUM(ol.OPINION_QTY_09)) as OPINION_QTY_09,
(SUM(ol.OPINION_QTY_10)) as OPINION_QTY_10,
(SUM(ol.OPINION_QTY_11)) as OPINION_QTY_11,
(SUM(ol.OPINION_QTY_12)) as OPINION_QTY_12,
(SUM(ol.OPINION_QTY_13)) as OPINION_QTY_13,
(SUM(ol.OPINION_QTY_14)) as OPINION_QTY_14,
(SUM(ol.OPINION_QTY_15)) as OPINION_QTY_15,
(SUM(ol.OPINION_QTY_16)) as OPINION_QTY_16,
(SUM(ol.OPINION_QTY_17)) as OPINION_QTY_17,
(SUM(ol.OPINION_QTY_18)) as OPINION_QTY_18,
(SUM(ol.OPINION_QTY_19)) as OPINION_QTY_19,
(SUM(ol.OPINION_QTY_20)) as OPINION_QTY_20,
(SUM(ol.OPINION_QTY_21)) as OPINION_QTY_21,
(SUM(ol.OPINION_QTY_22)) as OPINION_QTY_22,
(SUM(ol.OPINION_QTY_23)) as OPINION_QTY_23,
(SUM(ol.OPINION_QTY_24)) as OPINION_QTY_24,
(SUM(ol.OPINION_QTY_25)) as OPINION_QTY_25,
(SUM(ol.OPINION_QTY_26)) as OPINION_QTY_26,
(SUM(ol.OPINION_QTY_27)) as OPINION_QTY_27,
(SUM(ol.OPINION_QTY_28)) as OPINION_QTY_28,
(SUM(ol.OPINION_QTY_29)) as OPINION_QTY_29,
(SUM(ol.OPINION_QTY_30)) as OPINION_QTY_30,
(SUM(ol.OPINION_QTY_31)) as OPINION_QTY_31,
(SUM(ol.OPINION_QTY_32)) as OPINION_QTY_32,
(SUM(ol.OPINION_QTY_33)) as OPINION_QTY_33,
(SUM(ol.OPINION_QTY_34)) as OPINION_QTY_34,
(SUM(ol.OPINION_QTY_35)) as OPINION_QTY_35,
(SUM(ol.OPINION_QTY_36)) as OPINION_QTY_36,
(SUM(ol.OPINION_AMT_N24)) AS OPINION_AMT_N24,
(SUM(ol.OPINION_AMT_N23)) AS OPINION_AMT_N23,
(SUM(ol.OPINION_AMT_N22)) AS OPINION_AMT_N22,
(SUM(ol.OPINION_AMT_N21)) AS OPINION_AMT_N21,
(SUM(ol.OPINION_AMT_N20)) AS OPINION_AMT_N20,
(SUM(ol.OPINION_AMT_N19)) AS OPINION_AMT_N19,
(SUM(ol.OPINION_AMT_N18)) AS OPINION_AMT_N18,
(SUM(ol.OPINION_AMT_N17)) AS OPINION_AMT_N17,
(SUM(ol.OPINION_AMT_N16)) AS OPINION_AMT_N16,
(SUM(ol.OPINION_AMT_N15)) AS OPINION_AMT_N15,
(SUM(ol.OPINION_AMT_N14)) AS OPINION_AMT_N14,
(SUM(ol.OPINION_AMT_N13)) AS OPINION_AMT_N13,
(SUM(ol.OPINION_AMT_N12)) AS OPINION_AMT_N12,
(SUM(ol.OPINION_AMT_N11)) AS OPINION_AMT_N11,
(SUM(ol.OPINION_AMT_N10)) AS OPINION_AMT_N10,
(SUM(ol.OPINION_AMT_N09)) AS OPINION_AMT_N09,
(SUM(ol.OPINION_AMT_N08)) AS OPINION_AMT_N08,
(SUM(ol.OPINION_AMT_N07)) AS OPINION_AMT_N07,
(SUM(ol.OPINION_AMT_N06)) AS OPINION_AMT_N06,
(SUM(ol.OPINION_AMT_N05)) AS OPINION_AMT_N05,
(SUM(ol.OPINION_AMT_N04)) AS OPINION_AMT_N04,
(SUM(ol.OPINION_AMT_N03)) AS OPINION_AMT_N03,
(SUM(ol.OPINION_AMT_N02)) AS OPINION_AMT_N02,
(SUM(ol.OPINION_AMT_N01)) AS OPINION_AMT_N01,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_01*p3.OPINION_QTY_01)
else 0 end) AS OPINION_AMT_01,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_02*p3.OPINION_QTY_02)
else 0 end) AS OPINION_AMT_02,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_03*p3.OPINION_QTY_03)
else 0 end) AS OPINION_AMT_03,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_04*p3.OPINION_QTY_04)
else 0 end) AS OPINION_AMT_04,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_05*p3.OPINION_QTY_05)
else 0 end) AS OPINION_AMT_05,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_06*p3.OPINION_QTY_06)
else 0 end) AS OPINION_AMT_06,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_07*p3.OPINION_QTY_07)
else 0 end) AS OPINION_AMT_07,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_08*p3.OPINION_QTY_08)
else 0 end) AS OPINION_AMT_08,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_09*p3.OPINION_QTY_09)
else 0 end) AS OPINION_AMT_09,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_10*p3.OPINION_QTY_10)
else 0 end) AS OPINION_AMT_10,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_11*p3.OPINION_QTY_11)
else 0 end) AS OPINION_AMT_11,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_12*p3.OPINION_QTY_12)
else 0 end) AS OPINION_AMT_12,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_13*p3.OPINION_QTY_13)
else 0 end) AS OPINION_AMT_13,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_14*p3.OPINION_QTY_14)
else 0 end) AS OPINION_AMT_14,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_15*p3.OPINION_QTY_15)
else 0 end) AS OPINION_AMT_15,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_16*p3.OPINION_QTY_16)
else 0 end) AS OPINION_AMT_16,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_17*p3.OPINION_QTY_17)
else 0 end) AS OPINION_AMT_17,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_18*p3.OPINION_QTY_18)
else 0 end) AS OPINION_AMT_18,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_19*p3.OPINION_QTY_19)
else 0 end) AS OPINION_AMT_19,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_20*p3.OPINION_QTY_20)
else 0 end) AS OPINION_AMT_20,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_21*p3.OPINION_QTY_21)
else 0 end) AS OPINION_AMT_21,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_22*p3.OPINION_QTY_22)
else 0 end) AS OPINION_AMT_22,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_23*p3.OPINION_QTY_23)
else 0 end) AS OPINION_AMT_23,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_24*p3.OPINION_QTY_24)
else 0 end) AS OPINION_AMT_24,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_25*p3.OPINION_QTY_25)
else 0 end) AS OPINION_AMT_25,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_26*p3.OPINION_QTY_26)
else 0 end) AS OPINION_AMT_26,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_27*p3.OPINION_QTY_27)
else 0 end) AS OPINION_AMT_27,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_28*p3.OPINION_QTY_28)
else 0 end) AS OPINION_AMT_28,
(case when ol.KY_OPINION_CD_ID not in (10,11,17)
then sum(ol.OPINION_QTY_29*p3.OPINION_QTY_29)
else 0 end) AS OPINION_AMT_29,
case ...
from ...
where ...
David Gugick wrote:
>> I have some users running an application against a SQL 2k enterprise
>> database with 4 CPU's. When multiple users execute the same task in
>[quoted text clipped - 4 lines]
>> <snip>
>Sounds like you are having blocking issues. That is, one query is blocking
>other queries and because of that they are executing in a serial fashion.
>Check the execution plans of the queries in the "task" to make sure they are
>properly using indexes and make sure you avoid long running transactions.
>Feel free to post the table DDL, indexes, queries in question, and the
>execution plans for each if you have any questions. You can check much of
>this information from Profiler. Use the SQL:BatchCompleted and RPC:Completed
>events for a high-level performance overview. If you want more detail add
>SQL:StmtCompleted and SP:StmtCompleted events. Look over the cpu, duration,
>and reads columns.
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||Robert R via SQLMonster.com wrote:
> This is a sample of one of the long running queries. Keep in mind the
> sample I have included here is only about a quarter of the entire
> statement. Care to explain how this might block other queries so that
> a newbie like me can understand?
> <snip>
Where did that SQL statement come from? It looks as though your table is
denormalized. I see a SKU_1 through SKU_34 that appear to be columns in
the table. If that's true, table design issues aside, that's an
extremely large and complex query that could very well be accessing the
entire table each time it's executed and / or causing extremely high IO
load on your disk subsystem and / or causing high CPU. You are also
returning a SPACE(100) as a column which is a little strange.
Denormalized tables cause large row sizes also very low row density
which means a lot of page reads. Excessive page reads mean a lot of
physical IO which is slow. A lot of aggregations means higher CPU
utilization. All of these items can translate in to slow executing
queries, long transaction times, extended locks that cause blocking,
high disk IO, hign CPU, etc.
So, first we need to know if your table really looks like what I see in
the query. If so, the answers here will likely point to the table design
as the primary problem. It would be impossible for anyone here to
recommend a solution for the query itself without knowing exactly what
the tables look like, what indexes are in place, what you're hoping to
return from the query, and a complete query with parameters/bind
variables.
Sorry I can't be more help. THe best thing to do is take a step back and
make a new post with all the relevant information.
David Gugick
Quest Software
www.quest.com

Qs on SQL Server backup using Enterprise Manager

Q1: When using All Tasks\Maintenance Plan to setup a database backup, why
does it have options for differential backup?
Q2: When using All Tasks\Backup Database, Why don't I have option
Remove the files older than
Q3: Does performing a Full database backup through Enterprise manager
automatically remove the inactived portion in the log file (or the portion
has been committed and written to the database)? It is grayed out in both
Full DB backup and Diff Backup. It is only available in Log backup.
Q4: How do you define "inactive entries"?
ThanksFrankie,
First, I suggest you read up on the BACKUP and RESTORE commands in Books Onl
ine. Backup is such an important
topic, and you don't want to be in a position where you need to do restore a
nd realize that your backup
strategy causes you to loose more data than acceptable. Having said that:

> Q1: When using All Tasks\Maintenance Plan to setup a database backup, why
> does it have options for differential backup?
I assume you meant to say "doesn't it have". Maint Wiz is a basic tool. It w
as never designed for more than
the simpler scenarios. It is very difficult to answer "why?" questions. Some
one in the design team at MS made
that decision, quite simply. Probably to make the Maint Plan easier to under
stand for beginners.

> Q2: When using All Tasks\Backup Database, Why don't I have option
> Remove the files older than
Because this is an immediate dialog.

> Q3: Does performing a Full database backup through Enterprise manager
> automatically remove the inactived portion in the log file (or the portion
> has been committed and written to the database)? It is grayed out in bo
th
> Full DB backup and Diff Backup. It is only available in Log backup.
Only transaction log backup empties the transaction log. This is in order fo
r a db backup to not break the log
backup chain sequence.

> Q4: How do you define "inactive entries"?
Transaction log records generated by transactions that are committed, and th
ere is no earlier non-committed
transaction. These are not required for SQL server to perform a possible lat
er rollback for any user.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Frankie" <Frankie@.discussions.microsoft.com> wrote in message
news:9FCB61BB-9A54-4FD9-B948-5229E9F7742E@.microsoft.com...
> Q1: When using All Tasks\Maintenance Plan to setup a database backup, why
> does it have options for differential backup?
> Q2: When using All Tasks\Backup Database, Why don't I have option
> Remove the files older than
> Q3: Does performing a Full database backup through Enterprise manager
> automatically remove the inactived portion in the log file (or the portion
> has been committed and written to the database)? It is grayed out in bo
th
> Full DB backup and Diff Backup. It is only available in Log backup.
> Q4: How do you define "inactive entries"?
> Thanks

Qs on SQL Server backup using Enterprise Manager

Q1: When using All Tasks\Maintenance Plan to setup a database backup, why
does it have options for differential backup?
Q2: When using All Tasks\Backup Database, Why don't I have option
Remove the files older than
Q3: Does performing a Full database backup through Enterprise manager
automatically remove the inactived portion in the log file (or the portion
has been committed and written to the database)? It is grayed out in both
Full DB backup and Diff Backup. It is only available in Log backup.
Q4: How do you define "inactive entries"?
ThanksFrankie,
First, I suggest you read up on the BACKUP and RESTORE commands in Books Online. Backup is such an important
topic, and you don't want to be in a position where you need to do restore and realize that your backup
strategy causes you to loose more data than acceptable. Having said that:
> Q1: When using All Tasks\Maintenance Plan to setup a database backup, why
> does it have options for differential backup?
I assume you meant to say "doesn't it have". Maint Wiz is a basic tool. It was never designed for more than
the simpler scenarios. It is very difficult to answer "why?" questions. Someone in the design team at MS made
that decision, quite simply. Probably to make the Maint Plan easier to understand for beginners.
> Q2: When using All Tasks\Backup Database, Why don't I have option
> Remove the files older than
Because this is an immediate dialog.
> Q3: Does performing a Full database backup through Enterprise manager
> automatically remove the inactived portion in the log file (or the portion
> has been committed and written to the database)? It is grayed out in both
> Full DB backup and Diff Backup. It is only available in Log backup.
Only transaction log backup empties the transaction log. This is in order for a db backup to not break the log
backup chain sequence.
> Q4: How do you define "inactive entries"?
Transaction log records generated by transactions that are committed, and there is no earlier non-committed
transaction. These are not required for SQL server to perform a possible later rollback for any user.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Frankie" <Frankie@.discussions.microsoft.com> wrote in message
news:9FCB61BB-9A54-4FD9-B948-5229E9F7742E@.microsoft.com...
> Q1: When using All Tasks\Maintenance Plan to setup a database backup, why
> does it have options for differential backup?
> Q2: When using All Tasks\Backup Database, Why don't I have option
> Remove the files older than
> Q3: Does performing a Full database backup through Enterprise manager
> automatically remove the inactived portion in the log file (or the portion
> has been committed and written to the database)? It is grayed out in both
> Full DB backup and Diff Backup. It is only available in Log backup.
> Q4: How do you define "inactive entries"?
> Thanks

Qs on SQL Server backup using Enterprise Manager

Q1: When using All Tasks\Maintenance Plan to setup a database backup, why
does it have options for differential backup?
Q2: When using All Tasks\Backup Database, Why don't I have option
Remove the files older than
Q3: Does performing a Full database backup through Enterprise manager
automatically remove the inactived portion in the log file (or the portion
has been committed and written to the database)? It is grayed out in both
Full DB backup and Diff Backup. It is only available in Log backup.
Q4: How do you define "inactive entries"?
Thanks
Frankie,
First, I suggest you read up on the BACKUP and RESTORE commands in Books Online. Backup is such an important
topic, and you don't want to be in a position where you need to do restore and realize that your backup
strategy causes you to loose more data than acceptable. Having said that:

> Q1: When using All Tasks\Maintenance Plan to setup a database backup, why
> does it have options for differential backup?
I assume you meant to say "doesn't it have". Maint Wiz is a basic tool. It was never designed for more than
the simpler scenarios. It is very difficult to answer "why?" questions. Someone in the design team at MS made
that decision, quite simply. Probably to make the Maint Plan easier to understand for beginners.

> Q2: When using All Tasks\Backup Database, Why don't I have option
> Remove the files older than
Because this is an immediate dialog.

> Q3: Does performing a Full database backup through Enterprise manager
> automatically remove the inactived portion in the log file (or the portion
> has been committed and written to the database)? It is grayed out in both
> Full DB backup and Diff Backup. It is only available in Log backup.
Only transaction log backup empties the transaction log. This is in order for a db backup to not break the log
backup chain sequence.

> Q4: How do you define "inactive entries"?
Transaction log records generated by transactions that are committed, and there is no earlier non-committed
transaction. These are not required for SQL server to perform a possible later rollback for any user.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Frankie" <Frankie@.discussions.microsoft.com> wrote in message
news:9FCB61BB-9A54-4FD9-B948-5229E9F7742E@.microsoft.com...
> Q1: When using All Tasks\Maintenance Plan to setup a database backup, why
> does it have options for differential backup?
> Q2: When using All Tasks\Backup Database, Why don't I have option
> Remove the files older than
> Q3: Does performing a Full database backup through Enterprise manager
> automatically remove the inactived portion in the log file (or the portion
> has been committed and written to the database)? It is grayed out in both
> Full DB backup and Diff Backup. It is only available in Log backup.
> Q4: How do you define "inactive entries"?
> Thanks

Q319697

SQL Enterprise Manager Restore to Point in Time Does Not
Stop at Requested Time
and the Database is Left in a Loading State 'Q319697'
Has anybody had problems similar to the one high-lighted
in Q319697.
Earlier this week I tried to Recover a database to a
specific point in time
but the restore went to the end of the file.
I have SP3 and MS03-031 hot fix installed both on my PC
and the server I was restoring to.
The Knowledge base article says that it is fixed in SP3,
has MS03-031 caused this?
After the initial panic I managed to restore the database
using SQL.
GaryI have the same problem that you describe even if I have SP3 installed.. Th
is is not related to MS03-031 hot fix because I don't have installed this on
e

Q319697

SQL Enterprise Manager Restore to Point in Time Does Not
Stop at Requested Time
and the Database is Left in a Loading State 'Q319697'
Has anybody had problems similar to the one high-lighted
in Q319697.
Earlier this week I tried to Recover a database to a
specific point in time
but the restore went to the end of the file.
I have SP3 and MS03-031 hot fix installed both on my PC
and the server I was restoring to.
The Knowledge base article says that it is fixed in SP3,
has MS03-031 caused this?
After the initial panic I managed to restore the database
using SQL.
GaryI have the same problem that you describe even if I have SP3 installed.. This is not related to MS03-031 hot fix because I don't have installed this one

q; Registration access denied

Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
SQL2000 server in SQL Server Management Studio?You can register a SQL 2000 instance in SSMS but not a SQL 2005 instance in
EM. However, the subject of your message indicates an 'access denied'
error, which is different than a version incompatibility. Try to connecting
with SSMS and post the full error message.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:03DDAA79-2041-44F7-8FF2-ABA2BB9D2BCA@.microsoft.com...
> Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
> SQL2000 server in SQL Server Management Studio?|||EM will not be able to register SQL 2005 servers, AND SSMS will be able to
register SQL 2000 servers.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:03DDAA79-2041-44F7-8FF2-ABA2BB9D2BCA@.microsoft.com...
> Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
> SQL2000 server in SQL Server Management Studio?

q; Registration access denied

Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
SQL2000 server in SQL Server Management Studio?You can register a SQL 2000 instance in SSMS but not a SQL 2005 instance in
EM. However, the subject of your message indicates an 'access denied'
error, which is different than a version incompatibility. Try to connecting
with SSMS and post the full error message.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:03DDAA79-2041-44F7-8FF2-ABA2BB9D2BCA@.microsoft.com...
> Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
> SQL2000 server in SQL Server Management Studio?|||EM will not be able to register SQL 2005 servers, AND SSMS will be able to
register SQL 2000 servers.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:03DDAA79-2041-44F7-8FF2-ABA2BB9D2BCA@.microsoft.com...
> Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
> SQL2000 server in SQL Server Management Studio?

q; Registration access denied

Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
SQL2000 server in SQL Server Management Studio?
You can register a SQL 2000 instance in SSMS but not a SQL 2005 instance in
EM. However, the subject of your message indicates an 'access denied'
error, which is different than a version incompatibility. Try to connecting
with SSMS and post the full error message.
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:03DDAA79-2041-44F7-8FF2-ABA2BB9D2BCA@.microsoft.com...
> Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
> SQL2000 server in SQL Server Management Studio?
|||EM will not be able to register SQL 2005 servers, AND SSMS will be able to
register SQL 2000 servers.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:03DDAA79-2041-44F7-8FF2-ABA2BB9D2BCA@.microsoft.com...
> Am I able to register SQL2005 server in Enterprise Manager of SQL2000, or
> SQL2000 server in SQL Server Management Studio?

Q: Standard or Enterprise

Hello,
Can I use my Standard SQL Server and Reporting services and run report
though our companyâ's internet site for everyone? Or does it have to be an
Enterprise version of SQL Server?
Thanks,
Jim.Are you talking internet or intranet. The licensing as far as RS is the same
as SQL Server. I.e. if everyone in your company can access your SQL Server
then they can access your RS. If you have RS on another machine you need an
additional license. There is no difference between Standard Edition and
Enterprise as far as how many can access RS. There is a difference in
functionality and if you don't need the additional functionality then
Standard Edition will work. Enterprise Edition adds web farm capability,
data driven subscriptions, etc. See this link for the differences in the
versions:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx
http://www.microsoft.com/sql/reporting/techinfo/techoverview.mspx
Here is a licensing FAQ:
http://www.microsoft.com/sql/reporting/howtobuy/faq.mspx
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:17707232-9B95-443C-B342-95244D1045A3@.microsoft.com...
> Hello,
> Can I use my Standard SQL Server and Reporting services and run report
> though our company's internet site for everyone? Or does it have to be an
> Enterprise version of SQL Server?
> Thanks,
> Jim.
>|||Hello Bruce,
Thanks you very much for all your help. You helped me a lot now and in the
past.
I am using RS on top of Standard SQL Server in the intranet. We have enough
SQL Server licenses for this. Now I need to deploy some reports to internet
for our partners:
1. Can I do this if I meet licensing requirement for SQL Server without any
technical problem? (I assume I need to buy more licenses, correct?, before
doing that I want to make sure I can use Standard SQL Server and RS for our
Internet, please advice.)
2. We already have our web site accessing to our Standard SQL Server to
fetch data (there is no RS functionality on there at this point). Now; here
is more detail: our Standard SQL Server is in our domain, but Web Server is
actually is not in the domain but it is in its own workgroup. Our current
html internet application accesses this Standard SQL Server through ODBC.
Now, in our local network, my asp.net and RS applications work fine, if I put
my asp.net and RS application to this web sever, will it still access
Standard SQL Server without problem? I am suing standard SqlDataAdapter to
connect to database.
Please advice.
Thanks,
Jim.
"Bruce L-C [MVP]" wrote:
> Are you talking internet or intranet. The licensing as far as RS is the same
> as SQL Server. I.e. if everyone in your company can access your SQL Server
> then they can access your RS. If you have RS on another machine you need an
> additional license. There is no difference between Standard Edition and
> Enterprise as far as how many can access RS. There is a difference in
> functionality and if you don't need the additional functionality then
> Standard Edition will work. Enterprise Edition adds web farm capability,
> data driven subscriptions, etc. See this link for the differences in the
> versions:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx
> http://www.microsoft.com/sql/reporting/techinfo/techoverview.mspx
> Here is a licensing FAQ:
> http://www.microsoft.com/sql/reporting/howtobuy/faq.mspx
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:17707232-9B95-443C-B342-95244D1045A3@.microsoft.com...
> > Hello,
> > Can I use my Standard SQL Server and Reporting services and run report
> > though our company's internet site for everyone? Or does it have to be an
> > Enterprise version of SQL Server?
> > Thanks,
> > Jim.
> >
>
>|||Let me just clarify a few things. Are you using your own front end or are
you using Report Manager? I.e. do you have your own web front end and then
are using either URL integration or web services to integrate with RS? Also,
what type of security are you using?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:C558B489-11AE-45E4-84C8-218A49C5D1B8@.microsoft.com...
> Hello Bruce,
> Thanks you very much for all your help. You helped me a lot now and in the
> past.
> I am using RS on top of Standard SQL Server in the intranet. We have
> enough
> SQL Server licenses for this. Now I need to deploy some reports to
> internet
> for our partners:
> 1. Can I do this if I meet licensing requirement for SQL Server without
> any
> technical problem? (I assume I need to buy more licenses, correct?, before
> doing that I want to make sure I can use Standard SQL Server and RS for
> our
> Internet, please advice.)
> 2. We already have our web site accessing to our Standard SQL Server to
> fetch data (there is no RS functionality on there at this point). Now;
> here
> is more detail: our Standard SQL Server is in our domain, but Web Server
> is
> actually is not in the domain but it is in its own workgroup. Our current
> html internet application accesses this Standard SQL Server through ODBC.
> Now, in our local network, my asp.net and RS applications work fine, if I
> put
> my asp.net and RS application to this web sever, will it still access
> Standard SQL Server without problem? I am suing standard SqlDataAdapter to
> connect to database.
> Please advice.
> Thanks,
> Jim.
>
> "Bruce L-C [MVP]" wrote:
>> Are you talking internet or intranet. The licensing as far as RS is the
>> same
>> as SQL Server. I.e. if everyone in your company can access your SQL
>> Server
>> then they can access your RS. If you have RS on another machine you need
>> an
>> additional license. There is no difference between Standard Edition and
>> Enterprise as far as how many can access RS. There is a difference in
>> functionality and if you don't need the additional functionality then
>> Standard Edition will work. Enterprise Edition adds web farm capability,
>> data driven subscriptions, etc. See this link for the differences in the
>> versions:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx
>> http://www.microsoft.com/sql/reporting/techinfo/techoverview.mspx
>> Here is a licensing FAQ:
>> http://www.microsoft.com/sql/reporting/howtobuy/faq.mspx
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:17707232-9B95-443C-B342-95244D1045A3@.microsoft.com...
>> > Hello,
>> > Can I use my Standard SQL Server and Reporting services and run report
>> > though our company's internet site for everyone? Or does it have to be
>> > an
>> > Enterprise version of SQL Server?
>> > Thanks,
>> > Jim.
>> >
>>|||Hello Bruce,
I embedded ReportViewer into my ASP.Net application and using it as a
component. I am guessing it calls report manager, fetch data and show it
through the viewer. It works in our intranet. I am using integrated windows
authentication in our intranet. Does this answer your question? Please let me
know.
Thanks,
"Bruce L-C [MVP]" wrote:
> Let me just clarify a few things. Are you using your own front end or are
> you using Report Manager? I.e. do you have your own web front end and then
> are using either URL integration or web services to integrate with RS? Also,
> what type of security are you using?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:C558B489-11AE-45E4-84C8-218A49C5D1B8@.microsoft.com...
> > Hello Bruce,
> >
> > Thanks you very much for all your help. You helped me a lot now and in the
> > past.
> >
> > I am using RS on top of Standard SQL Server in the intranet. We have
> > enough
> > SQL Server licenses for this. Now I need to deploy some reports to
> > internet
> > for our partners:
> >
> > 1. Can I do this if I meet licensing requirement for SQL Server without
> > any
> > technical problem? (I assume I need to buy more licenses, correct?, before
> > doing that I want to make sure I can use Standard SQL Server and RS for
> > our
> > Internet, please advice.)
> >
> > 2. We already have our web site accessing to our Standard SQL Server to
> > fetch data (there is no RS functionality on there at this point). Now;
> > here
> > is more detail: our Standard SQL Server is in our domain, but Web Server
> > is
> > actually is not in the domain but it is in its own workgroup. Our current
> > html internet application accesses this Standard SQL Server through ODBC.
> > Now, in our local network, my asp.net and RS applications work fine, if I
> > put
> > my asp.net and RS application to this web sever, will it still access
> > Standard SQL Server without problem? I am suing standard SqlDataAdapter to
> > connect to database.
> > Please advice.
> > Thanks,
> > Jim.
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Are you talking internet or intranet. The licensing as far as RS is the
> >> same
> >> as SQL Server. I.e. if everyone in your company can access your SQL
> >> Server
> >> then they can access your RS. If you have RS on another machine you need
> >> an
> >> additional license. There is no difference between Standard Edition and
> >> Enterprise as far as how many can access RS. There is a difference in
> >> functionality and if you don't need the additional functionality then
> >> Standard Edition will work. Enterprise Edition adds web farm capability,
> >> data driven subscriptions, etc. See this link for the differences in the
> >> versions:
> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx
> >>
> >> http://www.microsoft.com/sql/reporting/techinfo/techoverview.mspx
> >>
> >> Here is a licensing FAQ:
> >> http://www.microsoft.com/sql/reporting/howtobuy/faq.mspx
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:17707232-9B95-443C-B342-95244D1045A3@.microsoft.com...
> >> > Hello,
> >> > Can I use my Standard SQL Server and Reporting services and run report
> >> > though our company's internet site for everyone? Or does it have to be
> >> > an
> >> > Enterprise version of SQL Server?
> >> > Thanks,
> >> > Jim.
> >> >
> >>
> >>
> >>
>
>|||The ReportViewer control uses URL integration. So, whether this is an issue
depends on how your internet access is setup. If you have a DMZ it could be
a problem. URL integration with the report control is really no different
than typing in the URL from the PC over on the internet. What I mean is that
the user has to have access to the RS machine. If your web server is on one
side of the DMZ and the RS is on the other, the user on the Internet will
not have access to it.
Another issue is security. How is the user on the internet going to be
validated. This is from Books On Line:
>>>>>>>>
Securing Reports for Global Access
Report Manager and HTML Viewer are not intended for extranet or Internet
deployment scenarios. To support external users of a report server
effectively, consider building a custom Web application. The application you
provide should be installed on the same computer as report server. You
should also implement Secure Sockets Layer (SSL) to encrypt transmitted
data.
Although custom authentication models are not currently supported, you can
use Windows authentication to support report server access to users that are
external to your organization. The following list provides guidelines about
supporting this scenario:
a.. Create a low-privileged domain user account with read-only
permissions. The account must have access to the computer hosting the report
server.
b.. Create role assignments that map the user account to specific items in
the report server folder hierarchy. You can limit access to read-only
operations by choosing the Browser predefined role for the role assignment.
c.. Configure data source connections to use Windows NT Integrated
Security if you want to access a data source using the security context of
the user. Alternatively, you can use stored credentials that specify a
different account. This approach is useful if you want to query the external
data source using an account that is different from the account that allows
access to the report server. For more information about these options, see
Specifying Credential and Connection Information.
If you have a small number of external users to support, you can consider
using Microsoft Passport. Passport accounts can be mapped to Windows
accounts. Mapping accounts is a manual process that must be performed by a
system administrator, so this approach is not practical if you want to
provide access to a large number of users.
>>>>>>>>>>
The other option (which I have never implemented so I am not an expert on
it) is to provide your own authentication instead of using Windows accounts.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A832A82A-F212-4696-95F3-7A460C1CF618@.microsoft.com...
> Hello Bruce,
> I embedded ReportViewer into my ASP.Net application and using it as a
> component. I am guessing it calls report manager, fetch data and show it
> through the viewer. It works in our intranet. I am using integrated
> windows
> authentication in our intranet. Does this answer your question? Please let
> me
> know.
> Thanks,
> "Bruce L-C [MVP]" wrote:
>> Let me just clarify a few things. Are you using your own front end or are
>> you using Report Manager? I.e. do you have your own web front end and
>> then
>> are using either URL integration or web services to integrate with RS?
>> Also,
>> what type of security are you using?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:C558B489-11AE-45E4-84C8-218A49C5D1B8@.microsoft.com...
>> > Hello Bruce,
>> >
>> > Thanks you very much for all your help. You helped me a lot now and in
>> > the
>> > past.
>> >
>> > I am using RS on top of Standard SQL Server in the intranet. We have
>> > enough
>> > SQL Server licenses for this. Now I need to deploy some reports to
>> > internet
>> > for our partners:
>> >
>> > 1. Can I do this if I meet licensing requirement for SQL Server without
>> > any
>> > technical problem? (I assume I need to buy more licenses, correct?,
>> > before
>> > doing that I want to make sure I can use Standard SQL Server and RS for
>> > our
>> > Internet, please advice.)
>> >
>> > 2. We already have our web site accessing to our Standard SQL Server to
>> > fetch data (there is no RS functionality on there at this point). Now;
>> > here
>> > is more detail: our Standard SQL Server is in our domain, but Web
>> > Server
>> > is
>> > actually is not in the domain but it is in its own workgroup. Our
>> > current
>> > html internet application accesses this Standard SQL Server through
>> > ODBC.
>> > Now, in our local network, my asp.net and RS applications work fine, if
>> > I
>> > put
>> > my asp.net and RS application to this web sever, will it still access
>> > Standard SQL Server without problem? I am suing standard SqlDataAdapter
>> > to
>> > connect to database.
>> > Please advice.
>> > Thanks,
>> > Jim.
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Are you talking internet or intranet. The licensing as far as RS is
>> >> the
>> >> same
>> >> as SQL Server. I.e. if everyone in your company can access your SQL
>> >> Server
>> >> then they can access your RS. If you have RS on another machine you
>> >> need
>> >> an
>> >> additional license. There is no difference between Standard Edition
>> >> and
>> >> Enterprise as far as how many can access RS. There is a difference in
>> >> functionality and if you don't need the additional functionality then
>> >> Standard Edition will work. Enterprise Edition adds web farm
>> >> capability,
>> >> data driven subscriptions, etc. See this link for the differences in
>> >> the
>> >> versions:
>> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx
>> >>
>> >> http://www.microsoft.com/sql/reporting/techinfo/techoverview.mspx
>> >>
>> >> Here is a licensing FAQ:
>> >> http://www.microsoft.com/sql/reporting/howtobuy/faq.mspx
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> >> news:17707232-9B95-443C-B342-95244D1045A3@.microsoft.com...
>> >> > Hello,
>> >> > Can I use my Standard SQL Server and Reporting services and run
>> >> > report
>> >> > though our company's internet site for everyone? Or does it have to
>> >> > be
>> >> > an
>> >> > Enterprise version of SQL Server?
>> >> > Thanks,
>> >> > Jim.
>> >> >
>> >>
>> >>
>> >>
>>|||Hi Bruce,
Thanks for your responses. I will be implementing your security steps, if I
install RS to DMZ machine, and point it to the database in our domain (so DB
is in the network and application and RS is in the DMZ), will that work?
Thanks,
"Bruce L-C [MVP]" wrote:
> The ReportViewer control uses URL integration. So, whether this is an issue
> depends on how your internet access is setup. If you have a DMZ it could be
> a problem. URL integration with the report control is really no different
> than typing in the URL from the PC over on the internet. What I mean is that
> the user has to have access to the RS machine. If your web server is on one
> side of the DMZ and the RS is on the other, the user on the Internet will
> not have access to it.
> Another issue is security. How is the user on the internet going to be
> validated. This is from Books On Line:
> >>>>>>>>
> Securing Reports for Global Access
> Report Manager and HTML Viewer are not intended for extranet or Internet
> deployment scenarios. To support external users of a report server
> effectively, consider building a custom Web application. The application you
> provide should be installed on the same computer as report server. You
> should also implement Secure Sockets Layer (SSL) to encrypt transmitted
> data.
> Although custom authentication models are not currently supported, you can
> use Windows authentication to support report server access to users that are
> external to your organization. The following list provides guidelines about
> supporting this scenario:
> a.. Create a low-privileged domain user account with read-only
> permissions. The account must have access to the computer hosting the report
> server.
> b.. Create role assignments that map the user account to specific items in
> the report server folder hierarchy. You can limit access to read-only
> operations by choosing the Browser predefined role for the role assignment.
> c.. Configure data source connections to use Windows NT Integrated
> Security if you want to access a data source using the security context of
> the user. Alternatively, you can use stored credentials that specify a
> different account. This approach is useful if you want to query the external
> data source using an account that is different from the account that allows
> access to the report server. For more information about these options, see
> Specifying Credential and Connection Information.
> If you have a small number of external users to support, you can consider
> using Microsoft Passport. Passport accounts can be mapped to Windows
> accounts. Mapping accounts is a manual process that must be performed by a
> system administrator, so this approach is not practical if you want to
> provide access to a large number of users.
> >>>>>>>>>>
> The other option (which I have never implemented so I am not an expert on
> it) is to provide your own authentication instead of using Windows accounts.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:A832A82A-F212-4696-95F3-7A460C1CF618@.microsoft.com...
> > Hello Bruce,
> > I embedded ReportViewer into my ASP.Net application and using it as a
> > component. I am guessing it calls report manager, fetch data and show it
> > through the viewer. It works in our intranet. I am using integrated
> > windows
> > authentication in our intranet. Does this answer your question? Please let
> > me
> > know.
> > Thanks,
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Let me just clarify a few things. Are you using your own front end or are
> >> you using Report Manager? I.e. do you have your own web front end and
> >> then
> >> are using either URL integration or web services to integrate with RS?
> >> Also,
> >> what type of security are you using?
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:C558B489-11AE-45E4-84C8-218A49C5D1B8@.microsoft.com...
> >> > Hello Bruce,
> >> >
> >> > Thanks you very much for all your help. You helped me a lot now and in
> >> > the
> >> > past.
> >> >
> >> > I am using RS on top of Standard SQL Server in the intranet. We have
> >> > enough
> >> > SQL Server licenses for this. Now I need to deploy some reports to
> >> > internet
> >> > for our partners:
> >> >
> >> > 1. Can I do this if I meet licensing requirement for SQL Server without
> >> > any
> >> > technical problem? (I assume I need to buy more licenses, correct?,
> >> > before
> >> > doing that I want to make sure I can use Standard SQL Server and RS for
> >> > our
> >> > Internet, please advice.)
> >> >
> >> > 2. We already have our web site accessing to our Standard SQL Server to
> >> > fetch data (there is no RS functionality on there at this point). Now;
> >> > here
> >> > is more detail: our Standard SQL Server is in our domain, but Web
> >> > Server
> >> > is
> >> > actually is not in the domain but it is in its own workgroup. Our
> >> > current
> >> > html internet application accesses this Standard SQL Server through
> >> > ODBC.
> >> > Now, in our local network, my asp.net and RS applications work fine, if
> >> > I
> >> > put
> >> > my asp.net and RS application to this web sever, will it still access
> >> > Standard SQL Server without problem? I am suing standard SqlDataAdapter
> >> > to
> >> > connect to database.
> >> > Please advice.
> >> > Thanks,
> >> > Jim.
> >> >
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> Are you talking internet or intranet. The licensing as far as RS is
> >> >> the
> >> >> same
> >> >> as SQL Server. I.e. if everyone in your company can access your SQL
> >> >> Server
> >> >> then they can access your RS. If you have RS on another machine you
> >> >> need
> >> >> an
> >> >> additional license. There is no difference between Standard Edition
> >> >> and
> >> >> Enterprise as far as how many can access RS. There is a difference in
> >> >> functionality and if you don't need the additional functionality then
> >> >> Standard Edition will work. Enterprise Edition adds web farm
> >> >> capability,
> >> >> data driven subscriptions, etc. See this link for the differences in
> >> >> the
> >> >> versions:
> >> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx
> >> >>
> >> >> http://www.microsoft.com/sql/reporting/techinfo/techoverview.mspx
> >> >>
> >> >> Here is a licensing FAQ:
> >> >> http://www.microsoft.com/sql/reporting/howtobuy/faq.mspx
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> >> news:17707232-9B95-443C-B342-95244D1045A3@.microsoft.com...
> >> >> > Hello,
> >> >> > Can I use my Standard SQL Server and Reporting services and run
> >> >> > report
> >> >> > though our company's internet site for everyone? Or does it have to
> >> >> > be
> >> >> > an
> >> >> > Enterprise version of SQL Server?
> >> >> > Thanks,
> >> >> > Jim.
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||That will take care of the issue with URL integration. It does not deal with
the issue of accounts. If you can give your business users a domain account
then you will be in good shape. I do not operate this way so take it with a
grain of salt. What I have done is log into the intranet via VPN. When this
occurs and you connect to RS it does not know who you are (with my VPN
connection from home, I am not logged onto a domain). When I do this I then
get prompted for a username and password. I haven't done this for awhile so
I don't know if it prompts for the domain or if I have to put in the
username like this: MyDomainName\MyUsername
Note that putting your RS in the DMZ (which means you have split RS and the
Database) requires another license. Plus, when SQL Server is being used on
the internet that affects licensing.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:D961D195-A824-44C8-9BC7-88126739FFCF@.microsoft.com...
> Hi Bruce,
> Thanks for your responses. I will be implementing your security steps, if
> I
> install RS to DMZ machine, and point it to the database in our domain (so
> DB
> is in the network and application and RS is in the DMZ), will that work?
> Thanks,
>
> "Bruce L-C [MVP]" wrote:
>> The ReportViewer control uses URL integration. So, whether this is an
>> issue
>> depends on how your internet access is setup. If you have a DMZ it could
>> be
>> a problem. URL integration with the report control is really no different
>> than typing in the URL from the PC over on the internet. What I mean is
>> that
>> the user has to have access to the RS machine. If your web server is on
>> one
>> side of the DMZ and the RS is on the other, the user on the Internet will
>> not have access to it.
>> Another issue is security. How is the user on the internet going to be
>> validated. This is from Books On Line:
>> >>>>>>>>
>> Securing Reports for Global Access
>> Report Manager and HTML Viewer are not intended for extranet or Internet
>> deployment scenarios. To support external users of a report server
>> effectively, consider building a custom Web application. The application
>> you
>> provide should be installed on the same computer as report server. You
>> should also implement Secure Sockets Layer (SSL) to encrypt transmitted
>> data.
>> Although custom authentication models are not currently supported, you
>> can
>> use Windows authentication to support report server access to users that
>> are
>> external to your organization. The following list provides guidelines
>> about
>> supporting this scenario:
>> a.. Create a low-privileged domain user account with read-only
>> permissions. The account must have access to the computer hosting the
>> report
>> server.
>> b.. Create role assignments that map the user account to specific items
>> in
>> the report server folder hierarchy. You can limit access to read-only
>> operations by choosing the Browser predefined role for the role
>> assignment.
>> c.. Configure data source connections to use Windows NT Integrated
>> Security if you want to access a data source using the security context
>> of
>> the user. Alternatively, you can use stored credentials that specify a
>> different account. This approach is useful if you want to query the
>> external
>> data source using an account that is different from the account that
>> allows
>> access to the report server. For more information about these options,
>> see
>> Specifying Credential and Connection Information.
>> If you have a small number of external users to support, you can consider
>> using Microsoft Passport. Passport accounts can be mapped to Windows
>> accounts. Mapping accounts is a manual process that must be performed by
>> a
>> system administrator, so this approach is not practical if you want to
>> provide access to a large number of users.
>> >>>>>>>>>>
>> The other option (which I have never implemented so I am not an expert on
>> it) is to provide your own authentication instead of using Windows
>> accounts.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:A832A82A-F212-4696-95F3-7A460C1CF618@.microsoft.com...
>> > Hello Bruce,
>> > I embedded ReportViewer into my ASP.Net application and using it as a
>> > component. I am guessing it calls report manager, fetch data and show
>> > it
>> > through the viewer. It works in our intranet. I am using integrated
>> > windows
>> > authentication in our intranet. Does this answer your question? Please
>> > let
>> > me
>> > know.
>> > Thanks,
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Let me just clarify a few things. Are you using your own front end or
>> >> are
>> >> you using Report Manager? I.e. do you have your own web front end and
>> >> then
>> >> are using either URL integration or web services to integrate with RS?
>> >> Also,
>> >> what type of security are you using?
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> >> news:C558B489-11AE-45E4-84C8-218A49C5D1B8@.microsoft.com...
>> >> > Hello Bruce,
>> >> >
>> >> > Thanks you very much for all your help. You helped me a lot now and
>> >> > in
>> >> > the
>> >> > past.
>> >> >
>> >> > I am using RS on top of Standard SQL Server in the intranet. We have
>> >> > enough
>> >> > SQL Server licenses for this. Now I need to deploy some reports to
>> >> > internet
>> >> > for our partners:
>> >> >
>> >> > 1. Can I do this if I meet licensing requirement for SQL Server
>> >> > without
>> >> > any
>> >> > technical problem? (I assume I need to buy more licenses, correct?,
>> >> > before
>> >> > doing that I want to make sure I can use Standard SQL Server and RS
>> >> > for
>> >> > our
>> >> > Internet, please advice.)
>> >> >
>> >> > 2. We already have our web site accessing to our Standard SQL Server
>> >> > to
>> >> > fetch data (there is no RS functionality on there at this point).
>> >> > Now;
>> >> > here
>> >> > is more detail: our Standard SQL Server is in our domain, but Web
>> >> > Server
>> >> > is
>> >> > actually is not in the domain but it is in its own workgroup. Our
>> >> > current
>> >> > html internet application accesses this Standard SQL Server through
>> >> > ODBC.
>> >> > Now, in our local network, my asp.net and RS applications work fine,
>> >> > if
>> >> > I
>> >> > put
>> >> > my asp.net and RS application to this web sever, will it still
>> >> > access
>> >> > Standard SQL Server without problem? I am suing standard
>> >> > SqlDataAdapter
>> >> > to
>> >> > connect to database.
>> >> > Please advice.
>> >> > Thanks,
>> >> > Jim.
>> >> >
>> >> >
>> >> > "Bruce L-C [MVP]" wrote:
>> >> >
>> >> >> Are you talking internet or intranet. The licensing as far as RS is
>> >> >> the
>> >> >> same
>> >> >> as SQL Server. I.e. if everyone in your company can access your SQL
>> >> >> Server
>> >> >> then they can access your RS. If you have RS on another machine you
>> >> >> need
>> >> >> an
>> >> >> additional license. There is no difference between Standard Edition
>> >> >> and
>> >> >> Enterprise as far as how many can access RS. There is a difference
>> >> >> in
>> >> >> functionality and if you don't need the additional functionality
>> >> >> then
>> >> >> Standard Edition will work. Enterprise Edition adds web farm
>> >> >> capability,
>> >> >> data driven subscriptions, etc. See this link for the differences
>> >> >> in
>> >> >> the
>> >> >> versions:
>> >> >> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx
>> >> >>
>> >> >> http://www.microsoft.com/sql/reporting/techinfo/techoverview.mspx
>> >> >>
>> >> >> Here is a licensing FAQ:
>> >> >> http://www.microsoft.com/sql/reporting/howtobuy/faq.mspx
>> >> >>
>> >> >> --
>> >> >> Bruce Loehle-Conger
>> >> >> MVP SQL Server Reporting Services
>> >> >>
>> >> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> >> >> news:17707232-9B95-443C-B342-95244D1045A3@.microsoft.com...
>> >> >> > Hello,
>> >> >> > Can I use my Standard SQL Server and Reporting services and run
>> >> >> > report
>> >> >> > though our company's internet site for everyone? Or does it have
>> >> >> > to
>> >> >> > be
>> >> >> > an
>> >> >> > Enterprise version of SQL Server?
>> >> >> > Thanks,
>> >> >> > Jim.
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>