My company is currently setting up an application for a client that requires a Database server on the backend. We have selected SQL Server 2000 Enterprise as our DB but we are not sure how to size the hardware of the server. I have searched around and it appears that DB sizing is more experience/trial & error based than formula-based. I hope someone here can provide me with some advice if I provide the performance requirements of the server.
5 GB of data currently (will double in size every year) Approx. 10 million records (will double in size as well) 85,000 Transactions / hour 200 Concurrent users Client requires no greater than 1/2 second response time.
Questions:
How many CPU's should be needed? Why? How much RAM should be needed? Why?
RAID 10 was the recommended fault tolerance. Agree? Disagree?
Thanks in advance
JBAs you guessed there is no magical answer but here are some things to think about.
The actual size of the db is not too critical as long as you plan for growth and take into account how the drive arrays should be set up to achieve good performance. 85K per hour is less than 30 a second and although I wouldn't try that on a single processor box it is not too bad. (I routinely do over 800 a second with 4 Processors).
But since you need to handle Peak amounts and want less than 1 second response time you should be particularly aware of minimums. Ram will depend on how much of the data you actually use on a regular basis. You want to aim for enough ram to have all the data that you access on a regular basis in cache at one time leaving room for the procedure cache etc. Just because you have 5GB of data doesn't mean you will need 5GB of ram. You can always add ram if needed later just make sure you plan for growth ahead of time and save some ram slots so you don't have to throw away ram later to add more. It's always better to have more ram than not enough (don't forget to leave some for the OS too).
As for the hardwareI would shoot for the following:
RAID 1 (or 10) for the Log file(s).
RAID 5 or 10 for the data files (smaller and more disks vs less larger ones).
Raid 1 for the OS and SQL System files.
Depending on how much you will use tempdb (sorting etc) you may or may not want a separate Raid for tempdb.
If you do disk backups you may want to think about another RAID or 5 for the backups. In all cases make sure the arrays are expandable for future growth.
As for processors you will probably want to start with a 4 or 8 processor box with less than all the procs to begin with. The number of procs will depend on so many things but if you don't have enough you will probably see less than 1/2 second response time in peak loads while the rest of the time it will be fine. Poor code or schema is usually the reasons for needing more processors.
Andrewsql
Showing posts with label setting. Show all posts
Showing posts with label setting. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Database size question
I have a small application that I've developed using MSDE (2K). I'm
going to put this in to production by setting up a new computer and it
seems that Server 2005 Express edition will be adequate for our needs.
I want to check that the size of the databases I'm using are less than
4GB. How do I do that? I've looked around in SQL Server Enterprise
Manager but I don't see file sizes shown anywhere.
In Query Analyzer, try this:
EXECUTE sp_spaceused
In Enterprise Mangler, right click on the database, choose [Properties]. The
file size is in the middle of the page on the [General] tab,
Also, you can use Windows Explorer to view the database file. (It's not a
precise measure, but it's close.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Martin" <martinvalley@.comcast.net> wrote in message
news:2jvbl2h28n9ac4fc2usdi44g3kpa2gv40l@.4ax.com...
>I have a small application that I've developed using MSDE (2K). I'm
> going to put this in to production by setting up a new computer and it
> seems that Server 2005 Express edition will be adequate for our needs.
> I want to check that the size of the databases I'm using are less than
> 4GB. How do I do that? I've looked around in SQL Server Enterprise
> Manager but I don't see file sizes shown anywhere.
|||Thanks, Arnie - I don't know why I couldn't find that
My db's are all in the single-digit MB size, so I guess that the 4GB
limit of the Express version won't be an issue. And of course, if it
ever becomes an issue, we just have to upgrade.
Thanks again.
On Sat, 11 Nov 2006 09:10:03 -0800, "Arnie Rowland" <arnie@.1568.com>
wrote:
>In Query Analyzer, try this:
>EXECUTE sp_spaceused
>In Enterprise Mangler, right click on the database, choose [Properties]. The
>file size is in the middle of the page on the [General] tab,
>Also, you can use Windows Explorer to view the database file. (It's not a
>precise measure, but it's close.)
|||The maximum size of an MSDE database is 2GB so you can be sure your
databases are under 4GB
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Martin" <martinvalley@.comcast.net> wrote in message
news:2jvbl2h28n9ac4fc2usdi44g3kpa2gv40l@.4ax.com...
>I have a small application that I've developed using MSDE (2K). I'm
> going to put this in to production by setting up a new computer and it
> seems that Server 2005 Express edition will be adequate for our needs.
> I want to check that the size of the databases I'm using are less than
> 4GB. How do I do that? I've looked around in SQL Server Enterprise
> Manager but I don't see file sizes shown anywhere.
going to put this in to production by setting up a new computer and it
seems that Server 2005 Express edition will be adequate for our needs.
I want to check that the size of the databases I'm using are less than
4GB. How do I do that? I've looked around in SQL Server Enterprise
Manager but I don't see file sizes shown anywhere.
In Query Analyzer, try this:
EXECUTE sp_spaceused
In Enterprise Mangler, right click on the database, choose [Properties]. The
file size is in the middle of the page on the [General] tab,
Also, you can use Windows Explorer to view the database file. (It's not a
precise measure, but it's close.)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Martin" <martinvalley@.comcast.net> wrote in message
news:2jvbl2h28n9ac4fc2usdi44g3kpa2gv40l@.4ax.com...
>I have a small application that I've developed using MSDE (2K). I'm
> going to put this in to production by setting up a new computer and it
> seems that Server 2005 Express edition will be adequate for our needs.
> I want to check that the size of the databases I'm using are less than
> 4GB. How do I do that? I've looked around in SQL Server Enterprise
> Manager but I don't see file sizes shown anywhere.
|||Thanks, Arnie - I don't know why I couldn't find that
My db's are all in the single-digit MB size, so I guess that the 4GB
limit of the Express version won't be an issue. And of course, if it
ever becomes an issue, we just have to upgrade.
Thanks again.
On Sat, 11 Nov 2006 09:10:03 -0800, "Arnie Rowland" <arnie@.1568.com>
wrote:
>In Query Analyzer, try this:
>EXECUTE sp_spaceused
>In Enterprise Mangler, right click on the database, choose [Properties]. The
>file size is in the middle of the page on the [General] tab,
>Also, you can use Windows Explorer to view the database file. (It's not a
>precise measure, but it's close.)
|||The maximum size of an MSDE database is 2GB so you can be sure your
databases are under 4GB
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Martin" <martinvalley@.comcast.net> wrote in message
news:2jvbl2h28n9ac4fc2usdi44g3kpa2gv40l@.4ax.com...
>I have a small application that I've developed using MSDE (2K). I'm
> going to put this in to production by setting up a new computer and it
> seems that Server 2005 Express edition will be adequate for our needs.
> I want to check that the size of the databases I'm using are less than
> 4GB. How do I do that? I've looked around in SQL Server Enterprise
> Manager but I don't see file sizes shown anywhere.
Wednesday, March 21, 2012
Database Setting for text box and text area forms
I have a SQL Server database. The data from a table is populated in the table and can do a regular display query on a record without issue.
Problem is when I pull the data into a form the data doesn't show up in some form fields for editing.
I am building a backend for the manager to make updates and changes and this is vital. Does anyone know if it has something to do with a database setting or has had a similar issue in the past?
The reason I think its a database setting is becuase the same table converted into MS Access has no problem populating the text boxs and text areas.
Your help is much needed and appreciated.
Thanks.In case anyone is interested. You can solve this with a work around. Set a vaiable for the field item then use the response.write the variable to popluate the text box or text area.
Problem is when I pull the data into a form the data doesn't show up in some form fields for editing.
I am building a backend for the manager to make updates and changes and this is vital. Does anyone know if it has something to do with a database setting or has had a similar issue in the past?
The reason I think its a database setting is becuase the same table converted into MS Access has no problem populating the text boxs and text areas.
Your help is much needed and appreciated.
Thanks.In case anyone is interested. You can solve this with a work around. Set a vaiable for the field item then use the response.write the variable to popluate the text box or text area.
Database Setting - File Growth
Hi,
What will happen or what should a database administrator do if the setting
for file growth for a database and log file is not automatic growth?
Monitor free space and before nearing full increase the size of the relevant files (ALTER DATABASE
... MODIFY FILE...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Alice" <Alice@.discussions.microsoft.com> wrote in message
news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
> Hi,
> What will happen or what should a database administrator do if the setting
> for file growth for a database and log file is not automatic growth?
|||Hello,
Like Tibor mentioned you may need to monitor the MDF and LDF files closely
if the file size is not to set automatic growth.
But my best recommendation is create the MDF and LDF size with required size
and make sure that auto growth not happends as well as
you can make the auto growth option enabled. THis will help you not having
any downtime due to lack of database space...
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23PFEXm1qHHA.3492@.TK2MSFTNGP02.phx.gbl...
> Monitor free space and before nearing full increase the size of the
> relevant files (ALTER DATABASE ... MODIFY FILE...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Alice" <Alice@.discussions.microsoft.com> wrote in message
> news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
>
|||1) DO NOT EVER let a production database keep the default size and
especially growth increments for either the data OR log file!!! You will
quickly get extremely fragmented os files (for any reasonable amount of data
size) that will significantly affect performance of the database.
2) I advise my clients to estimate their data growth needs for at least 1
year out and set the database/log size appropriate for that to avoid any
growth and also to leave relatively large blocks of empty space so sql
server can lay data down contiguously (esp. during maintenance) - thus
leading to much better disk I/O performance.
3) Set autogrowth on and some reasonable MB or percentage growth factor
depending on size and growth patterns.
TheSQLGuru
President
Indicium Resources, Inc.
"Alice" <Alice@.discussions.microsoft.com> wrote in message
news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
> Hi,
> What will happen or what should a database administrator do if the setting
> for file growth for a database and log file is not automatic growth?
What will happen or what should a database administrator do if the setting
for file growth for a database and log file is not automatic growth?
Monitor free space and before nearing full increase the size of the relevant files (ALTER DATABASE
... MODIFY FILE...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Alice" <Alice@.discussions.microsoft.com> wrote in message
news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
> Hi,
> What will happen or what should a database administrator do if the setting
> for file growth for a database and log file is not automatic growth?
|||Hello,
Like Tibor mentioned you may need to monitor the MDF and LDF files closely
if the file size is not to set automatic growth.
But my best recommendation is create the MDF and LDF size with required size
and make sure that auto growth not happends as well as
you can make the auto growth option enabled. THis will help you not having
any downtime due to lack of database space...
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23PFEXm1qHHA.3492@.TK2MSFTNGP02.phx.gbl...
> Monitor free space and before nearing full increase the size of the
> relevant files (ALTER DATABASE ... MODIFY FILE...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Alice" <Alice@.discussions.microsoft.com> wrote in message
> news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
>
|||1) DO NOT EVER let a production database keep the default size and
especially growth increments for either the data OR log file!!! You will
quickly get extremely fragmented os files (for any reasonable amount of data
size) that will significantly affect performance of the database.
2) I advise my clients to estimate their data growth needs for at least 1
year out and set the database/log size appropriate for that to avoid any
growth and also to leave relatively large blocks of empty space so sql
server can lay data down contiguously (esp. during maintenance) - thus
leading to much better disk I/O performance.
3) Set autogrowth on and some reasonable MB or percentage growth factor
depending on size and growth patterns.
TheSQLGuru
President
Indicium Resources, Inc.
"Alice" <Alice@.discussions.microsoft.com> wrote in message
news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
> Hi,
> What will happen or what should a database administrator do if the setting
> for file growth for a database and log file is not automatic growth?
Database Setting - File Growth
Hi,
What will happen or what should a database administrator do if the setting
for file growth for a database and log file is not automatic growth?Monitor free space and before nearing full increase the size of the relevant files (ALTER DATABASE
... MODIFY FILE...).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Alice" <Alice@.discussions.microsoft.com> wrote in message
news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
> Hi,
> What will happen or what should a database administrator do if the setting
> for file growth for a database and log file is not automatic growth?|||Hello,
Like Tibor mentioned you may need to monitor the MDF and LDF files closely
if the file size is not to set automatic growth.
But my best recommendation is create the MDF and LDF size with required size
and make sure that auto growth not happends as well as
you can make the auto growth option enabled. THis will help you not having
any downtime due to lack of database space...
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23PFEXm1qHHA.3492@.TK2MSFTNGP02.phx.gbl...
> Monitor free space and before nearing full increase the size of the
> relevant files (ALTER DATABASE ... MODIFY FILE...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Alice" <Alice@.discussions.microsoft.com> wrote in message
> news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
>> Hi,
>> What will happen or what should a database administrator do if the
>> setting
>> for file growth for a database and log file is not automatic growth?
>|||1) DO NOT EVER let a production database keep the default size and
especially growth increments for either the data OR log file!!! You will
quickly get extremely fragmented os files (for any reasonable amount of data
size) that will significantly affect performance of the database.
2) I advise my clients to estimate their data growth needs for at least 1
year out and set the database/log size appropriate for that to avoid any
growth and also to leave relatively large blocks of empty space so sql
server can lay data down contiguously (esp. during maintenance) - thus
leading to much better disk I/O performance.
3) Set autogrowth on and some reasonable MB or percentage growth factor
depending on size and growth patterns.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Alice" <Alice@.discussions.microsoft.com> wrote in message
news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
> Hi,
> What will happen or what should a database administrator do if the setting
> for file growth for a database and log file is not automatic growth?
What will happen or what should a database administrator do if the setting
for file growth for a database and log file is not automatic growth?Monitor free space and before nearing full increase the size of the relevant files (ALTER DATABASE
... MODIFY FILE...).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Alice" <Alice@.discussions.microsoft.com> wrote in message
news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
> Hi,
> What will happen or what should a database administrator do if the setting
> for file growth for a database and log file is not automatic growth?|||Hello,
Like Tibor mentioned you may need to monitor the MDF and LDF files closely
if the file size is not to set automatic growth.
But my best recommendation is create the MDF and LDF size with required size
and make sure that auto growth not happends as well as
you can make the auto growth option enabled. THis will help you not having
any downtime due to lack of database space...
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23PFEXm1qHHA.3492@.TK2MSFTNGP02.phx.gbl...
> Monitor free space and before nearing full increase the size of the
> relevant files (ALTER DATABASE ... MODIFY FILE...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Alice" <Alice@.discussions.microsoft.com> wrote in message
> news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
>> Hi,
>> What will happen or what should a database administrator do if the
>> setting
>> for file growth for a database and log file is not automatic growth?
>|||1) DO NOT EVER let a production database keep the default size and
especially growth increments for either the data OR log file!!! You will
quickly get extremely fragmented os files (for any reasonable amount of data
size) that will significantly affect performance of the database.
2) I advise my clients to estimate their data growth needs for at least 1
year out and set the database/log size appropriate for that to avoid any
growth and also to leave relatively large blocks of empty space so sql
server can lay data down contiguously (esp. during maintenance) - thus
leading to much better disk I/O performance.
3) Set autogrowth on and some reasonable MB or percentage growth factor
depending on size and growth patterns.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Alice" <Alice@.discussions.microsoft.com> wrote in message
news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
> Hi,
> What will happen or what should a database administrator do if the setting
> for file growth for a database and log file is not automatic growth?
Database Setting - File Growth
Hi,
What will happen or what should a database administrator do if the setting
for file growth for a database and log file is not automatic growth?Monitor free space and before nearing full increase the size of the relevant
files (ALTER DATABASE
... MODIFY FILE...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Alice" <Alice@.discussions.microsoft.com> wrote in message
news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
> Hi,
> What will happen or what should a database administrator do if the setting
> for file growth for a database and log file is not automatic growth?|||Hello,
Like Tibor mentioned you may need to monitor the MDF and LDF files closely
if the file size is not to set automatic growth.
But my best recommendation is create the MDF and LDF size with required size
and make sure that auto growth not happends as well as
you can make the auto growth option enabled. THis will help you not having
any downtime due to lack of database space...
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23PFEXm1qHHA.3492@.TK2MSFTNGP02.phx.gbl...
> Monitor free space and before nearing full increase the size of the
> relevant files (ALTER DATABASE ... MODIFY FILE...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Alice" <Alice@.discussions.microsoft.com> wrote in message
> news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
>|||1) DO NOT EVER let a production database keep the default size and
especially growth increments for either the data OR log file!!! You will
quickly get extremely fragmented os files (for any reasonable amount of data
size) that will significantly affect performance of the database.
2) I advise my clients to estimate their data growth needs for at least 1
year out and set the database/log size appropriate for that to avoid any
growth and also to leave relatively large blocks of empty space so sql
server can lay data down contiguously (esp. during maintenance) - thus
leading to much better disk I/O performance.
3) Set autogrowth on and some reasonable MB or percentage growth factor
depending on size and growth patterns.
TheSQLGuru
President
Indicium Resources, Inc.
"Alice" <Alice@.discussions.microsoft.com> wrote in message
news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
> Hi,
> What will happen or what should a database administrator do if the setting
> for file growth for a database and log file is not automatic growth?
What will happen or what should a database administrator do if the setting
for file growth for a database and log file is not automatic growth?Monitor free space and before nearing full increase the size of the relevant
files (ALTER DATABASE
... MODIFY FILE...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Alice" <Alice@.discussions.microsoft.com> wrote in message
news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
> Hi,
> What will happen or what should a database administrator do if the setting
> for file growth for a database and log file is not automatic growth?|||Hello,
Like Tibor mentioned you may need to monitor the MDF and LDF files closely
if the file size is not to set automatic growth.
But my best recommendation is create the MDF and LDF size with required size
and make sure that auto growth not happends as well as
you can make the auto growth option enabled. THis will help you not having
any downtime due to lack of database space...
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23PFEXm1qHHA.3492@.TK2MSFTNGP02.phx.gbl...
> Monitor free space and before nearing full increase the size of the
> relevant files (ALTER DATABASE ... MODIFY FILE...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Alice" <Alice@.discussions.microsoft.com> wrote in message
> news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
>|||1) DO NOT EVER let a production database keep the default size and
especially growth increments for either the data OR log file!!! You will
quickly get extremely fragmented os files (for any reasonable amount of data
size) that will significantly affect performance of the database.
2) I advise my clients to estimate their data growth needs for at least 1
year out and set the database/log size appropriate for that to avoid any
growth and also to leave relatively large blocks of empty space so sql
server can lay data down contiguously (esp. during maintenance) - thus
leading to much better disk I/O performance.
3) Set autogrowth on and some reasonable MB or percentage growth factor
depending on size and growth patterns.
TheSQLGuru
President
Indicium Resources, Inc.
"Alice" <Alice@.discussions.microsoft.com> wrote in message
news:6329BF3B-A9E3-4F8A-989F-717103397012@.microsoft.com...
> Hi,
> What will happen or what should a database administrator do if the setting
> for file growth for a database and log file is not automatic growth?
Database setting
. The BPA recommend that the model database setting for the items below be set to on. I can accomplish this task through the query analyzer and run the set command. (Set ANSI_NULLS on). The response is positive but when I re-run the report the setting are back off.
Why?
QUOTED_IDENTIFIER
ANSI_NULLS
ANSI_WARNINGS
ANSI_PADDING
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULLDon't the setting only last for the scope of the session?
That's why they have to be coded inside the sporcs?
I'll have to look a more defenitive answer...but I'll just be fgetting from BOL|||Garry,
Those settings are only taking effect for that single session i.e. within Query Analyzer.
To make any permanent changes to the model database, you need to right click it in Query Analyzer and check the options under properties. You should save a copy of the database first in case you should find that you need to return to the default settings.
Please note carefully this article in case you need to reattach your model database:
http://support.microsoft.com/?id=224071
We would advise leaving the model database at default settings.
Use the following from Query Analyzer to check the settings:
Sp_helpdb
And also check for databaseproperty in Books Online
Syntax
DATABASEPROPERTY( database , property )
USE master
SELECT DATABASEPROPERTY('model', 'IsANSINullDEFAULT')|||Well its up to you, but most people would leave the model database alone. It depends on your particular needs more than anything else. Remember the model database is only a template used to create new databases, so if you are bringing databases to this machine from another server, then the model database settings will have no effect.
Any conflict here is due to ANSI compatibility levels. SQL Server does not always default to the ANSI compatible levels.
Please review this article for a note on the various database settings and their effects:-
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_03_6ohf.asp
These two commands give you information on your current connection details, and on the database settings that may be configured respectively:-
sp_dboption
dbcc useroptions|||why did you post the question if you already had the answer?|||Perhaps it was a rhetorical question?
Read the top of his posts. He was just copying information from MicroCoughed.sql
Why?
QUOTED_IDENTIFIER
ANSI_NULLS
ANSI_WARNINGS
ANSI_PADDING
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULLDon't the setting only last for the scope of the session?
That's why they have to be coded inside the sporcs?
I'll have to look a more defenitive answer...but I'll just be fgetting from BOL|||Garry,
Those settings are only taking effect for that single session i.e. within Query Analyzer.
To make any permanent changes to the model database, you need to right click it in Query Analyzer and check the options under properties. You should save a copy of the database first in case you should find that you need to return to the default settings.
Please note carefully this article in case you need to reattach your model database:
http://support.microsoft.com/?id=224071
We would advise leaving the model database at default settings.
Use the following from Query Analyzer to check the settings:
Sp_helpdb
And also check for databaseproperty in Books Online
Syntax
DATABASEPROPERTY( database , property )
USE master
SELECT DATABASEPROPERTY('model', 'IsANSINullDEFAULT')|||Well its up to you, but most people would leave the model database alone. It depends on your particular needs more than anything else. Remember the model database is only a template used to create new databases, so if you are bringing databases to this machine from another server, then the model database settings will have no effect.
Any conflict here is due to ANSI compatibility levels. SQL Server does not always default to the ANSI compatible levels.
Please review this article for a note on the various database settings and their effects:-
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_03_6ohf.asp
These two commands give you information on your current connection details, and on the database settings that may be configured respectively:-
sp_dboption
dbcc useroptions|||why did you post the question if you already had the answer?|||Perhaps it was a rhetorical question?
Read the top of his posts. He was just copying information from MicroCoughed.sql
Monday, March 19, 2012
Database security design with ASP.net and form-based authentication
I'm setting up an ASP.Net intranet application with a SQL Server 2000
database. The application uses form-based authentication which is supported
by the following tables: User, Role, UserRole (where each user is assigned
specific roles). The system will have several different roles and users can
belong to multiple roles. As an example, let's say I have the following
roles: data entry, guest/view only, admin, report viewer. I'm guessing now
the system will have about 20 unique users. I've figured out how to
implement the role-based part on ASP.Net, but I'm stuck trying to decide the
best way to secure my database tables and stored procedures.
We're on a Novell network, so I'm using SQL Server authentication. At it's
simplest, I could just have one login for my database and lock down all the
tables and stored procedures to that one login. I'd like to have the
security a little tighter, though, so that only users who belong to the
administrative role can access the administrative procedures, only data
entry members can access the data entry procedures, etc.
I've thought of the following scenarios, but none makes me happy:
1) Create a SQL Server login for each user of the application and assign
them to roles. Then lock the tables and procedures down to the appropriate
roles.
I don't want to do this because I want an administrative user to be able to
create new application users through the Web application. This wouldn't be
possible as I don't have rights to create new SQL Server logins. I'd have
to go to my DB Admin each time we want to add a new user, which isn't really
acceptable.
2) Use SQL application roles to secure tables and procedures. We've used
these in other applications, but I'd like to stay away from them since
connection pooling doesn't work with them.
3) Use a set number of SQL Logins for each pre-defined role (data entry,
guest, admin, report viewer) and grant those logins permission to tables and
procedures as appropriate. I think this is my favorite method right now,
but then I'm not sure how to manage the multiple usernames and passwords.
Where do I store them and how does the application decide which one to use?
This is where maybe this question is more appropriate in an ASP.Net group,
but I thought I'd try here first.
I'm wondering what other people have done in this scenario?
Thanks,
Diane Y.Since you already have forms-based security, why not use a single SQL login
for all database access?
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
news:OUiKBQwQGHA.5500@.TK2MSFTNGP12.phx.gbl...
> I'm setting up an ASP.Net intranet application with a SQL Server 2000
> database. The application uses form-based authentication which is
> supported
> by the following tables: User, Role, UserRole (where each user is assigned
> specific roles). The system will have several different roles and users
> can
> belong to multiple roles. As an example, let's say I have the following
> roles: data entry, guest/view only, admin, report viewer. I'm guessing
> now
> the system will have about 20 unique users. I've figured out how to
> implement the role-based part on ASP.Net, but I'm stuck trying to decide
> the
> best way to secure my database tables and stored procedures.
> We're on a Novell network, so I'm using SQL Server authentication. At
> it's
> simplest, I could just have one login for my database and lock down all
> the
> tables and stored procedures to that one login. I'd like to have the
> security a little tighter, though, so that only users who belong to the
> administrative role can access the administrative procedures, only data
> entry members can access the data entry procedures, etc.
> I've thought of the following scenarios, but none makes me happy:
> 1) Create a SQL Server login for each user of the application and assign
> them to roles. Then lock the tables and procedures down to the
> appropriate
> roles.
> I don't want to do this because I want an administrative user to be able
> to
> create new application users through the Web application. This wouldn't
> be
> possible as I don't have rights to create new SQL Server logins. I'd have
> to go to my DB Admin each time we want to add a new user, which isn't
> really
> acceptable.
> 2) Use SQL application roles to secure tables and procedures. We've used
> these in other applications, but I'd like to stay away from them since
> connection pooling doesn't work with them.
> 3) Use a set number of SQL Logins for each pre-defined role (data entry,
> guest, admin, report viewer) and grant those logins permission to tables
> and
> procedures as appropriate. I think this is my favorite method right now,
> but then I'm not sure how to manage the multiple usernames and passwords.
> Where do I store them and how does the application decide which one to
> use?
> This is where maybe this question is more appropriate in an ASP.Net group,
> but I thought I'd try here first.
> I'm wondering what other people have done in this scenario?
> Thanks,
> Diane Y.
>|||That's actually the way I have it setup now and it's what I've mostly done
in the past. I just really liked how, when I used multiple application
roles, I was able to give only certain roles permission to certain stored
procedures. So, I was just wondering what others have done...
Diane
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eoNTQdyQGHA.5552@.TK2MSFTNGP10.phx.gbl...
> Since you already have forms-based security, why not use a single SQL
login
> for all database access?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
> news:OUiKBQwQGHA.5500@.TK2MSFTNGP12.phx.gbl...
assigned[vbcol=seagreen]
have[vbcol=seagreen]
used[vbcol=seagreen]
now,[vbcol=seagreen]
passwords.[vbcol=seagreen]
group,[vbcol=seagreen]
>|||> So, I was just wondering what others have done...
I usually opt for option #1 (individual logins/database role membership) for
intranet apps, . This allows SQL Server to control security from both
within and outside your application. Unfortunately, this isn't an option
for you due to the reasons you stated.
Application roles vs. role-based logins are similar approaches. These work
well when a user belongs to a single role so that you can use the same
security context for a given user's database access. However, this method
is problematic in your case because a user can belong to multiple roles
(cumulative permissions). The difficult question is how you decide which
database security context to enable when a user belongs to multiple roles
and multiple roles are associated with a particular application feature.
For example, if user Mary belongs to both DataEntry and ReportViewer roles
and your security is such that either role can view a report, which role
should be used as the database security context?
As long as you can define your business rules for identifying the
appropriate database security context, the implementation is easy. All you
need to do is store the application role name or login along with the
password (encrypted) in your Role table. You can then use that for database
access.
IMHO, the single login approach is best in your situation since you don't
want DBA involvement for security administration.
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
news:e8WPEH5QGHA.2300@.TK2MSFTNGP11.phx.gbl...
> That's actually the way I have it setup now and it's what I've mostly done
> in the past. I just really liked how, when I used multiple application
> roles, I was able to give only certain roles permission to certain stored
> procedures. So, I was just wondering what others have done...
> Diane
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eoNTQdyQGHA.5552@.TK2MSFTNGP10.phx.gbl...
> login
> assigned
> have
> used
> now,
> passwords.
> group,
>
database. The application uses form-based authentication which is supported
by the following tables: User, Role, UserRole (where each user is assigned
specific roles). The system will have several different roles and users can
belong to multiple roles. As an example, let's say I have the following
roles: data entry, guest/view only, admin, report viewer. I'm guessing now
the system will have about 20 unique users. I've figured out how to
implement the role-based part on ASP.Net, but I'm stuck trying to decide the
best way to secure my database tables and stored procedures.
We're on a Novell network, so I'm using SQL Server authentication. At it's
simplest, I could just have one login for my database and lock down all the
tables and stored procedures to that one login. I'd like to have the
security a little tighter, though, so that only users who belong to the
administrative role can access the administrative procedures, only data
entry members can access the data entry procedures, etc.
I've thought of the following scenarios, but none makes me happy:
1) Create a SQL Server login for each user of the application and assign
them to roles. Then lock the tables and procedures down to the appropriate
roles.
I don't want to do this because I want an administrative user to be able to
create new application users through the Web application. This wouldn't be
possible as I don't have rights to create new SQL Server logins. I'd have
to go to my DB Admin each time we want to add a new user, which isn't really
acceptable.
2) Use SQL application roles to secure tables and procedures. We've used
these in other applications, but I'd like to stay away from them since
connection pooling doesn't work with them.
3) Use a set number of SQL Logins for each pre-defined role (data entry,
guest, admin, report viewer) and grant those logins permission to tables and
procedures as appropriate. I think this is my favorite method right now,
but then I'm not sure how to manage the multiple usernames and passwords.
Where do I store them and how does the application decide which one to use?
This is where maybe this question is more appropriate in an ASP.Net group,
but I thought I'd try here first.
I'm wondering what other people have done in this scenario?
Thanks,
Diane Y.Since you already have forms-based security, why not use a single SQL login
for all database access?
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
news:OUiKBQwQGHA.5500@.TK2MSFTNGP12.phx.gbl...
> I'm setting up an ASP.Net intranet application with a SQL Server 2000
> database. The application uses form-based authentication which is
> supported
> by the following tables: User, Role, UserRole (where each user is assigned
> specific roles). The system will have several different roles and users
> can
> belong to multiple roles. As an example, let's say I have the following
> roles: data entry, guest/view only, admin, report viewer. I'm guessing
> now
> the system will have about 20 unique users. I've figured out how to
> implement the role-based part on ASP.Net, but I'm stuck trying to decide
> the
> best way to secure my database tables and stored procedures.
> We're on a Novell network, so I'm using SQL Server authentication. At
> it's
> simplest, I could just have one login for my database and lock down all
> the
> tables and stored procedures to that one login. I'd like to have the
> security a little tighter, though, so that only users who belong to the
> administrative role can access the administrative procedures, only data
> entry members can access the data entry procedures, etc.
> I've thought of the following scenarios, but none makes me happy:
> 1) Create a SQL Server login for each user of the application and assign
> them to roles. Then lock the tables and procedures down to the
> appropriate
> roles.
> I don't want to do this because I want an administrative user to be able
> to
> create new application users through the Web application. This wouldn't
> be
> possible as I don't have rights to create new SQL Server logins. I'd have
> to go to my DB Admin each time we want to add a new user, which isn't
> really
> acceptable.
> 2) Use SQL application roles to secure tables and procedures. We've used
> these in other applications, but I'd like to stay away from them since
> connection pooling doesn't work with them.
> 3) Use a set number of SQL Logins for each pre-defined role (data entry,
> guest, admin, report viewer) and grant those logins permission to tables
> and
> procedures as appropriate. I think this is my favorite method right now,
> but then I'm not sure how to manage the multiple usernames and passwords.
> Where do I store them and how does the application decide which one to
> use?
> This is where maybe this question is more appropriate in an ASP.Net group,
> but I thought I'd try here first.
> I'm wondering what other people have done in this scenario?
> Thanks,
> Diane Y.
>|||That's actually the way I have it setup now and it's what I've mostly done
in the past. I just really liked how, when I used multiple application
roles, I was able to give only certain roles permission to certain stored
procedures. So, I was just wondering what others have done...
Diane
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eoNTQdyQGHA.5552@.TK2MSFTNGP10.phx.gbl...
> Since you already have forms-based security, why not use a single SQL
login
> for all database access?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
> news:OUiKBQwQGHA.5500@.TK2MSFTNGP12.phx.gbl...
assigned[vbcol=seagreen]
have[vbcol=seagreen]
used[vbcol=seagreen]
now,[vbcol=seagreen]
passwords.[vbcol=seagreen]
group,[vbcol=seagreen]
>|||> So, I was just wondering what others have done...
I usually opt for option #1 (individual logins/database role membership) for
intranet apps, . This allows SQL Server to control security from both
within and outside your application. Unfortunately, this isn't an option
for you due to the reasons you stated.
Application roles vs. role-based logins are similar approaches. These work
well when a user belongs to a single role so that you can use the same
security context for a given user's database access. However, this method
is problematic in your case because a user can belong to multiple roles
(cumulative permissions). The difficult question is how you decide which
database security context to enable when a user belongs to multiple roles
and multiple roles are associated with a particular application feature.
For example, if user Mary belongs to both DataEntry and ReportViewer roles
and your security is such that either role can view a report, which role
should be used as the database security context?
As long as you can define your business rules for identifying the
appropriate database security context, the implementation is easy. All you
need to do is store the application role name or login along with the
password (encrypted) in your Role table. You can then use that for database
access.
IMHO, the single login approach is best in your situation since you don't
want DBA involvement for security administration.
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Y" <diane.yocom@.spam.seattle.gov> wrote in message
news:e8WPEH5QGHA.2300@.TK2MSFTNGP11.phx.gbl...
> That's actually the way I have it setup now and it's what I've mostly done
> in the past. I just really liked how, when I used multiple application
> roles, I was able to give only certain roles permission to certain stored
> procedures. So, I was just wondering what others have done...
> Diane
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eoNTQdyQGHA.5552@.TK2MSFTNGP10.phx.gbl...
> login
> assigned
> have
> used
> now,
> passwords.
> group,
>
Thursday, March 8, 2012
Database restore to new database
Hello all,
I am using SQLDMO to try and restore a database to a 'new' database. I am using the relocatefiles property and setting it correctly, but when I restore I always get the error:
"[Microsoft][ODBC SQL Server Driver][SQL Server]Logical file 'OLDDATABASENAME' is not part of database 'NEWDATABASENAME'. Use RESTORE FILELISTONLY to list the logical file names.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE DATABASE is terminating abnormally."
My code is set up like this:
restoreObject.RelocateFiles = "[oldDbName],[C:\newDbName.mdf],[oldDbName],[C:\newDbName.ldf]";
Any ideas? Can I assume the logical file is the same as the 'old' database name?
When I try and use the 'readfilelist' function to get the list of logical filenames, I always get empty results.
what are the logical filenames of your new database?
they must match the logical filenames from the backup.
hope this helps
|||you can use readfilelist function to read the logic file name
for example: your "oldDbName" may be "oldDbName_data" and "oldDbName_log"
but i found the function is failure in SQL SERVER 2005 EXPRESS, i don't know why....
Saturday, February 25, 2012
Database Replication
Hello all,
I am looking for a "beginners guide" on setting up the automatic (daily)
replication of a production database to an identical database on another
server. This second database is to be used for training purposes. This is
for MSSQL 2000.
I have a customer that wishes to do this, and rather than explain it all I
would rather point him to a guide that he can look at (pleanty of pictures
would be good) and then ask questions if necessary.
Does anyone know of such a thing that is accessible?
Thank you in advance.
Jon Hunt
IT Manager
You might want to direct your customer to this -
http://www.mssqlcity.com/Articles/Re...TR/SetupTR.htm
There is some nonsense in here, but its ok. This is for SQL 7, but it is
much the same for SQL 2000.
"Jon Hunt" <thisisnotmyaddress@.nospam.invalid> wrote in message
news:Xns9578844C8F373softworks@.158.152.254.254...
> Hello all,
> I am looking for a "beginners guide" on setting up the automatic (daily)
> replication of a production database to an identical database on another
> server. This second database is to be used for training purposes. This is
> for MSSQL 2000.
> I have a customer that wishes to do this, and rather than explain it all I
> would rather point him to a guide that he can look at (pleanty of pictures
> would be good) and then ask questions if necessary.
> Does anyone know of such a thing that is accessible?
> Thank you in advance.
> --
> Jon Hunt
> IT Manager
I am looking for a "beginners guide" on setting up the automatic (daily)
replication of a production database to an identical database on another
server. This second database is to be used for training purposes. This is
for MSSQL 2000.
I have a customer that wishes to do this, and rather than explain it all I
would rather point him to a guide that he can look at (pleanty of pictures
would be good) and then ask questions if necessary.
Does anyone know of such a thing that is accessible?
Thank you in advance.
Jon Hunt
IT Manager
You might want to direct your customer to this -
http://www.mssqlcity.com/Articles/Re...TR/SetupTR.htm
There is some nonsense in here, but its ok. This is for SQL 7, but it is
much the same for SQL 2000.
"Jon Hunt" <thisisnotmyaddress@.nospam.invalid> wrote in message
news:Xns9578844C8F373softworks@.158.152.254.254...
> Hello all,
> I am looking for a "beginners guide" on setting up the automatic (daily)
> replication of a production database to an identical database on another
> server. This second database is to be used for training purposes. This is
> for MSSQL 2000.
> I have a customer that wishes to do this, and rather than explain it all I
> would rather point him to a guide that he can look at (pleanty of pictures
> would be good) and then ask questions if necessary.
> Does anyone know of such a thing that is accessible?
> Thank you in advance.
> --
> Jon Hunt
> IT Manager
Tuesday, February 14, 2012
Database Options
What is the real difference between setting the database
options using A SET statement which applies to the current
connection only or specifying the setting as a database-
level default with ALTER DATABASE?
When the option is set at the database level, that is the default setting
for all new connections.
The Set statement overrides the DB setting however...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"van" <vandenm2@.yahoo.com> wrote in message
news:16abe01c4480f$e8afa130$a501280a@.phx.gbl...
> What is the real difference between setting the database
> options using A SET statement which applies to the current
> connection only or specifying the setting as a database-
> level default with ALTER DATABASE?
|||Well, one change is temporary for the current session, the other change is
permanent?
http://www.aspfaq.com/
(Reverse address to reply.)
"van" <vandenm2@.yahoo.com> wrote in message
news:16abe01c4480f$e8afa130$a501280a@.phx.gbl...
> What is the real difference between setting the database
> options using A SET statement which applies to the current
> connection only or specifying the setting as a database-
> level default with ALTER DATABASE?
options using A SET statement which applies to the current
connection only or specifying the setting as a database-
level default with ALTER DATABASE?
When the option is set at the database level, that is the default setting
for all new connections.
The Set statement overrides the DB setting however...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"van" <vandenm2@.yahoo.com> wrote in message
news:16abe01c4480f$e8afa130$a501280a@.phx.gbl...
> What is the real difference between setting the database
> options using A SET statement which applies to the current
> connection only or specifying the setting as a database-
> level default with ALTER DATABASE?
|||Well, one change is temporary for the current session, the other change is
permanent?
http://www.aspfaq.com/
(Reverse address to reply.)
"van" <vandenm2@.yahoo.com> wrote in message
news:16abe01c4480f$e8afa130$a501280a@.phx.gbl...
> What is the real difference between setting the database
> options using A SET statement which applies to the current
> connection only or specifying the setting as a database-
> level default with ALTER DATABASE?
Database Options
What is the real difference between setting the database
options using A SET statement which applies to the current
connection only or specifying the setting as a database-
level default with ALTER DATABASE?When the option is set at the database level, that is the default setting
for all new connections.
The Set statement overrides the DB setting however...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"van" <vandenm2@.yahoo.com> wrote in message
news:16abe01c4480f$e8afa130$a501280a@.phx
.gbl...
> What is the real difference between setting the database
> options using A SET statement which applies to the current
> connection only or specifying the setting as a database-
> level default with ALTER DATABASE?|||Well, one change is temporary for the current session, the other change is
permanent?
http://www.aspfaq.com/
(Reverse address to reply.)
"van" <vandenm2@.yahoo.com> wrote in message
news:16abe01c4480f$e8afa130$a501280a@.phx
.gbl...
> What is the real difference between setting the database
> options using A SET statement which applies to the current
> connection only or specifying the setting as a database-
> level default with ALTER DATABASE?
options using A SET statement which applies to the current
connection only or specifying the setting as a database-
level default with ALTER DATABASE?When the option is set at the database level, that is the default setting
for all new connections.
The Set statement overrides the DB setting however...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"van" <vandenm2@.yahoo.com> wrote in message
news:16abe01c4480f$e8afa130$a501280a@.phx
.gbl...
> What is the real difference between setting the database
> options using A SET statement which applies to the current
> connection only or specifying the setting as a database-
> level default with ALTER DATABASE?|||Well, one change is temporary for the current session, the other change is
permanent?
http://www.aspfaq.com/
(Reverse address to reply.)
"van" <vandenm2@.yahoo.com> wrote in message
news:16abe01c4480f$e8afa130$a501280a@.phx
.gbl...
> What is the real difference between setting the database
> options using A SET statement which applies to the current
> connection only or specifying the setting as a database-
> level default with ALTER DATABASE?
Database Options
What is the real difference between setting the database
options using A SET statement which applies to the current
connection only or specifying the setting as a database-
level default with ALTER DATABASE?When the option is set at the database level, that is the default setting
for all new connections.
The Set statement overrides the DB setting however...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"van" <vandenm2@.yahoo.com> wrote in message
news:16abe01c4480f$e8afa130$a501280a@.phx.gbl...
> What is the real difference between setting the database
> options using A SET statement which applies to the current
> connection only or specifying the setting as a database-
> level default with ALTER DATABASE?|||Well, one change is temporary for the current session, the other change is
permanent?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"van" <vandenm2@.yahoo.com> wrote in message
news:16abe01c4480f$e8afa130$a501280a@.phx.gbl...
> What is the real difference between setting the database
> options using A SET statement which applies to the current
> connection only or specifying the setting as a database-
> level default with ALTER DATABASE?
options using A SET statement which applies to the current
connection only or specifying the setting as a database-
level default with ALTER DATABASE?When the option is set at the database level, that is the default setting
for all new connections.
The Set statement overrides the DB setting however...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"van" <vandenm2@.yahoo.com> wrote in message
news:16abe01c4480f$e8afa130$a501280a@.phx.gbl...
> What is the real difference between setting the database
> options using A SET statement which applies to the current
> connection only or specifying the setting as a database-
> level default with ALTER DATABASE?|||Well, one change is temporary for the current session, the other change is
permanent?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"van" <vandenm2@.yahoo.com> wrote in message
news:16abe01c4480f$e8afa130$a501280a@.phx.gbl...
> What is the real difference between setting the database
> options using A SET statement which applies to the current
> connection only or specifying the setting as a database-
> level default with ALTER DATABASE?
Subscribe to:
Posts (Atom)