Grant Execute!

Hi all,
One of my workmates has acciddently changed the EXECUTE permissions for my
main login.
Is their a System stored procedure or something i can do to give Execute
permissions on all stored procedures in my DB
to a particular user, without having to do each procedure individually.
Cheers,
AdamIf you have SQL Server 2005, you can GRANT EXECUTE on a schema. If you have
SQL 2000, you'd have to do the GRANT's separately. Ideally, you should
grant only to a role and then add users to the role.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
news:uMljqe%23PGHA.2888@.tk2msftngp13.phx.gbl...
Hi all,
One of my workmates has acciddently changed the EXECUTE permissions for my
main login.
Is their a System stored procedure or something i can do to give Execute
permissions on all stored procedures in my DB
to a particular user, without having to do each procedure individually.
Cheers,
Adam|||Hi,
http://www.codeproject.com/database/T-SQL.asp
HTH, Jens Suessmeyer.|||Run this in the database, then copy the results to the query window
and execute.
select 'Grant EXEC on ' + name + ' to WhomEver'
from sysobjects
where type = 'P'
Roy Harvey
Beacon Falls, CT
On Sun, 5 Mar 2006 09:59:09 +1000, "Mr Ideas Man"
<adam@.pertrain.com.au> wrote:

>Hi all,
>One of my workmates has acciddently changed the EXECUTE permissions for my
>main login.
>Is their a System stored procedure or something i can do to give Execute
>permissions on all stored procedures in my DB
>to a particular user, without having to do each procedure individually.
>Cheers,
>Adam
>

Got Microsoft OLE DB Provider for ODBC Drivers error '80004005' when running Stored Procedures

when i am running a Stored Procedures, system always returns me error message below and Stored Procedures stops. please help

Microsoft OLE DB Provider for ODBC Drivers error

'80004005'

[Microsoft][ODBC SQL Server Driver] Received an unrecognized datatype 0 from

TDS data stream

sometime it returns error messge like, TDS Buffer Length Too Large
or
Unknown token received from SQL Server

or
Protocol error in TDS stream
or
Bad token from SQL Server: Datastream processing out of sync.
or
Invalid cursor state
or
TDS Buffer Link Too Large
or
Function sequence error

Many thanks, Please help, appreciated

Can you post more information about the configuration:

- What version of SQL Server do you use?

- What OLEDB provider do you use (e.g., SQLOLEDB, SQL Native Client?)?

- What is the definition of relevan tables?

- Code for the stored procedure and for the application calling it (omit any confidential information)?

|||

Thanks Peter:

it's SQL Server 2000 sp4, this problem happens on the OLEDB provider ODBC driver. the detials of sproc is that there is one sproc calling other 4 different sprocs to update 4 different table (about 30,000 columns need to be updated in each table). and it will run only once a month automaticly by Job.

I also tried to run those 4 sprocs separately and manually, still get the same problem, somehow.

cheers

|||anyone can help?|||

Could be a network performance or configuration error.

http://support.microsoft.com/default.aspx/kb/176256

Got Microsoft OLE DB Provider for ODBC Drivers error '80004005' when running Stored Procedures

when i am running a Stored Procedures, system always returns me error message below and Stored Procedures stops. please help

Microsoft OLE DB Provider for ODBC Drivers error

'80004005'

[Microsoft][ODBC SQL Server Driver] Received an unrecognized datatype 0 from

TDS data stream

sometime it returns error messge like, TDS Buffer Length Too Large
or
Unknown token received from SQL Server

or
Protocol error in TDS stream
or
Bad token from SQL Server: Datastream processing out of sync.
or
Invalid cursor state
or
TDS Buffer Link Too Large
or
Function sequence error

Many thanks, Please help, appreciated

Can you post more information about the configuration:

- What version of SQL Server do you use?

- What OLEDB provider do you use (e.g., SQLOLEDB, SQL Native Client?)?

- What is the definition of relevan tables?

- Code for the stored procedure and for the application calling it (omit any confidential information)?

|||

Thanks Peter:

it's SQL Server 2000 sp4, this problem happens on the OLEDB provider ODBC driver. the detials of sproc is that there is one sproc calling other 4 different sprocs to update 4 different table (about 30,000 columns need to be updated in each table). and it will run only once a month automaticly by Job.

I also tried to run those 4 sprocs separately and manually, still get the same problem, somehow.

cheers

|||anyone can help?|||

Could be a network performance or configuration error.

http://support.microsoft.com/default.aspx/kb/176256

Good sql server programming tool

Hi,
Are there any commercial tools available that allow easy management of sql
server objects? Intellisense, object grouping, hiding system objects etc.?
Thanks
Nick
In article <cfdaep$jpb$1@.phys-news-1.nl.colt.net>,=20
Nick.DELETETHISStansbury@.Sage-Partners.com says...
> Hi,
> Are there any commercial tools available that allow easy management of =
sql
> server objects? Intellisense, object grouping, hiding system objects etc.=
?
>=20
> Thanks
>=20
> Nick
>=20
>=20
>=20
Nick,
We have an IDE designed specifically for the development of SQL code=20
objects that is currently in closed beta. Features include source=20
control, logical grouping, intellisense and much more. =20
We are still accepting select sites to participate in our beta testing,=20
and offer free licenses to participating sites that aggressively utilize=20
the product during the beta phase and provide us quality feedback. =A0If=20
you are interested, please send details regarding your testing=20
environment to info@.perfectionedge.com.
Best regards,
David Barber
Perfection Edge
http://www.perfectionedge.com
|||Nick Stansbury wrote:
> Hi,
> Are there any commercial tools available that allow easy management
> of sql server objects? Intellisense, object grouping, hiding system
> objects etc.?
> Thanks
> Nick
Try Speed IDE from Imceda over at http://www.imceda.com.
David G.

Good reasons for 'sa' and service accounts passwords to be control

By other than the DBA. We have a security group with our org that wants to
control sa and system accounts for SQL Servers. We're desparately trying to
hold on to our control of these, but need to justify with the business unit.
Anyone got a really great reason not to let a non-DBA control these?
"David T." <DavidT@.discussions.microsoft.com> wrote in message
news:2A6FC7A8-EE65-4DBA-86A2-78C1DD836333@.microsoft.com...
> By other than the DBA. We have a security group with our org that wants
> to
> control sa and system accounts for SQL Servers. We're desparately trying
> to
> hold on to our control of these, but need to justify with the business
> unit.
> Anyone got a really great reason not to let a non-DBA control these?
No, I mostly agree. Accounts and passwords which are shared among groups of
people are inherently insecure. SA should be disabled, and the passwords
for fixed service accounts should be centrally and closely controlled. In
the normal course of things, people should connect with windows integrated
authentication and service accounts should be managed centrally.
But, and this is a _big_ but, a DBA should be a local administrator of any
database server and have sysadmin fixed server role. This will give a DBA
the ability, in a pinch, to reset passwords change service accounts and do
whatever is necessary to react in a "data emergency".
David

Good reasons for 'sa' and service accounts passwords to be control

By other than the DBA. We have a security group with our org that wants to
control sa and system accounts for SQL Servers. We're desparately trying to
hold on to our control of these, but need to justify with the business unit.
Anyone got a really great reason not to let a non-DBA control these?"David T." <DavidT@.discussions.microsoft.com> wrote in message
news:2A6FC7A8-EE65-4DBA-86A2-78C1DD836333@.microsoft.com...
> By other than the DBA. We have a security group with our org that wants
> to
> control sa and system accounts for SQL Servers. We're desparately trying
> to
> hold on to our control of these, but need to justify with the business
> unit.
> Anyone got a really great reason not to let a non-DBA control these?
No, I mostly agree. Accounts and passwords which are shared among groups of
people are inherently insecure. SA should be disabled, and the passwords
for fixed service accounts should be centrally and closely controlled. In
the normal course of things, people should connect with windows integrated
authentication and service accounts should be managed centrally.
But, and this is a _big_ but, a DBA should be a local administrator of any
database server and have sysadmin fixed server role. This will give a DBA
the ability, in a pinch, to reset passwords change service accounts and do
whatever is necessary to react in a "data emergency".
David

Good reasons for 'sa' and service accounts passwords to be control

By other than the DBA. We have a security group with our org that wants to
control sa and system accounts for SQL Servers. We're desparately trying to
hold on to our control of these, but need to justify with the business unit.
Anyone got a really great reason not to let a non-DBA control these?"David T." <DavidT@.discussions.microsoft.com> wrote in message
news:2A6FC7A8-EE65-4DBA-86A2-78C1DD836333@.microsoft.com...
> By other than the DBA. We have a security group with our org that wants
> to
> control sa and system accounts for SQL Servers. We're desparately trying
> to
> hold on to our control of these, but need to justify with the business
> unit.
> Anyone got a really great reason not to let a non-DBA control these?
No, I mostly agree. Accounts and passwords which are shared among groups of
people are inherently insecure. SA should be disabled, and the passwords
for fixed service accounts should be centrally and closely controlled. In
the normal course of things, people should connect with windows integrated
authentication and service accounts should be managed centrally.
But, and this is a _big_ but, a DBA should be a local administrator of any
database server and have sysadmin fixed server role. This will give a DBA
the ability, in a pinch, to reset passwords change service accounts and do
whatever is necessary to react in a "data emergency".
David

Going from 5M to 450M transactions a month

Hi,
We have developped a online web stats system. It currently has about 5
millions transactions per month. A customer wondered if we could support as
much as 450 millions transactions a month.
Obviously, a single server with a 1.5 Ghz dual-core processor and 2 Go RAM
will crash under this workload. But what could support that kind of workload?
Can SQL server basically support that? If so, what kind of hardware strategy
would be the best? Replication? One big fat server with 16 CPU and some
terabytes of RAM?
I'm looking for infos, thoughts, hints or articles to read about how to set
up a SQL server to support a huge workload.
Any idea?
Thanks
Stephane
1) Yes, SQL Server can support 450M xactions per month. See www.tpc.org and
their tpc-c benchmark.
http://www.tpc.org/tpcc/results/tpcc_perf_results.asp will show that sql
server (number 7 on the list) was able to scale to 1.2M transactions PER
MINUTE. Note that this was a $6M setup. However, 450M per month is about
10500 xactions/min running 24/7. Per this link -
http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=107031201, for
$63000 you can build a system that will process 70K xactions/min steady
state. The tpc-c transaction definition is fairly complex too.
2) Replication is usually not associated with performance. Just the
opposite.
3) You should hire an experienced person or company to spec, setup, install,
configure a mongo server for you. Make sure it is an entity that has
experience with very large systems. To do less will be wasting both time
and money and you will most likely still not get a performanct system.
4) Terabytes of RAM aren't achievable on sql server just yet. Tens+ of GB
is pretty much it AFAIK. What will really matter is the ability to get data
from disk (or the network for a web/streaming app) and into the CPUs VERY
quickly. You will also need a very optimized data structure, data access
mechanisms, index strategy and routine maintenance.
TheSQLGuru
President
Indicium Resources, Inc.
"Stephane" <Stephane@.discussions.microsoft.com> wrote in message
news:3712B71E-0A3A-40FA-929D-8C6074720204@.microsoft.com...
> Hi,
> We have developped a online web stats system. It currently has about 5
> millions transactions per month. A customer wondered if we could support
> as
> much as 450 millions transactions a month.
> Obviously, a single server with a 1.5 Ghz dual-core processor and 2 Go RAM
> will crash under this workload. But what could support that kind of
> workload?
> Can SQL server basically support that? If so, what kind of hardware
> strategy
> would be the best? Replication? One big fat server with 16 CPU and some
> terabytes of RAM?
> I'm looking for infos, thoughts, hints or articles to read about how to
> set
> up a SQL server to support a huge workload.
> Any idea?
> Thanks
> Stephane
|||Or, you can setup an active/active cluster and layout the tables using
DPV's.
"Stephane" <Stephane@.discussions.microsoft.com> wrote in message
news:3712B71E-0A3A-40FA-929D-8C6074720204@.microsoft.com...
> Hi,
> We have developped a online web stats system. It currently has about 5
> millions transactions per month. A customer wondered if we could support
> as
> much as 450 millions transactions a month.
> Obviously, a single server with a 1.5 Ghz dual-core processor and 2 Go RAM
> will crash under this workload. But what could support that kind of
> workload?
> Can SQL server basically support that? If so, what kind of hardware
> strategy
> would be the best? Replication? One big fat server with 16 CPU and some
> terabytes of RAM?
> I'm looking for infos, thoughts, hints or articles to read about how to
> set
> up a SQL server to support a huge workload.
> Any idea?
> Thanks
> Stephane
|||DPV's?
"Jay" wrote:

> Or, you can setup an active/active cluster and layout the tables using
> DPV's.
> "Stephane" <Stephane@.discussions.microsoft.com> wrote in message
> news:3712B71E-0A3A-40FA-929D-8C6074720204@.microsoft.com...
>
>
|||Hi,
Your answer is really helpful. Thanks a lot.
As I can understand, for less than 50K$, it's not really possible to manage
that kind of workload.
What could be the maximum worload for a scale up server? Let's say, for less
than 10K$?
Thanks
Stephane
"TheSQLGuru" wrote:

> 1) Yes, SQL Server can support 450M xactions per month. See www.tpc.org and
> their tpc-c benchmark.
> http://www.tpc.org/tpcc/results/tpcc_perf_results.asp will show that sql
> server (number 7 on the list) was able to scale to 1.2M transactions PER
> MINUTE. Note that this was a $6M setup. However, 450M per month is about
> 10500 xactions/min running 24/7. Per this link -
> http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=107031201, for
> $63000 you can build a system that will process 70K xactions/min steady
> state. The tpc-c transaction definition is fairly complex too.
> 2) Replication is usually not associated with performance. Just the
> opposite.
> 3) You should hire an experienced person or company to spec, setup, install,
> configure a mongo server for you. Make sure it is an entity that has
> experience with very large systems. To do less will be wasting both time
> and money and you will most likely still not get a performanct system.
> 4) Terabytes of RAM aren't achievable on sql server just yet. Tens+ of GB
> is pretty much it AFAIK. What will really matter is the ability to get data
> from disk (or the network for a web/streaming app) and into the CPUs VERY
> quickly. You will also need a very optimized data structure, data access
> mechanisms, index strategy and routine maintenance.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Stephane" <Stephane@.discussions.microsoft.com> wrote in message
> news:3712B71E-0A3A-40FA-929D-8C6074720204@.microsoft.com...
>
>
|||On Wed, 15 Aug 2007 16:08:01 -0700, Stephane
<Stephane@.discussions.microsoft.com> wrote:

>As I can understand, for less than 50K$, it's not really possible to manage
>that kind of workload.
>What could be the maximum worload for a scale up server? Let's say, for less
>than 10K$?
Only you have the most important source of information for those sorts
of question, the existing server. Analyze the load that 5M
transactions is placing on the server and how well it is dealing with
it. That should include analysis of peak load, not average over a
month. It should include analysis of CPU, memory and disk usage. Try
to identify the bottlenecks and estimate the headroom. Much of this
can be learned by using Performance Monitor. With that information
you can better know where to put your money - CPU or disk are the
obvious choices after loading up with memory.
You may also find that the application and database design need
tweaking. At higher transaction volumes minor issues can become major
ones, and a minor optimization can have a significant payback.
Roy Harvey
Beacon Falls, CT
|||Distributed Partitioned Views.
You put part of a table on one server and another part on a different
server.
It's what you do to get a fully load balanced, fault tolerant (lost a
server) system.
One warning, it relies heavily on the DDL and is complicated to setup, but
with enough nodes, could support Google type traffic.
"Stephane" <Stephane@.discussions.microsoft.com> wrote in message
news:B0CC6354-66FB-4306-83B6-2B163F766CB5@.microsoft.com...[vbcol=seagreen]
> DPV's?
> "Jay" wrote:
|||> You may also find that the application and database design need
> tweaking. At higher transaction volumes minor issues can become major
> ones, and a minor optimization can have a significant payback.
Truer words are seldom spoken!
|||On Wed, 15 Aug 2007 10:34:04 -0700, Stephane
<Stephane@.discussions.microsoft.com> wrote:

>We have developped a online web stats system. It currently has about 5
>millions transactions per month. A customer wondered if we could support as
>much as 450 millions transactions a month.
Waitaminute, what do you mean by "stats system"? Does your server
*do* 5m trx/month, or does your server *analyze* the records of 5m
trx/month? If the later, that's a very different thing!
J.

Going from 5M to 450M transactions a month

Hi,
We have developped a online web stats system. It currently has about 5
millions transactions per month. A customer wondered if we could support as
much as 450 millions transactions a month.
Obviously, a single server with a 1.5 Ghz dual-core processor and 2 Go RAM
will crash under this workload. But what could support that kind of workload
?
Can SQL server basically support that? If so, what kind of hardware strategy
would be the best? Replication? One big fat server with 16 CPU and some
terabytes of RAM?
I'm looking for infos, thoughts, hints or articles to read about how to set
up a SQL server to support a huge workload.
Any idea?
Thanks
Stephane1) Yes, SQL Server can support 450M xactions per month. See www.tpc.org and
their tpc-c benchmark.
http://www.tpc.org/tpcc/results/tpcc_perf_results.asp will show that sql
server (number 7 on the list) was able to scale to 1.2M transactions PER
MINUTE. Note that this was a $6M setup. However, 450M per month is about
10500 xactions/min running 24/7. Per this link -
http://www.tpc.org/tpcc/results/tpc...p?id=107031201, for
$63000 you can build a system that will process 70K xactions/min steady
state. The tpc-c transaction definition is fairly complex too.
2) Replication is usually not associated with performance. Just the
opposite.
3) You should hire an experienced person or company to spec, setup, install,
configure a mongo server for you. Make sure it is an entity that has
experience with very large systems. To do less will be wasting both time
and money and you will most likely still not get a performanct system.
4) Terabytes of RAM aren't achievable on sql server just yet. Tens+ of GB
is pretty much it AFAIK. What will really matter is the ability to get data
from disk (or the network for a web/streaming app) and into the CPUs VERY
quickly. You will also need a very optimized data structure, data access
mechanisms, index strategy and routine maintenance.
TheSQLGuru
President
Indicium Resources, Inc.
"Stephane" <Stephane@.discussions.microsoft.com> wrote in message
news:3712B71E-0A3A-40FA-929D-8C6074720204@.microsoft.com...
> Hi,
> We have developped a online web stats system. It currently has about 5
> millions transactions per month. A customer wondered if we could support
> as
> much as 450 millions transactions a month.
> Obviously, a single server with a 1.5 Ghz dual-core processor and 2 Go RAM
> will crash under this workload. But what could support that kind of
> workload?
> Can SQL server basically support that? If so, what kind of hardware
> strategy
> would be the best? Replication? One big fat server with 16 CPU and some
> terabytes of RAM?
> I'm looking for infos, thoughts, hints or articles to read about how to
> set
> up a SQL server to support a huge workload.
> Any idea?
> Thanks
> Stephane|||Or, you can setup an active/active cluster and layout the tables using
DPV's.
"Stephane" <Stephane@.discussions.microsoft.com> wrote in message
news:3712B71E-0A3A-40FA-929D-8C6074720204@.microsoft.com...
> Hi,
> We have developped a online web stats system. It currently has about 5
> millions transactions per month. A customer wondered if we could support
> as
> much as 450 millions transactions a month.
> Obviously, a single server with a 1.5 Ghz dual-core processor and 2 Go RAM
> will crash under this workload. But what could support that kind of
> workload?
> Can SQL server basically support that? If so, what kind of hardware
> strategy
> would be the best? Replication? One big fat server with 16 CPU and some
> terabytes of RAM?
> I'm looking for infos, thoughts, hints or articles to read about how to
> set
> up a SQL server to support a huge workload.
> Any idea?
> Thanks
> Stephane|||DPV's?
"Jay" wrote:

> Or, you can setup an active/active cluster and layout the tables using
> DPV's.
> "Stephane" <Stephane@.discussions.microsoft.com> wrote in message
> news:3712B71E-0A3A-40FA-929D-8C6074720204@.microsoft.com...
>
>|||Hi,
Your answer is really helpful. Thanks a lot.
As I can understand, for less than 50K$, it's not really possible to manage
that kind of workload.
What could be the maximum worload for a scale up server? Let's say, for less
than 10K$?
Thanks
Stephane
"TheSQLGuru" wrote:

> 1) Yes, SQL Server can support 450M xactions per month. See www.tpc.org a
nd
> their tpc-c benchmark.
> http://www.tpc.org/tpcc/results/tpcc_perf_results.asp will show that sql
> server (number 7 on the list) was able to scale to 1.2M transactions PER
> MINUTE. Note that this was a $6M setup. However, 450M per month is about
> 10500 xactions/min running 24/7. Per this link -
> http://www.tpc.org/tpcc/results/tpc...p?id=107031201, for
> $63000 you can build a system that will process 70K xactions/min steady
> state. The tpc-c transaction definition is fairly complex too.
> 2) Replication is usually not associated with performance. Just the
> opposite.
> 3) You should hire an experienced person or company to spec, setup, instal
l,
> configure a mongo server for you. Make sure it is an entity that has
> experience with very large systems. To do less will be wasting both time
> and money and you will most likely still not get a performanct system.
> 4) Terabytes of RAM aren't achievable on sql server just yet. Tens+ of GB
> is pretty much it AFAIK. What will really matter is the ability to get da
ta
> from disk (or the network for a web/streaming app) and into the CPUs VERY
> quickly. You will also need a very optimized data structure, data access
> mechanisms, index strategy and routine maintenance.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Stephane" <Stephane@.discussions.microsoft.com> wrote in message
> news:3712B71E-0A3A-40FA-929D-8C6074720204@.microsoft.com...
>
>|||On Wed, 15 Aug 2007 16:08:01 -0700, Stephane
<Stephane@.discussions.microsoft.com> wrote:

>As I can understand, for less than 50K$, it's not really possible to manage
>that kind of workload.
>What could be the maximum worload for a scale up server? Let's say, for les
s
>than 10K$?
Only you have the most important source of information for those sorts
of question, the existing server. Analyze the load that 5M
transactions is placing on the server and how well it is dealing with
it. That should include analysis of peak load, not average over a
month. It should include analysis of CPU, memory and disk usage. Try
to identify the bottlenecks and estimate the headroom. Much of this
can be learned by using Performance Monitor. With that information
you can better know where to put your money - CPU or disk are the
obvious choices after loading up with memory.
You may also find that the application and database design need
tweaking. At higher transaction volumes minor issues can become major
ones, and a minor optimization can have a significant payback.
Roy Harvey
Beacon Falls, CT|||Distributed Partitioned Views.
You put part of a table on one server and another part on a different
server.
It's what you do to get a fully load balanced, fault tolerant (lost a
server) system.
One warning, it relies heavily on the DDL and is complicated to setup, but
with enough nodes, could support Google type traffic.
"Stephane" <Stephane@.discussions.microsoft.com> wrote in message
news:B0CC6354-66FB-4306-83B6-2B163F766CB5@.microsoft.com...[vbcol=seagreen]
> DPV's?
> "Jay" wrote:
>|||> You may also find that the application and database design need
> tweaking. At higher transaction volumes minor issues can become major
> ones, and a minor optimization can have a significant payback.
Truer words are seldom spoken!|||On Wed, 15 Aug 2007 10:34:04 -0700, Stephane
<Stephane@.discussions.microsoft.com> wrote:

>We have developped a online web stats system. It currently has about 5
>millions transactions per month. A customer wondered if we could support as
>much as 450 millions transactions a month.
Waitaminute, what do you mean by "stats system"? Does your server
*do* 5m trx/month, or does your server *analyze* the records of 5m
trx/month? If the later, that's a very different thing!
J.

Going from 5M to 450M transactions a month

Hi,
We have developped a online web stats system. It currently has about 5
millions transactions per month. A customer wondered if we could support as
much as 450 millions transactions a month.
Obviously, a single server with a 1.5 Ghz dual-core processor and 2 Go RAM
will crash under this workload. But what could support that kind of workload?
Can SQL server basically support that? If so, what kind of hardware strategy
would be the best? Replication? One big fat server with 16 CPU and some
terabytes of RAM?
I'm looking for infos, thoughts, hints or articles to read about how to set
up a SQL server to support a huge workload.
Any idea?
Thanks
Stephane1) Yes, SQL Server can support 450M xactions per month. See www.tpc.org and
their tpc-c benchmark.
http://www.tpc.org/tpcc/results/tpcc_perf_results.asp will show that sql
server (number 7 on the list) was able to scale to 1.2M transactions PER
MINUTE. Note that this was a $6M setup. However, 450M per month is about
10500 xactions/min running 24/7. Per this link -
http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=107031201, for
$63000 you can build a system that will process 70K xactions/min steady
state. The tpc-c transaction definition is fairly complex too.
2) Replication is usually not associated with performance. Just the
opposite.
3) You should hire an experienced person or company to spec, setup, install,
configure a mongo server for you. Make sure it is an entity that has
experience with very large systems. To do less will be wasting both time
and money and you will most likely still not get a performanct system.
4) Terabytes of RAM aren't achievable on sql server just yet. Tens+ of GB
is pretty much it AFAIK. What will really matter is the ability to get data
from disk (or the network for a web/streaming app) and into the CPUs VERY
quickly. You will also need a very optimized data structure, data access
mechanisms, index strategy and routine maintenance.
TheSQLGuru
President
Indicium Resources, Inc.
"Stephane" <Stephane@.discussions.microsoft.com> wrote in message
news:3712B71E-0A3A-40FA-929D-8C6074720204@.microsoft.com...
> Hi,
> We have developped a online web stats system. It currently has about 5
> millions transactions per month. A customer wondered if we could support
> as
> much as 450 millions transactions a month.
> Obviously, a single server with a 1.5 Ghz dual-core processor and 2 Go RAM
> will crash under this workload. But what could support that kind of
> workload?
> Can SQL server basically support that? If so, what kind of hardware
> strategy
> would be the best? Replication? One big fat server with 16 CPU and some
> terabytes of RAM?
> I'm looking for infos, thoughts, hints or articles to read about how to
> set
> up a SQL server to support a huge workload.
> Any idea?
> Thanks
> Stephane|||Or, you can setup an active/active cluster and layout the tables using
DPV's.
"Stephane" <Stephane@.discussions.microsoft.com> wrote in message
news:3712B71E-0A3A-40FA-929D-8C6074720204@.microsoft.com...
> Hi,
> We have developped a online web stats system. It currently has about 5
> millions transactions per month. A customer wondered if we could support
> as
> much as 450 millions transactions a month.
> Obviously, a single server with a 1.5 Ghz dual-core processor and 2 Go RAM
> will crash under this workload. But what could support that kind of
> workload?
> Can SQL server basically support that? If so, what kind of hardware
> strategy
> would be the best? Replication? One big fat server with 16 CPU and some
> terabytes of RAM?
> I'm looking for infos, thoughts, hints or articles to read about how to
> set
> up a SQL server to support a huge workload.
> Any idea?
> Thanks
> Stephane|||DPV's?
"Jay" wrote:
> Or, you can setup an active/active cluster and layout the tables using
> DPV's.
> "Stephane" <Stephane@.discussions.microsoft.com> wrote in message
> news:3712B71E-0A3A-40FA-929D-8C6074720204@.microsoft.com...
> > Hi,
> >
> > We have developped a online web stats system. It currently has about 5
> > millions transactions per month. A customer wondered if we could support
> > as
> > much as 450 millions transactions a month.
> >
> > Obviously, a single server with a 1.5 Ghz dual-core processor and 2 Go RAM
> > will crash under this workload. But what could support that kind of
> > workload?
> > Can SQL server basically support that? If so, what kind of hardware
> > strategy
> > would be the best? Replication? One big fat server with 16 CPU and some
> > terabytes of RAM?
> >
> > I'm looking for infos, thoughts, hints or articles to read about how to
> > set
> > up a SQL server to support a huge workload.
> >
> > Any idea?
> >
> > Thanks
> >
> > Stephane
>
>|||Hi,
Your answer is really helpful. Thanks a lot.
As I can understand, for less than 50K$, it's not really possible to manage
that kind of workload.
What could be the maximum worload for a scale up server? Let's say, for less
than 10K$?
Thanks
Stephane
"TheSQLGuru" wrote:
> 1) Yes, SQL Server can support 450M xactions per month. See www.tpc.org and
> their tpc-c benchmark.
> http://www.tpc.org/tpcc/results/tpcc_perf_results.asp will show that sql
> server (number 7 on the list) was able to scale to 1.2M transactions PER
> MINUTE. Note that this was a $6M setup. However, 450M per month is about
> 10500 xactions/min running 24/7. Per this link -
> http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=107031201, for
> $63000 you can build a system that will process 70K xactions/min steady
> state. The tpc-c transaction definition is fairly complex too.
> 2) Replication is usually not associated with performance. Just the
> opposite.
> 3) You should hire an experienced person or company to spec, setup, install,
> configure a mongo server for you. Make sure it is an entity that has
> experience with very large systems. To do less will be wasting both time
> and money and you will most likely still not get a performanct system.
> 4) Terabytes of RAM aren't achievable on sql server just yet. Tens+ of GB
> is pretty much it AFAIK. What will really matter is the ability to get data
> from disk (or the network for a web/streaming app) and into the CPUs VERY
> quickly. You will also need a very optimized data structure, data access
> mechanisms, index strategy and routine maintenance.
>
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Stephane" <Stephane@.discussions.microsoft.com> wrote in message
> news:3712B71E-0A3A-40FA-929D-8C6074720204@.microsoft.com...
> > Hi,
> >
> > We have developped a online web stats system. It currently has about 5
> > millions transactions per month. A customer wondered if we could support
> > as
> > much as 450 millions transactions a month.
> >
> > Obviously, a single server with a 1.5 Ghz dual-core processor and 2 Go RAM
> > will crash under this workload. But what could support that kind of
> > workload?
> > Can SQL server basically support that? If so, what kind of hardware
> > strategy
> > would be the best? Replication? One big fat server with 16 CPU and some
> > terabytes of RAM?
> >
> > I'm looking for infos, thoughts, hints or articles to read about how to
> > set
> > up a SQL server to support a huge workload.
> >
> > Any idea?
> >
> > Thanks
> >
> > Stephane
>
>|||On Wed, 15 Aug 2007 16:08:01 -0700, Stephane
<Stephane@.discussions.microsoft.com> wrote:
>As I can understand, for less than 50K$, it's not really possible to manage
>that kind of workload.
>What could be the maximum worload for a scale up server? Let's say, for less
>than 10K$?
Only you have the most important source of information for those sorts
of question, the existing server. Analyze the load that 5M
transactions is placing on the server and how well it is dealing with
it. That should include analysis of peak load, not average over a
month. It should include analysis of CPU, memory and disk usage. Try
to identify the bottlenecks and estimate the headroom. Much of this
can be learned by using Performance Monitor. With that information
you can better know where to put your money - CPU or disk are the
obvious choices after loading up with memory.
You may also find that the application and database design need
tweaking. At higher transaction volumes minor issues can become major
ones, and a minor optimization can have a significant payback.
Roy Harvey
Beacon Falls, CT|||Distributed Partitioned Views.
You put part of a table on one server and another part on a different
server.
It's what you do to get a fully load balanced, fault tolerant (lost a
server) system.
One warning, it relies heavily on the DDL and is complicated to setup, but
with enough nodes, could support Google type traffic.
"Stephane" <Stephane@.discussions.microsoft.com> wrote in message
news:B0CC6354-66FB-4306-83B6-2B163F766CB5@.microsoft.com...
> DPV's?
> "Jay" wrote:
>> Or, you can setup an active/active cluster and layout the tables using
>> DPV's.
>> "Stephane" <Stephane@.discussions.microsoft.com> wrote in message
>> news:3712B71E-0A3A-40FA-929D-8C6074720204@.microsoft.com...
>> > Hi,
>> >
>> > We have developped a online web stats system. It currently has about 5
>> > millions transactions per month. A customer wondered if we could
>> > support
>> > as
>> > much as 450 millions transactions a month.
>> >
>> > Obviously, a single server with a 1.5 Ghz dual-core processor and 2 Go
>> > RAM
>> > will crash under this workload. But what could support that kind of
>> > workload?
>> > Can SQL server basically support that? If so, what kind of hardware
>> > strategy
>> > would be the best? Replication? One big fat server with 16 CPU and some
>> > terabytes of RAM?
>> >
>> > I'm looking for infos, thoughts, hints or articles to read about how to
>> > set
>> > up a SQL server to support a huge workload.
>> >
>> > Any idea?
>> >
>> > Thanks
>> >
>> > Stephane
>>|||> You may also find that the application and database design need
> tweaking. At higher transaction volumes minor issues can become major
> ones, and a minor optimization can have a significant payback.
Truer words are seldom spoken!|||On Wed, 15 Aug 2007 10:34:04 -0700, Stephane
<Stephane@.discussions.microsoft.com> wrote:
>We have developped a online web stats system. It currently has about 5
>millions transactions per month. A customer wondered if we could support as
>much as 450 millions transactions a month.
Waitaminute, what do you mean by "stats system"? Does your server
*do* 5m trx/month, or does your server *analyze* the records of 5m
trx/month? If the later, that's a very different thing!
J.

Global prefixes available?

Our business system consists of 4 separate database. One of these is a
Reference database that stores common look up tables, such as Countries,
Provinces, States, etc. When we want to access this information from
another database, we always prefix the table with the db name, then the
owner, as in:
select * from Reference.dbo.Country
Since there is no Country table in any of the other databases, I was
wondering if there was a way to 'instruct' SQL to always look in the
Reference database for a table called 'Country'. This would make coding
the stored procedures (and working in Query Analyzer) a bit less verbose,
but I do NOT want to sacrifice performance. Plus, I don't even know if this
possible. One of the developers here mentioned that Oracle has global
prefixes, or constants or something like that, that allows you to define
this. So far, the only thing I've come up with is to create a view in each
of the other databases with the same name as the tables in Reference, where
the view is something like:
Create View Country As Select * from Reference.dbo.Country
Any thoughts?On Sun, 3 Apr 2005 21:19:55 -0600, Dave Slinn wrote:
(snip)
> select * from Reference.dbo.Country
>Since there is no Country table in any of the other databases, I was
>wondering if there was a way to 'instruct' SQL to always look in the
>Reference database for a table called 'Country'.
Hi Dave,
No, there is no such option in SQL Server.
Sidenote - even if there was, I'd probably recommend against using it,
since it would slow things down. Consider the well documented
performance difference between "SELECT somecol FROM sometable" and
"SELECT somecol FROM dbo.sometable". The first will check first for a
table called sometable owned by the current user before going to the
sometable table owned by dbo; the second will skip the first step.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Giving Active Queries Priority

Is there a way to give a particular query greater system resources,
such as share of the processor?

I have two queries that were started from Query Analyzer. One is very
long, the other rather short. However the long one is taking almost
all the processor resources and not allowing the short one to complete.
I'd like to allocate more of the processor to the short one until it
is done.

I am the admin on this system and have access to Enterprise Mgr.

Thanks.Hi

If you had multiple processors then you may want to look at the maxdop query
hint, or you may want to break your longer running query up so that other
activity can occur. You may also want to look at the query plan to see if
you can optimise the long running query or possibly re-architect the
database/system to remove the need for such intensive processing.

John

<sfarkas@.visa.com> wrote in message
news:1117592953.657773.238730@.o13g2000cwo.googlegr oups.com...
> Is there a way to give a particular query greater system resources,
> such as share of the processor?
> I have two queries that were started from Query Analyzer. One is very
> long, the other rather short. However the long one is taking almost
> all the processor resources and not allowing the short one to complete.
> I'd like to allocate more of the processor to the short one until it
> is done.
> I am the admin on this system and have access to Enterprise Mgr.
> Thanks.

giving a user the ability to add logins and users to a database

I need to give a user the ability to create logins and
then add that login to a database. Is it possible to do
this without granting System Administrator Role? I cannot
seem to do this any other way then giving the user
sysadmin. Any help would be greatly apperciated.
Thanks.Add their login to the securityadmin fixed server role and to the
db_accessadmin fixed database role.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Matt." <mattcioffi@.crd.com> wrote in message
news:2e2901c428a4$9d9aec20$a601280a@.phx.gbl...
I need to give a user the ability to create logins and
then add that login to a database. Is it possible to do
this without granting System Administrator Role? I cannot
seem to do this any other way then giving the user
sysadmin. Any help would be greatly apperciated.
Thanks.|||Members of the server role securityadmin can add logins,
grant access to Windows logins.
Members of the database roles db_owner and db_accessadmin
can grant access to the databases in which they are members
of that role.
-Sue
On Thu, 22 Apr 2004 13:01:35 -0700, "Matt."
<mattcioffi@.crd.com> wrote:

>I need to give a user the ability to create logins and
>then add that login to a database. Is it possible to do
>this without granting System Administrator Role? I cannot
>seem to do this any other way then giving the user
>sysadmin. Any help would be greatly apperciated.
>Thanks.|||This does not work. I just created a user gave it
security admin role on the server and then gave it both
accessadmin and securityadmin. I get permission denied
when I try to add the user.
Please help.
>--Original Message--
>Members of the server role securityadmin can add logins,
>grant access to Windows logins.
>Members of the database roles db_owner and db_accessadmin
>can grant access to the databases in which they are
members
>of that role.
>-Sue
>On Thu, 22 Apr 2004 13:01:35 -0700, "Matt."
><mattcioffi@.crd.com> wrote:
>
cannot[vbcol=seagreen]
>.
>|||It works. I'm not sure what you missed - double check the
databases where you wanted to grant the permissions for the
new user you created - make sure you were in the correct
database. Also double check the database where the new user
is trying to add users and make sure they are executing the
procedures in the correct database.
As an example, for SomeLogin to be able to add logins and
users you would use something like:
sp_addlogin 'SomeLogin', 'SomePassword'
or
sp_grantlogin 'SomeLogin'
go
sp_addsrvrolemember 'SomeLogin', 'securityadmin'
go
use YourDatabase
go
sp_grantdbaccess 'SomeLogin'
go
sp_addrolemember 'db_accessadmin', 'SomeLogin'
go
Then when SomeLogin logs into SQL Server, they can execute:
sp_addlogin 'NewUser', 'SomeOtherPassword'
or
sp_grantlogin 'NewUser'
go
use YourDatabase
go
sp_grantdbaccess 'NewUser'
go
-Sue
On Thu, 22 Apr 2004 14:03:40 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>This does not work. I just created a user gave it
>security admin role on the server and then gave it both
>accessadmin and securityadmin. I get permission denied
>when I try to add the user.
>Please help.
>members
>cannot|||I rechecked everything and it does not work. Are there
any server configurations or database options that need to
be set? I will delete the new user and login and try one
more time but I have tried everything and it still gets
Server: Msg 15247, Level 16, State 1, Procedure
sp_adduser, Line 35
User does not have permission to perform this action.
login has security administrator server role, the user in
the database has accessadmin, securityadmin, and at
various times other roles in the database like db_owner
and it still will not allow this to work.

>--Original Message--
>It works. I'm not sure what you missed - double check the
>databases where you wanted to grant the permissions for
the
>new user you created - make sure you were in the correct
>database. Also double check the database where the new
user
>is trying to add users and make sure they are executing
the
>procedures in the correct database.
>As an example, for SomeLogin to be able to add logins and
>users you would use something like:
>sp_addlogin 'SomeLogin', 'SomePassword'
>or
>sp_grantlogin 'SomeLogin'
>go
>sp_addsrvrolemember 'SomeLogin', 'securityadmin'
>go
>use YourDatabase
>go
>sp_grantdbaccess 'SomeLogin'
>go
>sp_addrolemember 'db_accessadmin', 'SomeLogin'
>go
>Then when SomeLogin logs into SQL Server, they can
execute:
>sp_addlogin 'NewUser', 'SomeOtherPassword'
>or
>sp_grantlogin 'NewUser'
>go
>use YourDatabase
>go
>sp_grantdbaccess 'NewUser'
>go
>-Sue
>On Thu, 22 Apr 2004 14:03:40 -0700,
><anonymous@.discussions.microsoft.com> wrote:
>
db_accessadmin[vbcol=seagreen]
do[vbcol=seagreen]
>.
>|||Sorry...don't know what else to suggest. There are no
special server or database configurations, options needed to
allow a member of db_accessadmin to execute
sp_grantdbaccess.
-Sue
On Mon, 26 Apr 2004 06:09:57 -0700, "Matt"
<mattcioffi@.crd.com> wrote:
[vbcol=seagreen]
>I rechecked everything and it does not work. Are there
>any server configurations or database options that need to
>be set? I will delete the new user and login and try one
>more time but I have tried everything and it still gets
>Server: Msg 15247, Level 16, State 1, Procedure
>sp_adduser, Line 35
>User does not have permission to perform this action.
>login has security administrator server role, the user in
>the database has accessadmin, securityadmin, and at
>various times other roles in the database like db_owner
>and it still will not allow this to work.
>
>
>the
>user
>the
>execute:
>db_accessadmin
>do