how to determine who changed user permissions?

I have a problem administering my sql server security, I suspect there are
several users with excessive permissions and they themselves are granting and
modifying permissions for other users, the problem is that I am not running
in C2 Audit mode, is there any other way to check which users have modified
permissions for other users in, lets say the last month or so?i-DLT wrote:
> I have a problem administering my sql server security, I suspect
> there are several users with excessive permissions and they
> themselves are granting and modifying permissions for other users,
> the problem is that I am not running in C2 Audit mode, is there any
> other way to check which users have modified permissions for other
> users in, lets say the last month or so?
Better to check which users have rights that they should not have and
remove those rights. You might check the fixed administrator role (and
other fixed server roles) and see who is a member. You could also use
sp_helprotect to see who granted permissions to various objects.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

how to determine who changed user permissions?

I have a problem administering my sql server security, I suspect there are
several users with excessive permissions and they themselves are granting an
d
modifying permissions for other users, the problem is that I am not running
in C2 Audit mode, is there any other way to check which users have modified
permissions for other users in, lets say the last month or so?i-DLT wrote:
> I have a problem administering my sql server security, I suspect
> there are several users with excessive permissions and they
> themselves are granting and modifying permissions for other users,
> the problem is that I am not running in C2 Audit mode, is there any
> other way to check which users have modified permissions for other
> users in, lets say the last month or so?
Better to check which users have rights that they should not have and
remove those rights. You might check the fixed administrator role (and
other fixed server roles) and see who is a member. You could also use
sp_helprotect to see who granted permissions to various objects.
David Gugick
Quest Software
www.imceda.com
www.quest.comsql

how to determine who changed user permissions?

I have a problem administering my sql server security, I suspect there are
several users with excessive permissions and they themselves are granting and
modifying permissions for other users, the problem is that I am not running
in C2 Audit mode, is there any other way to check which users have modified
permissions for other users in, lets say the last month or so?
i-DLT wrote:
> I have a problem administering my sql server security, I suspect
> there are several users with excessive permissions and they
> themselves are granting and modifying permissions for other users,
> the problem is that I am not running in C2 Audit mode, is there any
> other way to check which users have modified permissions for other
> users in, lets say the last month or so?
Better to check which users have rights that they should not have and
remove those rights. You might check the fixed administrator role (and
other fixed server roles) and see who is a member. You could also use
sp_helprotect to see who granted permissions to various objects.
David Gugick
Quest Software
www.imceda.com
www.quest.com

how to determine which user changed permission?

I have a problem administering my sql server security, I suspect there are
several users with excessive permissions and they themselves are granting an
d
modifying permissions for other users, the problem is that I am not running
in C2 Audit mode, is there any other way to check which users have modified
permissions for other users in, lets say the last month or so?Hi
Unless you are running full transaction logging, and have the logs backed
up, you can't use any tools like LogPi www.lop.pi and Log Explorer
http://www.lumigent.com/products/le_sql.html to view what happened.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"i-DLT" <iDLT@.discussions.microsoft.com> wrote in message
news:11DF54D1-127C-4053-8EA4-3FE665BF4B0D@.microsoft.com...
>I have a problem administering my sql server security, I suspect there are
> several users with excessive permissions and they themselves are granting
> and
> modifying permissions for other users, the problem is that I am not
> running
> in C2 Audit mode, is there any other way to check which users have
> modified
> permissions for other users in, lets say the last month or so?
>|||Thx I downloaded LogPI Demo and will try to pinpoint where the security leak
is coming from. I also will turn on C2 Auditing on all of my instances.
Thx Mike
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Unless you are running full transaction logging, and have the logs backed
> up, you can't use any tools like LogPi www.lop.pi and Log Explorer
> http://www.lumigent.com/products/le_sql.html to view what happened.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "i-DLT" <iDLT@.discussions.microsoft.com> wrote in message
> news:11DF54D1-127C-4053-8EA4-3FE665BF4B0D@.microsoft.com...
>
>|||Hi,
If you enable the C2 Auditing please monitor your hard drive very closely.
Since C2 Audit will audit all actions in backend and wil cause
the trace file to grow heavily. THis will eat your hard drive and can even
cause downtime if you direct the trace file
to either data drive or system drive. So it is always good to allways direct
the trace to a un-used drive.
Thanks
Hari
SQL Server MVP
"i-DLT" <iDLT@.discussions.microsoft.com> wrote in message
news:C5DE8B75-5C3F-4DE3-87CC-9EEAA3786FCE@.microsoft.com...[vbcol=seagreen]
> Thx I downloaded LogPI Demo and will try to pinpoint where the security
> leak
> is coming from. I also will turn on C2 Auditing on all of my instances.
> Thx Mike
> "Mike Epprecht (SQL MVP)" wrote:
>

How to determine what the row size is.

I have a question. A bit new to SQL Server, but how does one
determine the row size of a given table. Here is my issue:
I am a CRM user and I understand that CRM uses SQL Server for its database.
When using SQL Server on its own, it will only allow ~8k of DATA to enter
the database and will truncate the rest (from my understanding anyway).
However, CRM has set its own restrictions on the database and says that "We
will not allow
any data that will exceed SQL Server's limits of ~8k and therefore we will
calculate
the size of the fields in the table and determine if anymore columns will be
permitted to be added. For example, if I wish to add a field to CRM as text
and set it to have a limit of 6K, then CRM will only allow me to add a numbe
r
of fields adding up to 2K. The thing is also is that CRM will not allow you
to DELETE or reconfigure any columns, therefore, if I said to make the new
field to be 1K, it will not allow me. Basically CRM breaks at this point an
d
I can no longer add ANYTHING to this table, but I need to.
Bearing this in mind, I wish to check and see what the table row size is
before hand so I can allocate certain fields more appropriately before addin
g
them to know the size of each field. Is there an store procedure to get
these values from SQL Server, so I can know if I am on the brink of BREAKING
CRM?
Thanks for any help.
Regards,
KeenerHi
You can create a row that is greater than than 8060 characters, but you will
get an error when you insert data greater than that value. For example
(formatting may get messed up!):
create table mytab ( col1 varchar(8000), col2 varchar(8000) )
-- Warning: The table 'mytab' has been created but its maximum row size
(16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 16013 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
-- There is a certain amount of overhead!!!!
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 8073 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
To get column information try sp_help
sp_help Mytab
/*
Name
Owner
Type Created_datetime
----
----
----
----
--
---
mytab
dbo
user table 2005-05-31
18:23:48.873
Column_name
Type
Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
----
----
----
----
-- -- -- --
-- --
--
----
----
col1
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
col2
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
Identity
Seed
Increment Not For Replication
----
----
---
--- --
No identity column defined.
NULL
NULL NULL
RowGuidCol
----
----
No rowguidcol column defined.
Data_located_on_filegroup
----
----
PRIMARY
The object does not have any indexes.
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
*/
HTH
John
"Keener" wrote:

> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its database
.
> When using SQL Server on its own, it will only allow ~8k of DATA to enter
> the database and will truncate the rest (from my understanding anyway).
> However, CRM has set its own restrictions on the database and says that "W
e
> will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we will
> calculate
> the size of the fields in the table and determine if anymore columns will
be
> permitted to be added. For example, if I wish to add a field to CRM as te
xt
> and set it to have a limit of 6K, then CRM will only allow me to add a num
ber
> of fields adding up to 2K. The thing is also is that CRM will not allow y
ou
> to DELETE or reconfigure any columns, therefore, if I said to make the new
> field to be 1K, it will not allow me. Basically CRM breaks at this point
and
> I can no longer add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size is
> before hand so I can allocate certain fields more appropriately before add
ing
> them to know the size of each field. Is there an store procedure to get
> these values from SQL Server, so I can know if I am on the brink of BREAKI
NG
> CRM?
> Thanks for any help.
> Regards,
> Keener|||Keener wrote:
> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its
> database.
> When using SQL Server on its own, it will only allow ~8k of DATA to
> enter the database and will truncate the rest (from my understanding
> anyway). However, CRM has set its own restrictions on the database
> and says that "We will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we
> will calculate
> the size of the fields in the table and determine if anymore columns
> will be permitted to be added. For example, if I wish to add a field
> to CRM as text and set it to have a limit of 6K, then CRM will only
> allow me to add a number of fields adding up to 2K. The thing is
> also is that CRM will not allow you to DELETE or reconfigure any
> columns, therefore, if I said to make the new field to be 1K, it will
> not allow me. Basically CRM breaks at this point and I can no longer
> add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size
> is before hand so I can allocate certain fields more appropriately
> before adding them to know the size of each field. Is there an store
> procedure to get these values from SQL Server, so I can know if I am
> on the brink of BREAKING CRM?
> Thanks for any help.
> Regards,
> Keener
You do not have to deal with the max row size (to a degree) if you use
TEXT/NTEXT/IMAGE data types. With those column data types, only a
16-byte pointer is stored in the row. If you are still worried a table
may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
somewhat accurate, measure of row size.
For example:
Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
for a more accurate measure that avoid varchar/nvarchar/varbinary
calculation issues, see "Estimating the Size of a Table" in BOL.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||John -- YOU DA MAN... DA MVP MAN!!!!!
I have to do a bit of math, but wow, that was what
I needed.
Thanks a bunch,
Keener
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You can create a row that is greater than than 8060 characters, but you wi
ll
> get an error when you insert data greater than that value. For example
> (formatting may get messed up!):
> create table mytab ( col1 varchar(8000), col2 varchar(8000) )
> -- Warning: The table 'mytab' has been created but its maximum row size
> (16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDA
TE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 16013 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> -- There is a certain amount of overhead!!!!
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 8073 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
> To get column information try sp_help
> sp_help Mytab
> /*
> Name
> Owner
> Type Created_datetime
> ----
---
> ----
---
> --
> ---
> mytab
> dbo
> user table 2005-05-31
> 18:23:48.873
>
> Column_name
> Type
> Computed Length P
rec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
>
> ----
---
> ----
---
> -- -- -- --
> -- --
> --
> ----
---
> col1
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
> col2
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
>
> Identity
> Seed
> Increment Not For Replicatio
n
> ----
---
> ---
> --- --
> No identity column defined.
> NULL
> NULL NULL
>
> RowGuidCol
> ----
---
> No rowguidcol column defined.
>
> Data_located_on_filegroup
> ----
---
> PRIMARY
>
> The object does not have any indexes.
> No constraints have been defined for this object.
> No foreign keys reference this table.
> No views with schema binding reference this table.
> */
> HTH
> John
> "Keener" wrote:
>|||Thanks for the reply David.
I understand what you mean, but I need to know how
much the table is allocated for each row, not the actual size
of the data in the rows. I will use your method if/when I run
into the issues of managing data row sizes.
Thanks again,
Keener
"David Gugick" wrote:

> Keener wrote:
> You do not have to deal with the max row size (to a degree) if you use
> TEXT/NTEXT/IMAGE data types. With those column data types, only a
> 16-byte pointer is stored in the row. If you are still worried a table
> may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
> somewhat accurate, measure of row size.
> For example:
> Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
> for a more accurate measure that avoid varchar/nvarchar/varbinary
> calculation issues, see "Estimating the Size of a Table" in BOL.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>sql

How to determine what the row size is.

I have a question. A bit new to SQL Server, but how does one
determine the row size of a given table. Here is my issue:
I am a CRM user and I understand that CRM uses SQL Server for its database.
When using SQL Server on its own, it will only allow ~8k of DATA to enter
the database and will truncate the rest (from my understanding anyway).
However, CRM has set its own restrictions on the database and says that "We
will not allow
any data that will exceed SQL Server's limits of ~8k and therefore we will
calculate
the size of the fields in the table and determine if anymore columns will be
permitted to be added. For example, if I wish to add a field to CRM as text
and set it to have a limit of 6K, then CRM will only allow me to add a number
of fields adding up to 2K. The thing is also is that CRM will not allow you
to DELETE or reconfigure any columns, therefore, if I said to make the new
field to be 1K, it will not allow me. Basically CRM breaks at this point and
I can no longer add ANYTHING to this table, but I need to.
Bearing this in mind, I wish to check and see what the table row size is
before hand so I can allocate certain fields more appropriately before adding
them to know the size of each field. Is there an store procedure to get
these values from SQL Server, so I can know if I am on the brink of BREAKING
CRM?
Thanks for any help.
Regards,
KeenerHi
You can create a row that is greater than than 8060 characters, but you will
get an error when you insert data greater than that value. For example
(formatting may get messed up!):
create table mytab ( col1 varchar(8000), col2 varchar(8000) )
-- Warning: The table 'mytab' has been created but its maximum row size
(16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 16013 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
-- There is a certain amount of overhead!!!!
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 8073 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
To get column information try sp_help
sp_help Mytab
/*
Name
Owner
Type Created_datetime
------
------
--
---
mytab
dbo
user table 2005-05-31
18:23:48.873
Column_name
Type
Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
------
------
-- -- -- --
-- --
--
------
col1
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
col2
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
Identity
Seed
Increment Not For Replication
------
---
--- --
No identity column defined.
NULL
NULL NULL
RowGuidCol
------
No rowguidcol column defined.
Data_located_on_filegroup
------
PRIMARY
The object does not have any indexes.
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
*/
HTH
John
"Keener" wrote:
> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its database.
> When using SQL Server on its own, it will only allow ~8k of DATA to enter
> the database and will truncate the rest (from my understanding anyway).
> However, CRM has set its own restrictions on the database and says that "We
> will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we will
> calculate
> the size of the fields in the table and determine if anymore columns will be
> permitted to be added. For example, if I wish to add a field to CRM as text
> and set it to have a limit of 6K, then CRM will only allow me to add a number
> of fields adding up to 2K. The thing is also is that CRM will not allow you
> to DELETE or reconfigure any columns, therefore, if I said to make the new
> field to be 1K, it will not allow me. Basically CRM breaks at this point and
> I can no longer add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size is
> before hand so I can allocate certain fields more appropriately before adding
> them to know the size of each field. Is there an store procedure to get
> these values from SQL Server, so I can know if I am on the brink of BREAKING
> CRM?
> Thanks for any help.
> Regards,
> Keener|||Keener wrote:
> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its
> database.
> When using SQL Server on its own, it will only allow ~8k of DATA to
> enter the database and will truncate the rest (from my understanding
> anyway). However, CRM has set its own restrictions on the database
> and says that "We will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we
> will calculate
> the size of the fields in the table and determine if anymore columns
> will be permitted to be added. For example, if I wish to add a field
> to CRM as text and set it to have a limit of 6K, then CRM will only
> allow me to add a number of fields adding up to 2K. The thing is
> also is that CRM will not allow you to DELETE or reconfigure any
> columns, therefore, if I said to make the new field to be 1K, it will
> not allow me. Basically CRM breaks at this point and I can no longer
> add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size
> is before hand so I can allocate certain fields more appropriately
> before adding them to know the size of each field. Is there an store
> procedure to get these values from SQL Server, so I can know if I am
> on the brink of BREAKING CRM?
> Thanks for any help.
> Regards,
> Keener
You do not have to deal with the max row size (to a degree) if you use
TEXT/NTEXT/IMAGE data types. With those column data types, only a
16-byte pointer is stored in the row. If you are still worried a table
may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
somewhat accurate, measure of row size.
For example:
Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
for a more accurate measure that avoid varchar/nvarchar/varbinary
calculation issues, see "Estimating the Size of a Table" in BOL.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||John -- YOU DA MAN... DA MVP MAN!!!!!
I have to do a bit of math, but wow, that was what
I needed.
Thanks a bunch,
Keener
"John Bell" wrote:
> Hi
> You can create a row that is greater than than 8060 characters, but you will
> get an error when you insert data greater than that value. For example
> (formatting may get messed up!):
> create table mytab ( col1 varchar(8000), col2 varchar(8000) )
> -- Warning: The table 'mytab' has been created but its maximum row size
> (16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 16013 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> -- There is a certain amount of overhead!!!!
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 8073 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
> To get column information try sp_help
> sp_help Mytab
> /*
> Name
> Owner
> Type Created_datetime
> ------
> ------
> --
> ---
> mytab
> dbo
> user table 2005-05-31
> 18:23:48.873
>
> Column_name
> Type
> Computed Length Prec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
>
> ------
> ------
> -- -- -- --
> -- --
> --
> ------
> col1
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
> col2
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
>
> Identity
> Seed
> Increment Not For Replication
> ------
> ---
> --- --
> No identity column defined.
> NULL
> NULL NULL
>
> RowGuidCol
> ------
> No rowguidcol column defined.
>
> Data_located_on_filegroup
> ------
> PRIMARY
>
> The object does not have any indexes.
> No constraints have been defined for this object.
> No foreign keys reference this table.
> No views with schema binding reference this table.
> */
> HTH
> John
> "Keener" wrote:
> > I have a question. A bit new to SQL Server, but how does one
> > determine the row size of a given table. Here is my issue:
> >
> > I am a CRM user and I understand that CRM uses SQL Server for its database.
> >
> > When using SQL Server on its own, it will only allow ~8k of DATA to enter
> > the database and will truncate the rest (from my understanding anyway).
> > However, CRM has set its own restrictions on the database and says that "We
> > will not allow
> > any data that will exceed SQL Server's limits of ~8k and therefore we will
> > calculate
> > the size of the fields in the table and determine if anymore columns will be
> > permitted to be added. For example, if I wish to add a field to CRM as text
> > and set it to have a limit of 6K, then CRM will only allow me to add a number
> > of fields adding up to 2K. The thing is also is that CRM will not allow you
> > to DELETE or reconfigure any columns, therefore, if I said to make the new
> > field to be 1K, it will not allow me. Basically CRM breaks at this point and
> > I can no longer add ANYTHING to this table, but I need to.
> >
> > Bearing this in mind, I wish to check and see what the table row size is
> > before hand so I can allocate certain fields more appropriately before adding
> > them to know the size of each field. Is there an store procedure to get
> > these values from SQL Server, so I can know if I am on the brink of BREAKING
> > CRM?
> >
> > Thanks for any help.
> >
> > Regards,
> > Keener|||Thanks for the reply David.
I understand what you mean, but I need to know how
much the table is allocated for each row, not the actual size
of the data in the rows. I will use your method if/when I run
into the issues of managing data row sizes.
Thanks again,
Keener
"David Gugick" wrote:
> Keener wrote:
> > I have a question. A bit new to SQL Server, but how does one
> > determine the row size of a given table. Here is my issue:
> >
> > I am a CRM user and I understand that CRM uses SQL Server for its
> > database.
> >
> > When using SQL Server on its own, it will only allow ~8k of DATA to
> > enter the database and will truncate the rest (from my understanding
> > anyway). However, CRM has set its own restrictions on the database
> > and says that "We will not allow
> > any data that will exceed SQL Server's limits of ~8k and therefore we
> > will calculate
> > the size of the fields in the table and determine if anymore columns
> > will be permitted to be added. For example, if I wish to add a field
> > to CRM as text and set it to have a limit of 6K, then CRM will only
> > allow me to add a number of fields adding up to 2K. The thing is
> > also is that CRM will not allow you to DELETE or reconfigure any
> > columns, therefore, if I said to make the new field to be 1K, it will
> > not allow me. Basically CRM breaks at this point and I can no longer
> > add ANYTHING to this table, but I need to.
> >
> > Bearing this in mind, I wish to check and see what the table row size
> > is before hand so I can allocate certain fields more appropriately
> > before adding them to know the size of each field. Is there an store
> > procedure to get these values from SQL Server, so I can know if I am
> > on the brink of BREAKING CRM?
> >
> > Thanks for any help.
> >
> > Regards,
> > Keener
> You do not have to deal with the max row size (to a degree) if you use
> TEXT/NTEXT/IMAGE data types. With those column data types, only a
> 16-byte pointer is stored in the row. If you are still worried a table
> may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
> somewhat accurate, measure of row size.
> For example:
> Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
> for a more accurate measure that avoid varchar/nvarchar/varbinary
> calculation issues, see "Estimating the Size of a Table" in BOL.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

How to determine what the row size is.

I have a question. A bit new to SQL Server, but how does one
determine the row size of a given table. Here is my issue:
I am a CRM user and I understand that CRM uses SQL Server for its database.
When using SQL Server on its own, it will only allow ~8k of DATA to enter
the database and will truncate the rest (from my understanding anyway).
However, CRM has set its own restrictions on the database and says that "We
will not allow
any data that will exceed SQL Server's limits of ~8k and therefore we will
calculate
the size of the fields in the table and determine if anymore columns will be
permitted to be added. For example, if I wish to add a field to CRM as text
and set it to have a limit of 6K, then CRM will only allow me to add a number
of fields adding up to 2K. The thing is also is that CRM will not allow you
to DELETE or reconfigure any columns, therefore, if I said to make the new
field to be 1K, it will not allow me. Basically CRM breaks at this point and
I can no longer add ANYTHING to this table, but I need to.
Bearing this in mind, I wish to check and see what the table row size is
before hand so I can allocate certain fields more appropriately before adding
them to know the size of each field. Is there an store procedure to get
these values from SQL Server, so I can know if I am on the brink of BREAKING
CRM?
Thanks for any help.
Regards,
Keener
Hi
You can create a row that is greater than than 8060 characters, but you will
get an error when you insert data greater than that value. For example
(formatting may get messed up!):
create table mytab ( col1 varchar(8000), col2 varchar(8000) )
-- Warning: The table 'mytab' has been created but its maximum row size
(16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 16013 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
-- There is a certain amount of overhead!!!!
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 8073 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
To get column information try sp_help
sp_help Mytab
/*
Name
Owner
Type Created_datetime
------
------
mytab
dbo
user table 2005-05-31
18:23:48.873
Column_name
Type
Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
------
------
-- -- -- --
-- --
------
col1
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
col2
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
Identity
Seed
Increment Not For Replication
------
--- --
No identity column defined.
NULL
NULL NULL
RowGuidCol
------
No rowguidcol column defined.
Data_located_on_filegroup
------
PRIMARY
The object does not have any indexes.
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
*/
HTH
John
"Keener" wrote:

> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its database.
> When using SQL Server on its own, it will only allow ~8k of DATA to enter
> the database and will truncate the rest (from my understanding anyway).
> However, CRM has set its own restrictions on the database and says that "We
> will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we will
> calculate
> the size of the fields in the table and determine if anymore columns will be
> permitted to be added. For example, if I wish to add a field to CRM as text
> and set it to have a limit of 6K, then CRM will only allow me to add a number
> of fields adding up to 2K. The thing is also is that CRM will not allow you
> to DELETE or reconfigure any columns, therefore, if I said to make the new
> field to be 1K, it will not allow me. Basically CRM breaks at this point and
> I can no longer add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size is
> before hand so I can allocate certain fields more appropriately before adding
> them to know the size of each field. Is there an store procedure to get
> these values from SQL Server, so I can know if I am on the brink of BREAKING
> CRM?
> Thanks for any help.
> Regards,
> Keener
|||Keener wrote:
> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its
> database.
> When using SQL Server on its own, it will only allow ~8k of DATA to
> enter the database and will truncate the rest (from my understanding
> anyway). However, CRM has set its own restrictions on the database
> and says that "We will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we
> will calculate
> the size of the fields in the table and determine if anymore columns
> will be permitted to be added. For example, if I wish to add a field
> to CRM as text and set it to have a limit of 6K, then CRM will only
> allow me to add a number of fields adding up to 2K. The thing is
> also is that CRM will not allow you to DELETE or reconfigure any
> columns, therefore, if I said to make the new field to be 1K, it will
> not allow me. Basically CRM breaks at this point and I can no longer
> add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size
> is before hand so I can allocate certain fields more appropriately
> before adding them to know the size of each field. Is there an store
> procedure to get these values from SQL Server, so I can know if I am
> on the brink of BREAKING CRM?
> Thanks for any help.
> Regards,
> Keener
You do not have to deal with the max row size (to a degree) if you use
TEXT/NTEXT/IMAGE data types. With those column data types, only a
16-byte pointer is stored in the row. If you are still worried a table
may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
somewhat accurate, measure of row size.
For example:
Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
for a more accurate measure that avoid varchar/nvarchar/varbinary
calculation issues, see "Estimating the Size of a Table" in BOL.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||John -- YOU DA MAN... DA MVP MAN!!!!!
I have to do a bit of math, but wow, that was what
I needed.
Thanks a bunch,
Keener
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You can create a row that is greater than than 8060 characters, but you will
> get an error when you insert data greater than that value. For example
> (formatting may get messed up!):
> create table mytab ( col1 varchar(8000), col2 varchar(8000) )
> -- Warning: The table 'mytab' has been created but its maximum row size
> (16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 16013 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> -- There is a certain amount of overhead!!!!
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 8073 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
> To get column information try sp_help
> sp_help Mytab
> /*
> Name
> Owner
> Type Created_datetime
> ------
> ------
> --
> mytab
> dbo
> user table 2005-05-31
> 18:23:48.873
>
> Column_name
> Type
> Computed Length Prec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
>
> ------
> ------
> -- -- -- --
> -- --
> --
> ------
> col1
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
> col2
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
>
> Identity
> Seed
> Increment Not For Replication
> ------
> --- --
> No identity column defined.
> NULL
> NULL NULL
>
> RowGuidCol
> ------
> No rowguidcol column defined.
>
> Data_located_on_filegroup
> ------
> PRIMARY
>
> The object does not have any indexes.
> No constraints have been defined for this object.
> No foreign keys reference this table.
> No views with schema binding reference this table.
> */
> HTH
> John
> "Keener" wrote:
|||Thanks for the reply David.
I understand what you mean, but I need to know how
much the table is allocated for each row, not the actual size
of the data in the rows. I will use your method if/when I run
into the issues of managing data row sizes.
Thanks again,
Keener
"David Gugick" wrote:

> Keener wrote:
> You do not have to deal with the max row size (to a degree) if you use
> TEXT/NTEXT/IMAGE data types. With those column data types, only a
> 16-byte pointer is stored in the row. If you are still worried a table
> may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
> somewhat accurate, measure of row size.
> For example:
> Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
> for a more accurate measure that avoid varchar/nvarchar/varbinary
> calculation issues, see "Estimating the Size of a Table" in BOL.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

How to determine the last time a table was accessed ?

I'm trying to do some housekeeping. I want to delete user tables from database(s) that have not had any activity...

I cannot seem to find a mechanism for accomplishing this. sysobjects only shows the createdate, not the last time a user table had a SELECT, INSERT, UPDATE or DELETE operation performed on it.

Anyone know how to do this ?

Thanks

(P.S. this is the second posting of this question today, as I went to my threads and I do not see the original post - sorry for the duplicate, but as I say, I do not see the original so am re-posting).

randyvol

SQL server doesn't store such information and all those process is logged in transction log, and you might need third party tools in this case to audit the events or run server side trace if you want to schedule such information for time being.|||

Satya -

First and foremost thank you for your reply.

Next - what?!!? WOW! I just naturally assumed that SQL Server would do this. I cannot imagine a product that is being touted as 'ready for prime time' does not provide such basic necessities. Don't get me wrong, I really like the product, especially the 2k5 instantiation, which is why I'm even more perplexed.

How is one supposed to know over time what tables one can delete with absolute safety? I understand that 3rd party tools provide this ability, but surely they leverage something (perhaps undocumented) in the basic system? Teradata, for instance provides this information - I know, I'm a certified Teradata Master and have used that system's entries on many occasions to ascertain whether or not a table was really 'stale' and could be dropped to free up disk. I would not think it is that big a deal (or that much overhead) to have one extra column, say in sysobjects, for instance, 'last updated'.

I just cannot believe MSFT overlooked this, or expects me to cough up dollars for a 3rd party tool to do this routine maintenance chore. This is something I'd expect to find in the sys tables for sure. Doesn't have to be elegant and exposed into Studio - just basic data I can fetch with a query would suffice.

As for the tranlog.. it is transient. I'm sure that there is data there to mine, but it doesn't help me on the 100's of tables already existent on our legacy system, that have been around for years.

I sure hope MSFT decides to provide this ability soon.

(It does explain why I cannot find any documentation on how to do this though ;-)

Oh well, I guess I'll have to go build my own stuff and let it cook for a couple of quarters to see if tables are stale or not.

Regards

randyvol

How to determine if user is Domain admin?

Does anyone know how to tell from within SQL Server if a
connected user is a Windows Domain Admin? Thanks.You could possibly query AD using an ADSI linked server using an LDAP query
as described in BOL (Linked server section) but this would be a fair bit of
effort and its pretty slow
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"schumacr" <anonymous@.discussions.microsoft.com> wrote in message
news:001401c3a2f6$b8b85ad0$a401280a@.phx.gbl...
Does anyone know how to tell from within SQL Server if a
connected user is a Windows Domain Admin? Thanks.

How to determine if the logged on user is a member of an active directory security group?

We are using Windows authenication within our system, and I was wondering how it would be possible to determine if the user conected to the SQL SERVER instance was a member of a particular active directory security group?

Thanks.

Moved to Security...

|||

You can use sys.login_token (http://msdn2.microsoft.com/en-us/library/ms186740.aspx) or xp_logininfo (http://msdn2.microsoft.com/en-us/library/ms190369.aspx) to get that information.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

How to Determine if a user is a member of the System Admin role?

Is there a script/function that can be used to determine if a user (granted
login/access via NT Group Membership) is a member of the System Administrato
r
group?
We had an issue where a user was a member of multiple NT Global Groups, one
of which was a member of (had) the System Admin role. Our application check
s
to see if the NT Group for our Application has DBO rights, but this returned
false ... yet the user would (by default) create objects (views/tables) in
dbo. We finally traced this down via Enterprise Mgr, Security, Server Roles
and dbl-clicked "System Administrators" and found that there were unexpected
groups there, and our user(s) were in one or more of these groups. How can w
e
determine this via code/script and then "turn it off" for our database (of
course it is possible that a user that is a member of another group MAY need
SA rights in another database)?
Thank you,
Brad
--
Brad Ashforth> Is there a script/function that can be used to determine if a user
> (granted
> login/access via NT Group Membership) is a member of the System
> Administrator
> group?
SELECT IS_SRVROLEMEMBER('sysadmin')

> How can we
> determine this via code/script and then "turn it off" for our database (of
> course it is possible that a user that is a member of another group MAY
> need
> SA rights in another database)?
In SQL 2000, there are only 2 cases where objects will be created in the dbo
schema by default: 1) user is the database owner and 2) user is a
sysadmin role member. The query 'SELECT USER' will return 'dbo' in both
cases.
I'm not sure I understand what you mean by 'turn it off'. Do you mean that
you want the default schema to be other than 'dbo' for the dbo user? Have
you considered schema-qualifying object names so that the default schema
isn't relevant?
Hope this helps.
Dan Guzman
SQL Server MVP
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:5BEE8141-69CC-415A-A57E-48C875CB31AE@.microsoft.com...
> Is there a script/function that can be used to determine if a user
> (granted
> login/access via NT Group Membership) is a member of the System
> Administrator
> group?
> We had an issue where a user was a member of multiple NT Global Groups,
> one
> of which was a member of (had) the System Admin role. Our application
> checks
> to see if the NT Group for our Application has DBO rights, but this
> returned
> false ... yet the user would (by default) create objects (views/tables) in
> dbo. We finally traced this down via Enterprise Mgr, Security, Server
> Roles
> and dbl-clicked "System Administrators" and found that there were
> unexpected
> groups there, and our user(s) were in one or more of these groups. How can
> we
> determine this via code/script and then "turn it off" for our database (of
> course it is possible that a user that is a member of another group MAY
> need
> SA rights in another database)?
> Thank you,
> Brad
> --
> Brad Ashforth|||Hello Brad,
As for a windows user account(or group) or a sqlserver account, before we
check if it is of sysadmin role (in the server instance), we should first
check if it's a server login(principal) on that server instance. For this,
we can use some T-SQL query to lookup all the principals of sysadmin role
in the master db. It'll be a bit different for SQL 2005 and SQL 2000:
============2005===========
select p1.Name as Role_name, p2.Name as Member_name from
sys.server_role_members r1 inner join sys.server_principals p1
on r1.Role_principal_id = p1.Principal_id
inner join sys.server_principals p2
on r1.Member_principal_id = p2.Principal_id
=========================
As you can see, we need to query multiple catalog views in sys schema.
While in SQL server 2000, we can diretly query the "syslogins" table in
master db, and this table contains a "sysadmin" column indicate whether the
certain principal is of sysadmin role.
==============2000==================
select * from syslogins
Hope this helps.
Regards,
Steven Cheng
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.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Brad,
How are you doing on this issue or does our suggestion help you some? If
there is still anything we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
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.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Brad,
How are you doing on this issue or does our suggestion help you some? If
there is still anything we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
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.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

How to determine if a user has a permission programmatically?

Hi,
I need to determine in advance if a user logged on to SQL Server 2005 can
execute certain privileged stored procedures and statements (such as
sp_addrolemember and ALTER LOGIN). To do this, I though I would need to
determine programmatically if a user has specific permissions (ALTER ANY
LOGIN, ALTER ANY CREDENTIAL, CONTROL SERVER, ALTER ANY USER, CREATE ROLE,
ALTER ANY ROLE).
Is there a way to perform this check programmatically via T-SQL? If this can
be done programmatically, is there a way to determine if a permission is
implied implicitly if it is not set up explicitly (e.g. via a group the user
belongs to, such as BUILTIN\Administrators)? Is there a better approach to
make the check?
Thanks,
AlekTry sp_helprotect , described here
http://msdn2.microsoft.com/en-us/library/aa933420(SQL.80).aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Alek" <alekDOTdavisATintelDOTcom> wrote in message
news:%23sEN40XHHHA.1064@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I need to determine in advance if a user logged on to SQL Server 2005 can
> execute certain privileged stored procedures and statements (such as
> sp_addrolemember and ALTER LOGIN). To do this, I though I would need to
> determine programmatically if a user has specific permissions (ALTER ANY
> LOGIN, ALTER ANY CREDENTIAL, CONTROL SERVER, ALTER ANY USER, CREATE ROLE,
> ALTER ANY ROLE).
> Is there a way to perform this check programmatically via T-SQL? If this
> can be done programmatically, is there a way to determine if a permission
> is implied implicitly if it is not set up explicitly (e.g. via a group the
> user belongs to, such as BUILTIN\Administrators)? Is there a better
> approach to make the check?
> Thanks,
> Alek
>|||In SQL Server 2005, you can use the has_perms_by_name
function to check the current users effective permissions on
securable.
http://msdn2.microsoft.com/en-us/library/ms189802.aspx
-Sue
On Mon, 11 Dec 2006 15:36:43 -0800, "Alek"
<alekDOTdavisATintelDOTcom> wrote:

>Hi,
>I need to determine in advance if a user logged on to SQL Server 2005 can
>execute certain privileged stored procedures and statements (such as
>sp_addrolemember and ALTER LOGIN). To do this, I though I would need to
>determine programmatically if a user has specific permissions (ALTER ANY
>LOGIN, ALTER ANY CREDENTIAL, CONTROL SERVER, ALTER ANY USER, CREATE ROLE,
>ALTER ANY ROLE).
>Is there a way to perform this check programmatically via T-SQL? If this ca
n
>be done programmatically, is there a way to determine if a permission is
>implied implicitly if it is not set up explicitly (e.g. via a group the use
r
>belongs to, such as BUILTIN\Administrators)? Is there a better approach to
>make the check?
>Thanks,
>Alek
>|||Thank you Remus. This is very helpful.
Alek
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> w
rote
in message news:%233xgzrYHHHA.1248@.TK2MSFTNGP03.phx.gbl...
> Try sp_helprotect , described here
> http://msdn2.microsoft.com/en-us/library/aa933420(SQL.80).aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Alek" <alekDOTdavisATintelDOTcom> wrote in message
> news:%23sEN40XHHHA.1064@.TK2MSFTNGP04.phx.gbl...
>|||Thanks a lot Sue. This looks exactly like what I'm looking for. :-)
Alek
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:ng3sn2pdu6apq505ftk8mhnpp73l2i7mk7@.
4ax.com...
> In SQL Server 2005, you can use the has_perms_by_name
> function to check the current users effective permissions on
> securable.
> http://msdn2.microsoft.com/en-us/library/ms189802.aspx
> -Sue
> On Mon, 11 Dec 2006 15:36:43 -0800, "Alek"
> <alekDOTdavisATintelDOTcom> wrote:
>
>sql

How to determine connection pool from SQL Server?

I was going thru Enterprise Manager and can't see connection count, I can
see User connected, but that doesn't give me an actual connection count.
Is there a way to determine my connection pool count from Enterprise Manager
and/or some other tool?
Thanks, Rob.
'Connection Pool' is a feature of the client side library, if you are using
the .net provider for sql server then you should be able to look at the
following perfmon object:
".NET data provider for SQL Server"
hth
Vikram Vamshi
Eclipsys Corporation
"Rob R. Ainscough" <robains@.pacbell.net> wrote in message
news:u%230vD7dQFHA.576@.TK2MSFTNGP15.phx.gbl...
>I was going thru Enterprise Manager and can't see connection count, I can
>see User connected, but that doesn't give me an actual connection count.
> Is there a way to determine my connection pool count from Enterprise
> Manager and/or some other tool?
> Thanks, Rob.
>
|||I ended up using the SQL Profiler -- Session -- not perferct (does show
counts), but gave me an idea.
Where does one find the ".NET data provider for SQL Server" performance mon
object?
"Vikram Vamshi" <vikram.vamshi@.nospam.com> wrote in message
news:OQxQ%23WfQFHA.4020@.tk2msftngp13.phx.gbl...
> 'Connection Pool' is a feature of the client side library, if you are
> using the .net provider for sql server then you should be able to look at
> the following perfmon object:
> ".NET data provider for SQL Server"
> hth
> --
> Vikram Vamshi
> Eclipsys Corporation
> "Rob R. Ainscough" <robains@.pacbell.net> wrote in message
> news:u%230vD7dQFHA.576@.TK2MSFTNGP15.phx.gbl...
>
|||It is bundled with the .net runtime /sdk
Vikram Vamshi
Eclipsys Corporation
"Rob R. Ainscough" <robains@.pacbell.net> wrote in message
news:%23a4CINgQFHA.3336@.TK2MSFTNGP10.phx.gbl...
>I ended up using the SQL Profiler -- Session -- not perferct (does show
>counts), but gave me an idea.
> Where does one find the ".NET data provider for SQL Server" performance
> mon object?
> "Vikram Vamshi" <vikram.vamshi@.nospam.com> wrote in message
> news:OQxQ%23WfQFHA.4020@.tk2msftngp13.phx.gbl...
>
sql

How to determine connection pool from SQL Server?

I was going thru Enterprise Manager and can't see connection count, I can
see User connected, but that doesn't give me an actual connection count.
Is there a way to determine my connection pool count from Enterprise Manager
and/or some other tool?
Thanks, Rob.'Connection Pool' is a feature of the client side library, if you are using
the .net provider for sql server then you should be able to look at the
following perfmon object:
".NET data provider for SQL Server"
hth
--
Vikram Vamshi
Eclipsys Corporation
"Rob R. Ainscough" <robains@.pacbell.net> wrote in message
news:u%230vD7dQFHA.576@.TK2MSFTNGP15.phx.gbl...
>I was going thru Enterprise Manager and can't see connection count, I can
>see User connected, but that doesn't give me an actual connection count.
> Is there a way to determine my connection pool count from Enterprise
> Manager and/or some other tool?
> Thanks, Rob.
>|||I ended up using the SQL Profiler -- Session -- not perferct (does show
counts), but gave me an idea.
Where does one find the ".NET data provider for SQL Server" performance mon
object?
"Vikram Vamshi" <vikram.vamshi@.nospam.com> wrote in message
news:OQxQ%23WfQFHA.4020@.tk2msftngp13.phx.gbl...
> 'Connection Pool' is a feature of the client side library, if you are
> using the .net provider for sql server then you should be able to look at
> the following perfmon object:
> ".NET data provider for SQL Server"
> hth
> --
> Vikram Vamshi
> Eclipsys Corporation
> "Rob R. Ainscough" <robains@.pacbell.net> wrote in message
> news:u%230vD7dQFHA.576@.TK2MSFTNGP15.phx.gbl...
>|||It is bundled with the .net runtime /sdk
Vikram Vamshi
Eclipsys Corporation
"Rob R. Ainscough" <robains@.pacbell.net> wrote in message
news:%23a4CINgQFHA.3336@.TK2MSFTNGP10.phx.gbl...
>I ended up using the SQL Profiler -- Session -- not perferct (does show
>counts), but gave me an idea.
> Where does one find the ".NET data provider for SQL Server" performance
> mon object?
> "Vikram Vamshi" <vikram.vamshi@.nospam.com> wrote in message
> news:OQxQ%23WfQFHA.4020@.tk2msftngp13.phx.gbl...
>

How to design SP with variable number of params?

I have a form that allows a user to update contact info. For example:
First name
Last name
PO Box
City
State
There are actually more fields (up to 50) but I have only used five for
simplicity. Sometimes a user will submit an update for all fields.
However, for a web service, some one may only send the First Name or any
other one field. In that case, is it better to design an SP for each case?
I see that having scaling issues.
Another approach is to design one SP with many conditionals (50). Both
approaches are inefficient. What is a better way?
Thanks,
BrettSpecify default values for your parameters. I.e.,
CREATE PROCEDURE sample
@.paramLast VARCHAR(30) = NULL, -- NULL default value
@.paramFirst VARCHAR(30) = NULL, -- NULL default value
@.paramPoBox VARCHAR(30) = NULL,
@.paramCity VARCHAR(30) = '', -- Empty string default value
@.paramState CHAR(2) = 'NY' -- 'NY' default value
It will be a little tedious for 50 fields, but will allow you to not specify
parameters on calling.
"Brett" <no@.spam.net> wrote in message
news:OkwoMaUGFHA.2676@.TK2MSFTNGP12.phx.gbl...
>I have a form that allows a user to update contact info. For example:
> First name
> Last name
> PO Box
> City
> State
> There are actually more fields (up to 50) but I have only used five for
> simplicity. Sometimes a user will submit an update for all fields.
> However, for a web service, some one may only send the First Name or any
> other one field. In that case, is it better to design an SP for each
> case? I see that having scaling issues.
> Another approach is to design one SP with many conditionals (50). Both
> approaches are inefficient. What is a better way?
> Thanks,
> Brett
>|||Michael C# wrote:
> Specify default values for your parameters. I.e.,
> CREATE PROCEDURE sample
> @.paramLast VARCHAR(30) = NULL, -- NULL default value
> @.paramFirst VARCHAR(30) = NULL, -- NULL default value
> @.paramPoBox VARCHAR(30) = NULL,
> @.paramCity VARCHAR(30) = '', -- Empty string default
> value @.paramState CHAR(2) = 'NY' -- 'NY' default
> value
> It will be a little tedious for 50 fields, but will allow you to not
> specify parameters on calling.
>
I'm not sure that will work for the OP for updating.
Specify all updatable values in the parameter list. 50 is a lot, and I
might question the number of attributes on the underlying table. Unless
you're dealing with more than one table and could break up the updates
in a meaningful way.
David Gugick
Imceda Software
www.imceda.com|||Commonly we would just update all data on an update in the stored procedure
unless there is a great reason not to. You could do something like:
CREATE PROCEDURE TABLE_UPDATE
@.LastName VARCHAR(30) = NULL,
@.FirstName VARCHAR(30) = NULL,
as
update table
set lastName = coalesce(@.lastName, lastName),
firstName = coalesce(@.firstName, firstName)
go
Then if you call it with table_update @.firstName ='Bob'
The current value of lastName will be used, and the new value for
@.firstName.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Brett" <no@.spam.net> wrote in message
news:OkwoMaUGFHA.2676@.TK2MSFTNGP12.phx.gbl...
>I have a form that allows a user to update contact info. For example:
> First name
> Last name
> PO Box
> City
> State
> There are actually more fields (up to 50) but I have only used five for
> simplicity. Sometimes a user will submit an update for all fields.
> However, for a web service, some one may only send the First Name or any
> other one field. In that case, is it better to design an SP for each
> case? I see that having scaling issues.
> Another approach is to design one SP with many conditionals (50). Both
> approaches are inefficient. What is a better way?
> Thanks,
> Brett
>|||there is one thing that bothers me with this approach (regardles of number
of fields). the thing is that on update, event if the value for the column
is unchanged, the constraints are being checked all the same. eg, if there
is a foreign key constraint, updating a fk column (with the same value, thus
in fact not updating at all) will cause a lookup in the referenced table,
which is absolutely unnecessary, imho. but the alternatives - dynamically
constructing the update statement, or creating a separate statement for
every combination of params - make even less sense.
any thoughts?
dean
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23sEA5TWGFHA.4088@.TK2MSFTNGP09.phx.gbl...
> Commonly we would just update all data on an update in the stored
procedure
> unless there is a great reason not to. You could do something like:
> CREATE PROCEDURE TABLE_UPDATE
> @.LastName VARCHAR(30) = NULL,
> @.FirstName VARCHAR(30) = NULL,
> as
> update table
> set lastName = coalesce(@.lastName, lastName),
> firstName = coalesce(@.firstName, firstName)
> go
> Then if you call it with table_update @.firstName ='Bob'
> The current value of lastName will be used, and the new value for
> @.firstName.
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Brett" <no@.spam.net> wrote in message
> news:OkwoMaUGFHA.2676@.TK2MSFTNGP12.phx.gbl...
>|||This seems to be the best approach of the posts here. I see there probably
isn't a way to get around conditionals for NULL checks. coalesce is a type
of conditional but probably better than using multiple IF statements
correct?
Thanks,
Brett
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:%23sEA5TWGFHA.4088@.TK2MSFTNGP09.phx.gbl...
> Commonly we would just update all data on an update in the stored
> procedure unless there is a great reason not to. You could do something
> like:
> CREATE PROCEDURE TABLE_UPDATE
> @.LastName VARCHAR(30) = NULL,
> @.FirstName VARCHAR(30) = NULL,
> as
> update table
> set lastName = coalesce(@.lastName, lastName),
> firstName = coalesce(@.firstName, firstName)
> go
> Then if you call it with table_update @.firstName ='Bob'
> The current value of lastName will be used, and the new value for
> @.firstName.
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Brett" <no@.spam.net> wrote in message
> news:OkwoMaUGFHA.2676@.TK2MSFTNGP12.phx.gbl...
>|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%2359VcIWGFHA.524@.TK2MSFTNGP14.phx.gbl...
> Michael C# wrote:
> I'm not sure that will work for the OP for updating.
>
Why not? Here's an example of a stored procedure, with a variable number of
params, that updates a table.
--Create Table and Primary Key
CREATE TABLE [dbo].[Table1] (
[IDNum] [int] NOT NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[IDNum]
) ON [PRIMARY]
GO
--Populate table
INSERT INTO Table1 (IDNum, LastName, FirstName) VALUES (0, 'Jetson',
'George')
INSERT INTO Table1 (IDNum, LastName, FirstName) VALUES (1, 'Flintstone',
'Fred')
INSERT INTO Table1 (IDNum, LastName, FirstName) VALUES (2, 'Rubble',
'Barney')
GO
--Create stored procedure with variable number of parameters
CREATE PROCEDURE usp_UpdateRecord
@.paramID INT,
@.paramLast VARCHAR(50) = NULL,
@.paramFirst VARCHAR(50) = NULL
AS
UPDATE Table1 SET LastName = @.paramLast
WHERE IDNum = @.paramID
AND @.paramLast IS NOT NULL
UPDATE Table1 SET FirstName = @.paramFirst
WHERE IDNum = @.paramID
AND @.paramFirst IS NOT NULL
GO
--Now call the stored procedure with a variable
--number of parameters each time
EXEC usp_UpdateRecord @.paramID = 0, @.paramLast = 'Johnson'
EXEC usp_UpdateRecord @.paramID = 1, @.paramFirst = 'Wilma'
EXEC usp_UpdateRecord @.paramID = 2, @.paramLast = 'Public', @.paramFirst =
'John'
GO

> Specify all updatable values in the parameter list. 50 is a lot, and I
> might question the number of attributes on the underlying table. Unless
> you're dealing with more than one table and could break up the updates in
> a meaningful way.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||I personally would not recommend you design a procedure to perform up to
50 distinct updates to update a single row in the table. Seems more work
and overhead than a single update to me.
David G.|||Are you talking about the overhead incurred when typing in the code once, or
the overhead incurred each time you UPDATE 50 fields in order to change one?
Michael C.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:eunWozbGFHA.3112@.tk2msftngp13.phx.gbl...
>I personally would not recommend you design a procedure to perform up to 50
>distinct updates to update a single row in the table. Seems more work and
>overhead than a single update to me.
> --
> David G.
>|||Michael C# wrote:
> Are you talking about the overhead incurred when typing in the code
> once, or the overhead incurred each time you UPDATE 50 fields in
> order to change one?
> Michael C.
I just mean the possibly running up to 50 individual updates to satisfy
what a single update can do. Plus, the implementation does not allow you
to return a column value to NULL, if needed.
My only real point here is issuing a single update and supplying all
parameters is generally the easiest, most maintainable, and safest
implementation. If the OP has a component in ASP.net or his/her
fat-client app that automates the execution of the update, then he only
has to write it once.
David G.

how to design dynamic reports based on user's choice

Hi all,

I'm a beginner to Report Services, and have tons of questions.

Here's the first one:

if the reports are created based on the condition that the user selects, how can I create the reports with Report Services?

For example,

the user can select the fields that will be shown on the reports, as well as the group fields, the sort fields and restrict fields. So I would not be able to pre-create all possible reports and deploy them to the report server, and I think I should create the reports dynamicly based on what the user select.

Could someone tell me how to do it (create and deploy the reports)?

Thanks a million!

Jonee

I think this it's possible to certain extend, but not sure if 100% percent. It would take some research and see how far can you get on this one.

How to design a large DB?

Hi All,
I'm trying to design a large DB and considering which direction to go.
The planned login user accounts will be 10,000, and each user has 15 DB
Tables. Each Table's structure similar to other users, but user's data
records have no relation with each other.
1. Create 1 Database for all users, and an User_Login Table containing
User_ID, and 15 other Tables each containing the related User_ID.
This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000 X
each user's data record).
2. Create 1 Database for each user.
This is easier to manage each user, but there'll be 10,000 Databases.
3. Create 1 Database for all users, and 15 Tables for each user.
This could result a DB of 15 X 10,000 Tables.
I also have to consider how to manage each user's data, i.e, backup, limit
size, delete outdated records ...
Please enlighten me how to design such a DB. Is there an optimalized
structure for both programming and management?
Best regards,
Kevin
The right design depends on:
What's the total size (GB) of data?
What's the operations you want to do with the data and how fast you want
them to be?
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:%23Wtc6i2JFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I'm trying to design a large DB and considering which direction to go.
> The planned login user accounts will be 10,000, and each user has 15 DB
> Tables. Each Table's structure similar to other users, but user's data
> records have no relation with each other.
> 1. Create 1 Database for all users, and an User_Login Table containing
> User_ID, and 15 other Tables each containing the related User_ID.
> This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000
> X each user's data record).
> 2. Create 1 Database for each user.
> This is easier to manage each user, but there'll be 10,000 Databases.
> 3. Create 1 Database for all users, and 15 Tables for each user.
> This could result a DB of 15 X 10,000 Tables.
> I also have to consider how to manage each user's data, i.e, backup, limit
> size, delete outdated records ...
> Please enlighten me how to design such a DB. Is there an optimalized
> structure for both programming and management?
>
> Best regards,
> Kevin
>
|||Kevin
Don't create 1 database for each use, moreover you said that table's
structure is the same. It's hard to maintain, optimize, tune. SQL Server
will need to use more system resource to maintain the databases.
"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:%23Wtc6i2JFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I'm trying to design a large DB and considering which direction to go.
> The planned login user accounts will be 10,000, and each user has 15 DB
> Tables. Each Table's structure similar to other users, but user's data
> records have no relation with each other.
> 1. Create 1 Database for all users, and an User_Login Table containing
> User_ID, and 15 other Tables each containing the related User_ID.
> This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000
X
> each user's data record).
> 2. Create 1 Database for each user.
> This is easier to manage each user, but there'll be 10,000 Databases.
> 3. Create 1 Database for all users, and 15 Tables for each user.
> This could result a DB of 15 X 10,000 Tables.
> I also have to consider how to manage each user's data, i.e, backup, limit
> size, delete outdated records ...
> Please enlighten me how to design such a DB. Is there an optimalized
> structure for both programming and management?
>
> Best regards,
> Kevin
>
|||"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:%23Wtc6i2JFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I'm trying to design a large DB and considering which direction to go.
> The planned login user accounts will be 10,000, and each user has 15 DB
> Tables. Each Table's structure similar to other users, but user's data
> records have no relation with each other.
> 1. Create 1 Database for all users, and an User_Login Table containing
> User_ID, and 15 other Tables each containing the related User_ID.
> This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000
> X each user's data record).
> 2. Create 1 Database for each user.
> This is easier to manage each user, but there'll be 10,000 Databases.
> 3. Create 1 Database for all users, and 15 Tables for each user.
> This could result a DB of 15 X 10,000 Tables.
> I also have to consider how to manage each user's data, i.e, backup, limit
> size, delete outdated records ...
> Please enlighten me how to design such a DB. Is there an optimalized
> structure for both programming and management?
>
This one is easy. Option 1.
David
|||I'm with all the others. Assume a single set of tables and a single
database for all users. Presumably the User ID will be part of the key
in these tables.
Throughout you've described your DB as "large" without telling us the
actual size in bytes. SQL Server can handle many terabytes of data. The
best strategy is to design your logical model first (logically you put
like data in a single table, not partitioned across multiple tables)
then estimate sizes and tune performance when you are ready to create
physical databases and test-data.
David Portas
SQL Server MVP
|||Thanks All,
After your opinions, I'll definitely use option 1. The data is not really
huge, but our server machine is not very good and a little difficult to
manage each user's data.
Best regards,
Kevin
"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:%23Wtc6i2JFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I'm trying to design a large DB and considering which direction to go.
> The planned login user accounts will be 10,000, and each user has 15 DB
> Tables. Each Table's structure similar to other users, but user's data
> records have no relation with each other.
> 1. Create 1 Database for all users, and an User_Login Table containing
> User_ID, and 15 other Tables each containing the related User_ID.
> This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000
> X each user's data record).
> 2. Create 1 Database for each user.
> This is easier to manage each user, but there'll be 10,000 Databases.
> 3. Create 1 Database for all users, and 15 Tables for each user.
> This could result a DB of 15 X 10,000 Tables.
> I also have to consider how to manage each user's data, i.e, backup, limit
> size, delete outdated records ...
> Please enlighten me how to design such a DB. Is there an optimalized
> structure for both programming and management?
>
> Best regards,
> Kevin
>
|||"Kevin" <Kevin@.NoSpam.home.nl> schrieb im Newsbeitrag
news:OCQefgEKFHA.2716@.TK2MSFTNGP15.phx.gbl...
> Thanks All,
> After your opinions, I'll definitely use option 1. The data is not
really
> huge, but our server machine is not very good and a little difficult to
> manage each user's data.
Then definitely use option 1. Note, that you might not need to put the
user id into all tables. For example, if one of the main table is a
contact table and each contact has n adresses (sitting in another table)
then you'll have a contact id which is used for joining the adresses of
this contact. Then only the contacts table needs a user id. Or you have
an additional table that has user id, contact id and no user id is needed
in the contact table. But that really depends on the nature of your data
and what you want to do with it.
Kind regards
robert

How to design a large DB?

Hi All,
I'm trying to design a large DB and considering which direction to go.
The planned login user accounts will be 10,000, and each user has 15 DB
Tables. Each Table's structure similar to other users, but user's data
records have no relation with each other.
1. Create 1 Database for all users, and an User_Login Table containing
User_ID, and 15 other Tables each containing the related User_ID.
This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000 X
each user's data record).
2. Create 1 Database for each user.
This is easier to manage each user, but there'll be 10,000 Databases.
3. Create 1 Database for all users, and 15 Tables for each user.
This could result a DB of 15 X 10,000 Tables.
I also have to consider how to manage each user's data, i.e, backup, limit
size, delete outdated records ...
Please enlighten me how to design such a DB. Is there an optimalized
structure for both programming and management?
Best regards,
KevinWhat's appends during upgrades or changes in the schema? Does your 10 000
clients must be updated at the same time?
What about the volume? does each client use 1mb or 1gb of data?
Maybe you can mix the solution...
Some tables can be shared while other tables are unique by user (specially
the fact table)
Big shared tables impact the query performance. allways filtering with the
UserId will degrade the performance on the biggest tables.
Unfortunatly you can't use partioned views, because there is a limitation of
256 tables in 1 view.
"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:OXJd9k2JFHA.3516@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I'm trying to design a large DB and considering which direction to go.
> The planned login user accounts will be 10,000, and each user has 15 DB
> Tables. Each Table's structure similar to other users, but user's data
> records have no relation with each other.
> 1. Create 1 Database for all users, and an User_Login Table containing
> User_ID, and 15 other Tables each containing the related User_ID.
> This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000
> X
> each user's data record).
> 2. Create 1 Database for each user.
> This is easier to manage each user, but there'll be 10,000 Databases.
> 3. Create 1 Database for all users, and 15 Tables for each user.
> This could result a DB of 15 X 10,000 Tables.
> I also have to consider how to manage each user's data, i.e, backup, limit
> size, delete outdated records ...
> Please enlighten me how to design such a DB. Is there an optimalized
> structure for both programming and management?
>
> Best regards,
> Kevin
>
>

How to design a large DB?

Hi All,
I'm trying to design a large DB and considering which direction to go.
The planned login user accounts will be 10,000, and each user has 15 DB
Tables. Each Table's structure similar to other users, but user's data
records have no relation with each other.
1. Create 1 Database for all users, and an User_Login Table containing
User_ID, and 15 other Tables each containing the related User_ID.
This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000 X
each user's data record).
2. Create 1 Database for each user.
This is easier to manage each user, but there'll be 10,000 Databases.
3. Create 1 Database for all users, and 15 Tables for each user.
This could result a DB of 15 X 10,000 Tables.
I also have to consider how to manage each user's data, i.e, backup, limit
size, delete outdated records ...
Please enlighten me how to design such a DB. Is there an optimalized
structure for both programming and management?
Best regards,
KevinThe right design depends on:
What's the total size (GB) of data?
What's the operations you want to do with the data and how fast you want
them to be?
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:%23Wtc6i2JFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I'm trying to design a large DB and considering which direction to go.
> The planned login user accounts will be 10,000, and each user has 15 DB
> Tables. Each Table's structure similar to other users, but user's data
> records have no relation with each other.
> 1. Create 1 Database for all users, and an User_Login Table containing
> User_ID, and 15 other Tables each containing the related User_ID.
> This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000
> X each user's data record).
> 2. Create 1 Database for each user.
> This is easier to manage each user, but there'll be 10,000 Databases.
> 3. Create 1 Database for all users, and 15 Tables for each user.
> This could result a DB of 15 X 10,000 Tables.
> I also have to consider how to manage each user's data, i.e, backup, limit
> size, delete outdated records ...
> Please enlighten me how to design such a DB. Is there an optimalized
> structure for both programming and management?
>
> Best regards,
> Kevin
>|||Kevin
Don't create 1 database for each use, moreover you said that table's
structure is the same. It's hard to maintain, optimize, tune. SQL Server
will need to use more system resource to maintain the databases.
"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:%23Wtc6i2JFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I'm trying to design a large DB and considering which direction to go.
> The planned login user accounts will be 10,000, and each user has 15 DB
> Tables. Each Table's structure similar to other users, but user's data
> records have no relation with each other.
> 1. Create 1 Database for all users, and an User_Login Table containing
> User_ID, and 15 other Tables each containing the related User_ID.
> This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000
X
> each user's data record).
> 2. Create 1 Database for each user.
> This is easier to manage each user, but there'll be 10,000 Databases.
> 3. Create 1 Database for all users, and 15 Tables for each user.
> This could result a DB of 15 X 10,000 Tables.
> I also have to consider how to manage each user's data, i.e, backup, limit
> size, delete outdated records ...
> Please enlighten me how to design such a DB. Is there an optimalized
> structure for both programming and management?
>
> Best regards,
> Kevin
>|||"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:%23Wtc6i2JFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I'm trying to design a large DB and considering which direction to go.
> The planned login user accounts will be 10,000, and each user has 15 DB
> Tables. Each Table's structure similar to other users, but user's data
> records have no relation with each other.
> 1. Create 1 Database for all users, and an User_Login Table containing
> User_ID, and 15 other Tables each containing the related User_ID.
> This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000
> X each user's data record).
> 2. Create 1 Database for each user.
> This is easier to manage each user, but there'll be 10,000 Databases.
> 3. Create 1 Database for all users, and 15 Tables for each user.
> This could result a DB of 15 X 10,000 Tables.
> I also have to consider how to manage each user's data, i.e, backup, limit
> size, delete outdated records ...
> Please enlighten me how to design such a DB. Is there an optimalized
> structure for both programming and management?
>
This one is easy. Option 1.
David|||I'm with all the others. Assume a single set of tables and a single
database for all users. Presumably the User ID will be part of the key
in these tables.
Throughout you've described your DB as "large" without telling us the
actual size in bytes. SQL Server can handle many terabytes of data. The
best strategy is to design your logical model first (logically you put
like data in a single table, not partitioned across multiple tables)
then estimate sizes and tune performance when you are ready to create
physical databases and test-data.
David Portas
SQL Server MVP
--|||Thanks All,
After your opinions, I'll definitely use option 1. The data is not really
huge, but our server machine is not very good and a little difficult to
manage each user's data.
Best regards,
Kevin
"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:%23Wtc6i2JFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I'm trying to design a large DB and considering which direction to go.
> The planned login user accounts will be 10,000, and each user has 15 DB
> Tables. Each Table's structure similar to other users, but user's data
> records have no relation with each other.
> 1. Create 1 Database for all users, and an User_Login Table containing
> User_ID, and 15 other Tables each containing the related User_ID.
> This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000
> X each user's data record).
> 2. Create 1 Database for each user.
> This is easier to manage each user, but there'll be 10,000 Databases.
> 3. Create 1 Database for all users, and 15 Tables for each user.
> This could result a DB of 15 X 10,000 Tables.
> I also have to consider how to manage each user's data, i.e, backup, limit
> size, delete outdated records ...
> Please enlighten me how to design such a DB. Is there an optimalized
> structure for both programming and management?
>
> Best regards,
> Kevin
>|||"Kevin" <Kevin@.NoSpam.home.nl> schrieb im Newsbeitrag
news:OCQefgEKFHA.2716@.TK2MSFTNGP15.phx.gbl...
> Thanks All,
> After your opinions, I'll definitely use option 1. The data is not
really
> huge, but our server machine is not very good and a little difficult to
> manage each user's data.
Then definitely use option 1. Note, that you might not need to put the
user id into all tables. For example, if one of the main table is a
contact table and each contact has n adresses (sitting in another table)
then you'll have a contact id which is used for joining the adresses of
this contact. Then only the contacts table needs a user id. Or you have
an additional table that has user id, contact id and no user id is needed
in the contact table. But that really depends on the nature of your data
and what you want to do with it.
Kind regards
robert

How to design a large DB?

Hi All,
I'm trying to design a large DB and considering which direction to go.
The planned login user accounts will be 10,000, and each user has 15 DB
Tables. Each Table's structure similar to other users, but user's data
records have no relation with each other.
1. Create 1 Database for all users, and an User_Login Table containing
User_ID, and 15 other Tables each containing the related User_ID.
This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000 X
each user's data record).
2. Create 1 Database for each user.
This is easier to manage each user, but there'll be 10,000 Databases.
3. Create 1 Database for all users, and 15 Tables for each user.
This could result a DB of 15 X 10,000 Tables.
I also have to consider how to manage each user's data, i.e, backup, limit
size, delete outdated records ...
Please enlighten me how to design such a DB. Is there an optimalized
structure for both programming and management?
Best regards,
KevinThe right design depends on:
What's the total size (GB) of data?
What's the operations you want to do with the data and how fast you want
them to be?
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:%23Wtc6i2JFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I'm trying to design a large DB and considering which direction to go.
> The planned login user accounts will be 10,000, and each user has 15 DB
> Tables. Each Table's structure similar to other users, but user's data
> records have no relation with each other.
> 1. Create 1 Database for all users, and an User_Login Table containing
> User_ID, and 15 other Tables each containing the related User_ID.
> This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000
> X each user's data record).
> 2. Create 1 Database for each user.
> This is easier to manage each user, but there'll be 10,000 Databases.
> 3. Create 1 Database for all users, and 15 Tables for each user.
> This could result a DB of 15 X 10,000 Tables.
> I also have to consider how to manage each user's data, i.e, backup, limit
> size, delete outdated records ...
> Please enlighten me how to design such a DB. Is there an optimalized
> structure for both programming and management?
>
> Best regards,
> Kevin
>|||Kevin
Don't create 1 database for each use, moreover you said that table's
structure is the same. It's hard to maintain, optimize, tune. SQL Server
will need to use more system resource to maintain the databases.
"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:%23Wtc6i2JFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I'm trying to design a large DB and considering which direction to go.
> The planned login user accounts will be 10,000, and each user has 15 DB
> Tables. Each Table's structure similar to other users, but user's data
> records have no relation with each other.
> 1. Create 1 Database for all users, and an User_Login Table containing
> User_ID, and 15 other Tables each containing the related User_ID.
> This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000
X
> each user's data record).
> 2. Create 1 Database for each user.
> This is easier to manage each user, but there'll be 10,000 Databases.
> 3. Create 1 Database for all users, and 15 Tables for each user.
> This could result a DB of 15 X 10,000 Tables.
> I also have to consider how to manage each user's data, i.e, backup, limit
> size, delete outdated records ...
> Please enlighten me how to design such a DB. Is there an optimalized
> structure for both programming and management?
>
> Best regards,
> Kevin
>|||"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:%23Wtc6i2JFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I'm trying to design a large DB and considering which direction to go.
> The planned login user accounts will be 10,000, and each user has 15 DB
> Tables. Each Table's structure similar to other users, but user's data
> records have no relation with each other.
> 1. Create 1 Database for all users, and an User_Login Table containing
> User_ID, and 15 other Tables each containing the related User_ID.
> This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000
> X each user's data record).
> 2. Create 1 Database for each user.
> This is easier to manage each user, but there'll be 10,000 Databases.
> 3. Create 1 Database for all users, and 15 Tables for each user.
> This could result a DB of 15 X 10,000 Tables.
> I also have to consider how to manage each user's data, i.e, backup, limit
> size, delete outdated records ...
> Please enlighten me how to design such a DB. Is there an optimalized
> structure for both programming and management?
>
This one is easy. Option 1.
David|||I'm with all the others. Assume a single set of tables and a single
database for all users. Presumably the User ID will be part of the key
in these tables.
Throughout you've described your DB as "large" without telling us the
actual size in bytes. SQL Server can handle many terabytes of data. The
best strategy is to design your logical model first (logically you put
like data in a single table, not partitioned across multiple tables)
then estimate sizes and tune performance when you are ready to create
physical databases and test-data.
--
David Portas
SQL Server MVP
--|||Thanks All,
After your opinions, I'll definitely use option 1. The data is not really
huge, but our server machine is not very good and a little difficult to
manage each user's data.
Best regards,
Kevin
"Kevin" <Kevin@.NoSpam.home.nl> wrote in message
news:%23Wtc6i2JFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I'm trying to design a large DB and considering which direction to go.
> The planned login user accounts will be 10,000, and each user has 15 DB
> Tables. Each Table's structure similar to other users, but user's data
> records have no relation with each other.
> 1. Create 1 Database for all users, and an User_Login Table containing
> User_ID, and 15 other Tables each containing the related User_ID.
> This has 15 + 1 Tables in 1 Database, but each Table could be huge (10,000
> X each user's data record).
> 2. Create 1 Database for each user.
> This is easier to manage each user, but there'll be 10,000 Databases.
> 3. Create 1 Database for all users, and 15 Tables for each user.
> This could result a DB of 15 X 10,000 Tables.
> I also have to consider how to manage each user's data, i.e, backup, limit
> size, delete outdated records ...
> Please enlighten me how to design such a DB. Is there an optimalized
> structure for both programming and management?
>
> Best regards,
> Kevin
>|||"Kevin" <Kevin@.NoSpam.home.nl> schrieb im Newsbeitrag
news:OCQefgEKFHA.2716@.TK2MSFTNGP15.phx.gbl...
> Thanks All,
> After your opinions, I'll definitely use option 1. The data is not
really
> huge, but our server machine is not very good and a little difficult to
> manage each user's data.
Then definitely use option 1. Note, that you might not need to put the
user id into all tables. For example, if one of the main table is a
contact table and each contact has n adresses (sitting in another table)
then you'll have a contact id which is used for joining the adresses of
this contact. Then only the contacts table needs a user id. Or you have
an additional table that has user id, contact id and no user id is needed
in the contact table. But that really depends on the nature of your data
and what you want to do with it.
Kind regards
robert