Does SQL release Ram when its needed elsewhere (like Exchange's store.exe)?
I have a server with 2.6 gigs of ram and SQL is using 1 GB idle.SQL will release RAM if the system starts paging. Otherwise, it will
continue to hold allocated memory.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Matt" <anonymous@.discussions.microsoft.com> wrote in message
news:BA71FDB6-DC93-43A1-9B47-B76DC485B5FF@.microsoft.com...
> Does SQL release Ram when its needed elsewhere (like Exchange's
store.exe)?
> I have a server with 2.6 gigs of ram and SQL is using 1 GB idle.|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:ey1xSF7$DHA.212@.TK2MSFTNGP12.phx.gbl...
> SQL will release RAM if the system starts paging. Otherwise, it will
> continue to hold allocated memory.
>
Just to follow up, this means that on machines which are not dedicated
database servers SQL Server should have an upper limit placed on its memory.
Just reserve 256M or 512M for Sql Server. If you reserve too little, then
you will have excessive physical IO. If you reserve more than enough, you
won't get any added benefit.
David
Showing posts with label store. Show all posts
Showing posts with label store. Show all posts
Tuesday, March 27, 2012
Does SQL release Ram when its needed
Does SQL release Ram when its needed elsewhere (like Exchange's store.exe)?
I have a server with 2.6 gigs of ram and SQL is using 1 GB idle.SQL will release RAM if the system starts paging. Otherwise, it will
continue to hold allocated memory.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Matt" <anonymous@.discussions.microsoft.com> wrote in message
news:BA71FDB6-DC93-43A1-9B47-B76DC485B5FF@.microsoft.com...
> Does SQL release Ram when its needed elsewhere (like Exchange's
store.exe)?
> I have a server with 2.6 gigs of ram and SQL is using 1 GB idle.|||Hi Matt,
Yes, SQL Server do release the memory when the OS asks
for. You can also tell SQL Server to use a fixed memory if
there are any other applications co-exist on the same Box.
Memory Architecture
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/architec/8_ar_sa_4rc5.asp
Server Memory Options
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_config_9zfy.asp
Regards
Thirumal
Sys Admin
www.sstil.com
>--Original Message--
>Does SQL release Ram when its needed elsewhere (like
Exchange's store.exe)?
>I have a server with 2.6 gigs of ram and SQL is using 1
GB idle.
>.
>|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:ey1xSF7$DHA.212@.TK2MSFTNGP12.phx.gbl...
> SQL will release RAM if the system starts paging. Otherwise, it will
> continue to hold allocated memory.
>
Just to follow up, this means that on machines which are not dedicated
database servers SQL Server should have an upper limit placed on its memory.
Just reserve 256M or 512M for Sql Server. If you reserve too little, then
you will have excessive physical IO. If you reserve more than enough, you
won't get any added benefit.
David
I have a server with 2.6 gigs of ram and SQL is using 1 GB idle.SQL will release RAM if the system starts paging. Otherwise, it will
continue to hold allocated memory.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Matt" <anonymous@.discussions.microsoft.com> wrote in message
news:BA71FDB6-DC93-43A1-9B47-B76DC485B5FF@.microsoft.com...
> Does SQL release Ram when its needed elsewhere (like Exchange's
store.exe)?
> I have a server with 2.6 gigs of ram and SQL is using 1 GB idle.|||Hi Matt,
Yes, SQL Server do release the memory when the OS asks
for. You can also tell SQL Server to use a fixed memory if
there are any other applications co-exist on the same Box.
Memory Architecture
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/architec/8_ar_sa_4rc5.asp
Server Memory Options
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_config_9zfy.asp
Regards
Thirumal
Sys Admin
www.sstil.com
>--Original Message--
>Does SQL release Ram when its needed elsewhere (like
Exchange's store.exe)?
>I have a server with 2.6 gigs of ram and SQL is using 1
GB idle.
>.
>|||"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:ey1xSF7$DHA.212@.TK2MSFTNGP12.phx.gbl...
> SQL will release RAM if the system starts paging. Otherwise, it will
> continue to hold allocated memory.
>
Just to follow up, this means that on machines which are not dedicated
database servers SQL Server should have an upper limit placed on its memory.
Just reserve 256M or 512M for Sql Server. If you reserve too little, then
you will have excessive physical IO. If you reserve more than enough, you
won't get any added benefit.
David
Wednesday, March 21, 2012
Does RAISERROR store values for @@ERROR?
I tried to write some test code that looks like:
DECLARE @.ErrorValue int
sp_addmessage 50001,10,'This is a test error message'
RAISERROR (50001,10,1)
SELECT @.ErrorValue = @.@.ERROR
If @.ErrorValue <> 0 ...etc.
I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
variable immediately after the line that raised the error. Instead, a zero
value was returned. Why?
If I try to replace that line with:
RAISERROR (-100,10,1)
...which generates a system error 2758 ("RAISERROR could not locate entry
for error -100 in sysmessages"), and @.ErrorValue is set to 2758, so I know
the code is working.
Does RAISERROR not set the value of @.@.ERROR?
Thanks!> I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
> variable immediately after the line that raised the error. Instead, a zero
> value was returned. Why?
The behavior is described completely in the documentation for raiserror.
You can use a different severity or override the behavior with an option.|||from BOL
"When an error is raised, the error number is placed in the @.@.ERROR
function, which stores the most recently generated error number. @.@.ERROR
is set to 0 by default for messages with a severity from 1 through 10."
Your second RAISERROR itself raised an error, which is what was returned
in @.@.ERROR, so that's not really a valid test.
Joel wrote:
> I tried to write some test code that looks like:
> DECLARE @.ErrorValue int
> sp_addmessage 50001,10,'This is a test error message'
> RAISERROR (50001,10,1)
> SELECT @.ErrorValue = @.@.ERROR
> If @.ErrorValue <> 0 ...etc.
> I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
> variable immediately after the line that raised the error. Instead, a zero
> value was returned. Why?
> If I try to replace that line with:
> RAISERROR (-100,10,1)
> ...which generates a system error 2758 ("RAISERROR could not locate entry
> for error -100 in sysmessages"), and @.ErrorValue is set to 2758, so I know
> the code is working.
> Does RAISERROR not set the value of @.@.ERROR?
> Thanks!|||Ahh... that worked. I just tried the same code with an error level of 12, an
d
it works fine. Thanks for the help!
"Trey Walpole" wrote:
> from BOL
> "When an error is raised, the error number is placed in the @.@.ERROR
> function, which stores the most recently generated error number. @.@.ERROR
> is set to 0 by default for messages with a severity from 1 through 10."
> Your second RAISERROR itself raised an error, which is what was returned
> in @.@.ERROR, so that's not really a valid test.
> Joel wrote:
>sql
DECLARE @.ErrorValue int
sp_addmessage 50001,10,'This is a test error message'
RAISERROR (50001,10,1)
SELECT @.ErrorValue = @.@.ERROR
If @.ErrorValue <> 0 ...etc.
I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
variable immediately after the line that raised the error. Instead, a zero
value was returned. Why?
If I try to replace that line with:
RAISERROR (-100,10,1)
...which generates a system error 2758 ("RAISERROR could not locate entry
for error -100 in sysmessages"), and @.ErrorValue is set to 2758, so I know
the code is working.
Does RAISERROR not set the value of @.@.ERROR?
Thanks!> I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
> variable immediately after the line that raised the error. Instead, a zero
> value was returned. Why?
The behavior is described completely in the documentation for raiserror.
You can use a different severity or override the behavior with an option.|||from BOL
"When an error is raised, the error number is placed in the @.@.ERROR
function, which stores the most recently generated error number. @.@.ERROR
is set to 0 by default for messages with a severity from 1 through 10."
Your second RAISERROR itself raised an error, which is what was returned
in @.@.ERROR, so that's not really a valid test.
Joel wrote:
> I tried to write some test code that looks like:
> DECLARE @.ErrorValue int
> sp_addmessage 50001,10,'This is a test error message'
> RAISERROR (50001,10,1)
> SELECT @.ErrorValue = @.@.ERROR
> If @.ErrorValue <> 0 ...etc.
> I expected @.ErrorValue to be 50001, since I tried to move @.@.ERROR to a
> variable immediately after the line that raised the error. Instead, a zero
> value was returned. Why?
> If I try to replace that line with:
> RAISERROR (-100,10,1)
> ...which generates a system error 2758 ("RAISERROR could not locate entry
> for error -100 in sysmessages"), and @.ErrorValue is set to 2758, so I know
> the code is working.
> Does RAISERROR not set the value of @.@.ERROR?
> Thanks!|||Ahh... that worked. I just tried the same code with an error level of 12, an
d
it works fine. Thanks for the help!
"Trey Walpole" wrote:
> from BOL
> "When an error is raised, the error number is placed in the @.@.ERROR
> function, which stores the most recently generated error number. @.@.ERROR
> is set to 0 by default for messages with a severity from 1 through 10."
> Your second RAISERROR itself raised an error, which is what was returned
> in @.@.ERROR, so that's not really a valid test.
> Joel wrote:
>sql
Monday, March 19, 2012
Does MSDE fit my need?
I am creating an application that need store data locally (the new data
are downloaded from my webserver daily). Here is what I want:
1. Secure - Only my application can read the database. I don't want the
user be able to look at the data using other tools, or be able to
export the data for other purpose.
2. Handle large amount of data and be very fast.
3. How MSDE is distributed?
Thanks
John
hi,
Q. John Chen wrote:
> I am creating an application that need store data locally (the new
> data are downloaded from my webserver daily). Here is what I want:
> 1. Secure - Only my application can read the database. I don't want
> the user be able to look at the data using other tools, or be able to
> export the data for other purpose.
>
SQL Server/MSDE is secure as long as you provide an accurate login logic...
SQL Server uses a so called "2 phase" authentication policy:
first an SQL Server Login or a Windows login must be created of granted
access to the SQL Server instance... at the server level a login can be made
member of none, 1 or all of the fixed server roles, which include "sysadmin"
role and so on...
the second authentication phase is at database level, where each login will
be granted database access mapping to a database user... here access
permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so on)
privileges at an object level (or column level for tables and views)..
the mapping is performed in the JOIN database..sysusers.sid =
master..syslogins.sid , so the only link is the provided Login's sid, it's
Security IDentification number
so, the second phase regards a database security implementation... in order
to access a specified database the simple login existance does not provide
database access, but a (database) user must be mapped to the corresponding
login.. and is about verifying that at each object level (including
database, tables, views, columns, procedures and so on) the Login/User
association is permitted access to... please go on reading at
http://msdn.microsoft.com/library/de...urity_05bt.asp ,
http://msdn.microsoft.com/library/de...ar_da_0n77.asp
and following chapters..
but, back to the first phase, you can choose between 2 authentication modes:
WinNT (trusted) connections or SQL Server authenticated connections... the
latter always requires full user's credential such as "User
Id=sa;Password=pwd", the password can be NULL so it must not be specified,
but I strongly advise you always to ensure strong passwords are present...
WindowsNT authentication, on the contrary, does not requires user's
credential becouse it's directly provided by Windows via the logins'ID
(sid), which authenticate user's login at the windows login step... SQL
Server only needs to verify that the corresponding login and/or group is
granted to log on the instance...
Microsoft recommends to use the Windows NT (trusted) model as it grants more
and reliable security patterns
you can start reading about authentication modes at
other articles worth reading can be found at
http://www.sql-server-performaXnce.c...l_security.asp
http://www.microsoft.com/technet/pro.../sp3sec03.mspx
http://www.microsoft.com/technet/pro.../sp3sec00.mspx
> 2. Handle large amount of data and be very fast.
about large amount of data, MSDE is limited to 2gb data file per database..
> 3. How MSDE is distributed?
I do not understand this question... if it's about legal permissions, MSDE
is free to download and use, where you have to register (for free) at
http://www.microsoft.com/sql/msde/ho...stregister.asp for
redistribution rights...
frmo a technical point of view, it isa provided as a package including a
boostrap installer based on Windows Installer technology, to be run from a
command line prompt in order to provide all the required parameters
http://msdn.microsoft.com/library/de...stsql_84xl.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||About security the OP asked. His intention is not allowing user to see his
database design and data. On this regard, login logic only guard very
innocent users. For any reasonably knowledgable network/computer
administrator, who allows MSDE being installed or who does his own MSDE
installation (thanks to MSDE, many non-database administrators know how to
do it now), can install/uninstall, attach/detach your *.mdf , then be able
to see the database, unless some sort of encryption is applied. After all,
you cannot prevent an Administrator to install/uninstall MSDE. Say, your app
installs MSDE with SQL Security only with a long SA password. The user can
easily enable Win Security by going to Registry, or simply uninstall the
MSDE (note, user database *.mdf does not get erased during uninstallation)
and re-install it with Windows security. And he can create whatever login
and give the login whatever role he wants and then attach your database and
open it.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3drhk9F6n6b9nU1@.individual.net...
> hi,
> Q. John Chen wrote:
> SQL Server/MSDE is secure as long as you provide an accurate login
logic...
> SQL Server uses a so called "2 phase" authentication policy:
> first an SQL Server Login or a Windows login must be created of granted
> access to the SQL Server instance... at the server level a login can be
made
> member of none, 1 or all of the fixed server roles, which include
"sysadmin"
> role and so on...
>
> the second authentication phase is at database level, where each login
will
> be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
on)
> privileges at an object level (or column level for tables and views)..
> the mapping is performed in the JOIN database..sysusers.sid =
> master..syslogins.sid , so the only link is the provided Login's sid, it's
> Security IDentification number
> so, the second phase regards a database security implementation... in
order
> to access a specified database the simple login existance does not provide
> database access, but a (database) user must be mapped to the corresponding
> login.. and is about verifying that at each object level (including
> database, tables, views, columns, procedures and so on) the Login/User
> association is permitted access to... please go on reading at
>
http://msdn.microsoft.com/library/de...urity_05bt.asp ,
>
http://msdn.microsoft.com/library/de...ar_da_0n77.asp
> and following chapters..
>
> but, back to the first phase, you can choose between 2 authentication
modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
present...
> WindowsNT authentication, on the contrary, does not requires user's
> credential becouse it's directly provided by Windows via the logins'ID
> (sid), which authenticate user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance...
>
> Microsoft recommends to use the Windows NT (trusted) model as it grants
more
> and reliable security patterns
> you can start reading about authentication modes at
>
> other articles worth reading can be found at
> http://www.sql-server-performaXnce.c...l_security.asp
>
http://www.microsoft.com/technet/pro.../sp3sec03.mspx
>
http://www.microsoft.com/technet/pro.../sp3sec00.mspx
>
> about large amount of data, MSDE is limited to 2gb data file per
database..
>
> I do not understand this question... if it's about legal permissions, MSDE
> is free to download and use, where you have to register (for free) at
> http://www.microsoft.com/sql/msde/ho...stregister.asp for
> redistribution rights...
> frmo a technical point of view, it isa provided as a package including a
> boostrap installer based on Windows Installer technology, to be run from a
> command line prompt in order to provide all the required parameters
>
http://msdn.microsoft.com/library/de...stsql_84xl.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||It seems that I can not use MSDE for my application thanks to you
answer. (unless I can find a way to remove your post :-))
Any recommendation on a local database that provide the security I
wanted. (not a good place to ask for an alternative here though).
Thanks
John
|||hi Norman,
I do not understand if you are just claiming for security...
Norman Yuan wrote:
> About security the OP asked. His intention is not allowing user to
> see his
> database design and data.
and of course you have to manage your WinNT users/groups accordingly to your
security needs and policy.. never give permissions your user is not
interested/accorded with..
and again, of course, we are dealing with SQL Server security and not OS
security, you should already know and manage accordingly to your needs
> On this regard, login logic only guard very
> innocent users. For any reasonably knowledgable network/computer
> administrator, who allows MSDE being installed or who does his own
> MSDE
> installation (thanks to MSDE, many non-database administrators know
> how to
> do it now), can install/uninstall, attach/detach your *.mdf , then be
> able
> to see the database, unless some sort of encryption is applied. After
> all,
> you cannot prevent an Administrator to install/uninstall MSDE.
do not understand this point... you can actually prevent your administrators
from logging in SQL Server... but you can not prevent them to uninstall SQL
Server... that's ok... but, what kind of employee do you have in your
organization? usually, if you can not trust your (fews) administrator, I
really think you have to fire them... the very same applys to SQL Server
(not os) administrator(s)..
>Say,
> your app
> installs MSDE with SQL Security only with a long SA password. The
> user can
> easily enable Win Security by going to Registry,
not the user, the local administrator...
>or simply uninstall the
> MSDE (note, user database *.mdf does not get erased during
> uninstallation)
> and re-install it with Windows security. And he can create whatever
> login
> and give the login whatever role he wants and then attach your
> database and
> open it.
again... I think you should fire your employees :D
as a local admin can do whatever operation he likes to do, you can not
prevent him to stop the server and trash your data.. he can perhaps even
eventually log on SQL Server, if you did not remove the
BUILTIN\Administrator login group (as I usually do) from the MSDE istance,
and of course, as part of the sysamin server role, even access the company
database and increase his salary by 20%...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||The original OP are concerned about protecting his software, including the
database design and data the software used. He does not want the software
user, be it individual or organization, to peek into his software logic by
openning database on MSDE. So my point is the software user has the power to
open a SQL Server database installed on his computer unless some encryption
is implemented on the database. It has nothing to do with how the employee
is behaves and is regulated.
Actually, from the point of view of pretecting software, lot of your unique
business logic are reflected on the database design. And when using MSDE in
your application, we are educated to use as much stroed procedures as
possible, meaning more business logic in the DB. Obviously, there is need to
protect them (I know and you know there are tools for encypting SPs). In
most cases of SQL Server being used in a organization, it is most likely
that app used there are custom-developed, so protecting software wouldn't be
a problem. But when you are developing a stand-alone app for sale, with MSDE
integrided, concerns on user peeking into the DB is understandable.
I developed a windows app package a couple of years ago and used MSDE with
tons of SPs in it. It was aimed to small business in certain business. There
is nothing to prevent them to find a knowledgable guy to get into the
database and uses those Tables/SPs and develop there new UI app, although
they did not do that. Since MSDE is a powerful data engine and very easy to
be integrited into your app, protecting software resulted in by this should
be a concern. How to safyly regulating SQL Server/MSDE in a organization is
not my topic here.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3dsa5rF6tlqnqU1@.individual.net...
> hi Norman,
> I do not understand if you are just claiming for security...
> Norman Yuan wrote:
> and of course you have to manage your WinNT users/groups accordingly to
your
> security needs and policy.. never give permissions your user is not
> interested/accorded with..
> and again, of course, we are dealing with SQL Server security and not OS
> security, you should already know and manage accordingly to your needs
>
> do not understand this point... you can actually prevent your
administrators
> from logging in SQL Server... but you can not prevent them to uninstall
SQL
> Server... that's ok... but, what kind of employee do you have in your
> organization? usually, if you can not trust your (fews) administrator, I
> really think you have to fire them... the very same applys to SQL Server
> (not os) administrator(s)..
>
> not the user, the local administrator...
>
> again... I think you should fire your employees :D
> as a local admin can do whatever operation he likes to do, you can not
> prevent him to stop the server and trash your data.. he can perhaps even
> eventually log on SQL Server, if you did not remove the
> BUILTIN\Administrator login group (as I usually do) from the MSDE istance,
> and of course, as part of the sysamin server role, even access the company
> database and increase his salary by 20%...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Norman,
You read my mind. The software I developed is an application analyzing
commodity trading data. The user is not of a single organization but in
different organization or individuals all over the world. What we are
selling is the data not the software ifself. So protect the data is the
first priority.
Still, I want thank Andrea for giving me all the information about
MSDE.
Again, thanks both.
John
|||Q. John Chen wrote:
> Norman,
> You read my mind. The software I developed is an application analyzing
> commodity trading data. The user is not of a single organization but in
> different organization or individuals all over the world. What we are
> selling is the data not the software ifself. So protect the data is the
> first priority.
> Still, I want thank Andrea for giving me all the information about
> MSDE.
> Again, thanks both.
> John
>
Hi,
My company sells data and ships an MSDE application. What we ended up
doing was coding in application encryption logic. Numbers are not
encrypted but databae column with anything in text (like say
COMPANYNAME) was encrypted.
We can't stop the end-users looking at the database through Access, but
what they do see is a garbled mess !
|||Encrypt the data before storing it in the database, and decrypt it on the
application side when you read it. Of course this will affect overall
performance, but if security is your primary concern, performance is going
to take a hit no matter what.
"Q. John Chen" <qjchen@.email.com> wrote in message
news:1115221772.287953.148830@.o13g2000cwo.googlegr oups.com...
> It seems that I can not use MSDE for my application thanks to you
> answer. (unless I can find a way to remove your post :-))
> Any recommendation on a local database that provide the security I
> wanted. (not a good place to ask for an alternative here though).
> Thanks
> John
>
are downloaded from my webserver daily). Here is what I want:
1. Secure - Only my application can read the database. I don't want the
user be able to look at the data using other tools, or be able to
export the data for other purpose.
2. Handle large amount of data and be very fast.
3. How MSDE is distributed?
Thanks
John
hi,
Q. John Chen wrote:
> I am creating an application that need store data locally (the new
> data are downloaded from my webserver daily). Here is what I want:
> 1. Secure - Only my application can read the database. I don't want
> the user be able to look at the data using other tools, or be able to
> export the data for other purpose.
>
SQL Server/MSDE is secure as long as you provide an accurate login logic...
SQL Server uses a so called "2 phase" authentication policy:
first an SQL Server Login or a Windows login must be created of granted
access to the SQL Server instance... at the server level a login can be made
member of none, 1 or all of the fixed server roles, which include "sysadmin"
role and so on...
the second authentication phase is at database level, where each login will
be granted database access mapping to a database user... here access
permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so on)
privileges at an object level (or column level for tables and views)..
the mapping is performed in the JOIN database..sysusers.sid =
master..syslogins.sid , so the only link is the provided Login's sid, it's
Security IDentification number
so, the second phase regards a database security implementation... in order
to access a specified database the simple login existance does not provide
database access, but a (database) user must be mapped to the corresponding
login.. and is about verifying that at each object level (including
database, tables, views, columns, procedures and so on) the Login/User
association is permitted access to... please go on reading at
http://msdn.microsoft.com/library/de...urity_05bt.asp ,
http://msdn.microsoft.com/library/de...ar_da_0n77.asp
and following chapters..
but, back to the first phase, you can choose between 2 authentication modes:
WinNT (trusted) connections or SQL Server authenticated connections... the
latter always requires full user's credential such as "User
Id=sa;Password=pwd", the password can be NULL so it must not be specified,
but I strongly advise you always to ensure strong passwords are present...
WindowsNT authentication, on the contrary, does not requires user's
credential becouse it's directly provided by Windows via the logins'ID
(sid), which authenticate user's login at the windows login step... SQL
Server only needs to verify that the corresponding login and/or group is
granted to log on the instance...
Microsoft recommends to use the Windows NT (trusted) model as it grants more
and reliable security patterns
you can start reading about authentication modes at
other articles worth reading can be found at
http://www.sql-server-performaXnce.c...l_security.asp
http://www.microsoft.com/technet/pro.../sp3sec03.mspx
http://www.microsoft.com/technet/pro.../sp3sec00.mspx
> 2. Handle large amount of data and be very fast.
about large amount of data, MSDE is limited to 2gb data file per database..
> 3. How MSDE is distributed?
I do not understand this question... if it's about legal permissions, MSDE
is free to download and use, where you have to register (for free) at
http://www.microsoft.com/sql/msde/ho...stregister.asp for
redistribution rights...
frmo a technical point of view, it isa provided as a package including a
boostrap installer based on Windows Installer technology, to be run from a
command line prompt in order to provide all the required parameters
http://msdn.microsoft.com/library/de...stsql_84xl.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||About security the OP asked. His intention is not allowing user to see his
database design and data. On this regard, login logic only guard very
innocent users. For any reasonably knowledgable network/computer
administrator, who allows MSDE being installed or who does his own MSDE
installation (thanks to MSDE, many non-database administrators know how to
do it now), can install/uninstall, attach/detach your *.mdf , then be able
to see the database, unless some sort of encryption is applied. After all,
you cannot prevent an Administrator to install/uninstall MSDE. Say, your app
installs MSDE with SQL Security only with a long SA password. The user can
easily enable Win Security by going to Registry, or simply uninstall the
MSDE (note, user database *.mdf does not get erased during uninstallation)
and re-install it with Windows security. And he can create whatever login
and give the login whatever role he wants and then attach your database and
open it.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3drhk9F6n6b9nU1@.individual.net...
> hi,
> Q. John Chen wrote:
> SQL Server/MSDE is secure as long as you provide an accurate login
logic...
> SQL Server uses a so called "2 phase" authentication policy:
> first an SQL Server Login or a Windows login must be created of granted
> access to the SQL Server instance... at the server level a login can be
made
> member of none, 1 or all of the fixed server roles, which include
"sysadmin"
> role and so on...
>
> the second authentication phase is at database level, where each login
will
> be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
on)
> privileges at an object level (or column level for tables and views)..
> the mapping is performed in the JOIN database..sysusers.sid =
> master..syslogins.sid , so the only link is the provided Login's sid, it's
> Security IDentification number
> so, the second phase regards a database security implementation... in
order
> to access a specified database the simple login existance does not provide
> database access, but a (database) user must be mapped to the corresponding
> login.. and is about verifying that at each object level (including
> database, tables, views, columns, procedures and so on) the Login/User
> association is permitted access to... please go on reading at
>
http://msdn.microsoft.com/library/de...urity_05bt.asp ,
>
http://msdn.microsoft.com/library/de...ar_da_0n77.asp
> and following chapters..
>
> but, back to the first phase, you can choose between 2 authentication
modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
present...
> WindowsNT authentication, on the contrary, does not requires user's
> credential becouse it's directly provided by Windows via the logins'ID
> (sid), which authenticate user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance...
>
> Microsoft recommends to use the Windows NT (trusted) model as it grants
more
> and reliable security patterns
> you can start reading about authentication modes at
>
> other articles worth reading can be found at
> http://www.sql-server-performaXnce.c...l_security.asp
>
http://www.microsoft.com/technet/pro.../sp3sec03.mspx
>
http://www.microsoft.com/technet/pro.../sp3sec00.mspx
>
> about large amount of data, MSDE is limited to 2gb data file per
database..
>
> I do not understand this question... if it's about legal permissions, MSDE
> is free to download and use, where you have to register (for free) at
> http://www.microsoft.com/sql/msde/ho...stregister.asp for
> redistribution rights...
> frmo a technical point of view, it isa provided as a package including a
> boostrap installer based on Windows Installer technology, to be run from a
> command line prompt in order to provide all the required parameters
>
http://msdn.microsoft.com/library/de...stsql_84xl.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||It seems that I can not use MSDE for my application thanks to you
answer. (unless I can find a way to remove your post :-))
Any recommendation on a local database that provide the security I
wanted. (not a good place to ask for an alternative here though).
Thanks
John
|||hi Norman,
I do not understand if you are just claiming for security...
Norman Yuan wrote:
> About security the OP asked. His intention is not allowing user to
> see his
> database design and data.
and of course you have to manage your WinNT users/groups accordingly to your
security needs and policy.. never give permissions your user is not
interested/accorded with..
and again, of course, we are dealing with SQL Server security and not OS
security, you should already know and manage accordingly to your needs
> On this regard, login logic only guard very
> innocent users. For any reasonably knowledgable network/computer
> administrator, who allows MSDE being installed or who does his own
> MSDE
> installation (thanks to MSDE, many non-database administrators know
> how to
> do it now), can install/uninstall, attach/detach your *.mdf , then be
> able
> to see the database, unless some sort of encryption is applied. After
> all,
> you cannot prevent an Administrator to install/uninstall MSDE.
do not understand this point... you can actually prevent your administrators
from logging in SQL Server... but you can not prevent them to uninstall SQL
Server... that's ok... but, what kind of employee do you have in your
organization? usually, if you can not trust your (fews) administrator, I
really think you have to fire them... the very same applys to SQL Server
(not os) administrator(s)..
>Say,
> your app
> installs MSDE with SQL Security only with a long SA password. The
> user can
> easily enable Win Security by going to Registry,
not the user, the local administrator...
>or simply uninstall the
> MSDE (note, user database *.mdf does not get erased during
> uninstallation)
> and re-install it with Windows security. And he can create whatever
> login
> and give the login whatever role he wants and then attach your
> database and
> open it.
again... I think you should fire your employees :D
as a local admin can do whatever operation he likes to do, you can not
prevent him to stop the server and trash your data.. he can perhaps even
eventually log on SQL Server, if you did not remove the
BUILTIN\Administrator login group (as I usually do) from the MSDE istance,
and of course, as part of the sysamin server role, even access the company
database and increase his salary by 20%...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||The original OP are concerned about protecting his software, including the
database design and data the software used. He does not want the software
user, be it individual or organization, to peek into his software logic by
openning database on MSDE. So my point is the software user has the power to
open a SQL Server database installed on his computer unless some encryption
is implemented on the database. It has nothing to do with how the employee
is behaves and is regulated.
Actually, from the point of view of pretecting software, lot of your unique
business logic are reflected on the database design. And when using MSDE in
your application, we are educated to use as much stroed procedures as
possible, meaning more business logic in the DB. Obviously, there is need to
protect them (I know and you know there are tools for encypting SPs). In
most cases of SQL Server being used in a organization, it is most likely
that app used there are custom-developed, so protecting software wouldn't be
a problem. But when you are developing a stand-alone app for sale, with MSDE
integrided, concerns on user peeking into the DB is understandable.
I developed a windows app package a couple of years ago and used MSDE with
tons of SPs in it. It was aimed to small business in certain business. There
is nothing to prevent them to find a knowledgable guy to get into the
database and uses those Tables/SPs and develop there new UI app, although
they did not do that. Since MSDE is a powerful data engine and very easy to
be integrited into your app, protecting software resulted in by this should
be a concern. How to safyly regulating SQL Server/MSDE in a organization is
not my topic here.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3dsa5rF6tlqnqU1@.individual.net...
> hi Norman,
> I do not understand if you are just claiming for security...
> Norman Yuan wrote:
> and of course you have to manage your WinNT users/groups accordingly to
your
> security needs and policy.. never give permissions your user is not
> interested/accorded with..
> and again, of course, we are dealing with SQL Server security and not OS
> security, you should already know and manage accordingly to your needs
>
> do not understand this point... you can actually prevent your
administrators
> from logging in SQL Server... but you can not prevent them to uninstall
SQL
> Server... that's ok... but, what kind of employee do you have in your
> organization? usually, if you can not trust your (fews) administrator, I
> really think you have to fire them... the very same applys to SQL Server
> (not os) administrator(s)..
>
> not the user, the local administrator...
>
> again... I think you should fire your employees :D
> as a local admin can do whatever operation he likes to do, you can not
> prevent him to stop the server and trash your data.. he can perhaps even
> eventually log on SQL Server, if you did not remove the
> BUILTIN\Administrator login group (as I usually do) from the MSDE istance,
> and of course, as part of the sysamin server role, even access the company
> database and increase his salary by 20%...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Norman,
You read my mind. The software I developed is an application analyzing
commodity trading data. The user is not of a single organization but in
different organization or individuals all over the world. What we are
selling is the data not the software ifself. So protect the data is the
first priority.
Still, I want thank Andrea for giving me all the information about
MSDE.
Again, thanks both.
John
|||Q. John Chen wrote:
> Norman,
> You read my mind. The software I developed is an application analyzing
> commodity trading data. The user is not of a single organization but in
> different organization or individuals all over the world. What we are
> selling is the data not the software ifself. So protect the data is the
> first priority.
> Still, I want thank Andrea for giving me all the information about
> MSDE.
> Again, thanks both.
> John
>
Hi,
My company sells data and ships an MSDE application. What we ended up
doing was coding in application encryption logic. Numbers are not
encrypted but databae column with anything in text (like say
COMPANYNAME) was encrypted.
We can't stop the end-users looking at the database through Access, but
what they do see is a garbled mess !
|||Encrypt the data before storing it in the database, and decrypt it on the
application side when you read it. Of course this will affect overall
performance, but if security is your primary concern, performance is going
to take a hit no matter what.
"Q. John Chen" <qjchen@.email.com> wrote in message
news:1115221772.287953.148830@.o13g2000cwo.googlegr oups.com...
> It seems that I can not use MSDE for my application thanks to you
> answer. (unless I can find a way to remove your post :-))
> Any recommendation on a local database that provide the security I
> wanted. (not a good place to ask for an alternative here though).
> Thanks
> John
>
Does MS Access installation is required for running application that uses Access mdb file
Hi,
I am developing an application that uses Access database (mdb file) to store the user data. The user of this application is not interested in the database file (to view in MS Access Environment). Does the user machine requires MS Access installation to run my application or just some couple of dlls (OleDB driver, Access DB Engine,..) should be enough to run my application?
Thanks,
Rao
No, they don't need Access. They will simply need to have MDAC installed, and chances are it will already be there. If not get it here
http://msdn.microsoft.com/data/ref/mdac/
|||The Information is helpful.
Thanks
Sunday, March 11, 2012
Does JOINs and VIEWs lose performance when used with more than one DataBase?
Hi,
My application consist of 2 big parts, which work together but are in some
way seperate.
In case I should store the 2 parts in different databases (but on the same
server), will I lose performance when doing a query that joins tables from
both databases? And what about views?
Any help our hints would be really appreciated.
Thanks a lot in advance,
Pieter
On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
> Hi,
> My application consist of 2 big parts, which work together but are in some
> way seperate.
> In case I should store the 2 parts in different databases (but on the same
> server), will I lose performance when doing a query that joins tables from
> both databases? And what about views?
> Any help our hints would be really appreciated.
> Thanks a lot in advance,
> Pieter
Hello,
You will have no performance decrease. Try to always include the owner in
the object naming (dbo I hope), to ease the work of SQL Server.
What you physically loose splitting your model, is of course the DRIs, but
you can manage it with triggers.
Rudi Bruchez
MCDBA
|||ok! thanks a lot!
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:1goqk59i7hqzp$.146q0p9y2vigx$.dlg@.40tude.net. ..
> On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
>
> Hello,
> You will have no performance decrease. Try to always include the owner in
> the object naming (dbo I hope), to ease the work of SQL Server.
> What you physically loose splitting your model, is of course the DRIs, but
> you can manage it with triggers.
> --
> Rudi Bruchez
> MCDBA
My application consist of 2 big parts, which work together but are in some
way seperate.
In case I should store the 2 parts in different databases (but on the same
server), will I lose performance when doing a query that joins tables from
both databases? And what about views?
Any help our hints would be really appreciated.
Thanks a lot in advance,
Pieter
On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
> Hi,
> My application consist of 2 big parts, which work together but are in some
> way seperate.
> In case I should store the 2 parts in different databases (but on the same
> server), will I lose performance when doing a query that joins tables from
> both databases? And what about views?
> Any help our hints would be really appreciated.
> Thanks a lot in advance,
> Pieter
Hello,
You will have no performance decrease. Try to always include the owner in
the object naming (dbo I hope), to ease the work of SQL Server.
What you physically loose splitting your model, is of course the DRIs, but
you can manage it with triggers.
Rudi Bruchez
MCDBA
|||ok! thanks a lot!
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:1goqk59i7hqzp$.146q0p9y2vigx$.dlg@.40tude.net. ..
> On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
>
> Hello,
> You will have no performance decrease. Try to always include the owner in
> the object naming (dbo I hope), to ease the work of SQL Server.
> What you physically loose splitting your model, is of course the DRIs, but
> you can manage it with triggers.
> --
> Rudi Bruchez
> MCDBA
Does JOINs and VIEWs lose performance when used with more than one DataBase?
Hi,
My application consist of 2 big parts, which work together but are in some
way seperate.
In case I should store the 2 parts in different databases (but on the same
server), will I lose performance when doing a query that joins tables from
both databases? And what about views?
Any help our hints would be really appreciated.
Thanks a lot in advance,
PieterOn Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
> Hi,
> My application consist of 2 big parts, which work together but are in some
> way seperate.
> In case I should store the 2 parts in different databases (but on the same
> server), will I lose performance when doing a query that joins tables from
> both databases? And what about views?
> Any help our hints would be really appreciated.
> Thanks a lot in advance,
> Pieter
Hello,
You will have no performance decrease. Try to always include the owner in
the object naming (dbo I hope), to ease the work of SQL Server.
What you physically loose splitting your model, is of course the DRIs, but
you can manage it with triggers.
Rudi Bruchez
MCDBA|||ok! thanks a lot!
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:1goqk59i7hqzp$.146q0p9y2vigx$.dlg@.40tude.net...
> On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
>
> Hello,
> You will have no performance decrease. Try to always include the owner in
> the object naming (dbo I hope), to ease the work of SQL Server.
> What you physically loose splitting your model, is of course the DRIs, but
> you can manage it with triggers.
> --
> Rudi Bruchez
> MCDBA
My application consist of 2 big parts, which work together but are in some
way seperate.
In case I should store the 2 parts in different databases (but on the same
server), will I lose performance when doing a query that joins tables from
both databases? And what about views?
Any help our hints would be really appreciated.
Thanks a lot in advance,
PieterOn Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
> Hi,
> My application consist of 2 big parts, which work together but are in some
> way seperate.
> In case I should store the 2 parts in different databases (but on the same
> server), will I lose performance when doing a query that joins tables from
> both databases? And what about views?
> Any help our hints would be really appreciated.
> Thanks a lot in advance,
> Pieter
Hello,
You will have no performance decrease. Try to always include the owner in
the object naming (dbo I hope), to ease the work of SQL Server.
What you physically loose splitting your model, is of course the DRIs, but
you can manage it with triggers.
Rudi Bruchez
MCDBA|||ok! thanks a lot!
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:1goqk59i7hqzp$.146q0p9y2vigx$.dlg@.40tude.net...
> On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
>
> Hello,
> You will have no performance decrease. Try to always include the owner in
> the object naming (dbo I hope), to ease the work of SQL Server.
> What you physically loose splitting your model, is of course the DRIs, but
> you can manage it with triggers.
> --
> Rudi Bruchez
> MCDBA
Does JOINs and VIEWs lose performance when used with more than one DataBase?
Hi,
My application consist of 2 big parts, which work together but are in some
way seperate.
In case I should store the 2 parts in different databases (but on the same
server), will I lose performance when doing a query that joins tables from
both databases? And what about views?
Any help our hints would be really appreciated.
Thanks a lot in advance,
PieterOn Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
> Hi,
> My application consist of 2 big parts, which work together but are in some
> way seperate.
> In case I should store the 2 parts in different databases (but on the same
> server), will I lose performance when doing a query that joins tables from
> both databases? And what about views?
> Any help our hints would be really appreciated.
> Thanks a lot in advance,
> Pieter
Hello,
You will have no performance decrease. Try to always include the owner in
the object naming (dbo I hope), to ease the work of SQL Server.
What you physically loose splitting your model, is of course the DRIs, but
you can manage it with triggers.
--
Rudi Bruchez
MCDBA|||ok! thanks a lot!
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:1goqk59i7hqzp$.146q0p9y2vigx$.dlg@.40tude.net...
> On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
>> Hi,
>> My application consist of 2 big parts, which work together but are in
>> some
>> way seperate.
>> In case I should store the 2 parts in different databases (but on the
>> same
>> server), will I lose performance when doing a query that joins tables
>> from
>> both databases? And what about views?
>> Any help our hints would be really appreciated.
>> Thanks a lot in advance,
>> Pieter
> Hello,
> You will have no performance decrease. Try to always include the owner in
> the object naming (dbo I hope), to ease the work of SQL Server.
> What you physically loose splitting your model, is of course the DRIs, but
> you can manage it with triggers.
> --
> Rudi Bruchez
> MCDBA
My application consist of 2 big parts, which work together but are in some
way seperate.
In case I should store the 2 parts in different databases (but on the same
server), will I lose performance when doing a query that joins tables from
both databases? And what about views?
Any help our hints would be really appreciated.
Thanks a lot in advance,
PieterOn Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
> Hi,
> My application consist of 2 big parts, which work together but are in some
> way seperate.
> In case I should store the 2 parts in different databases (but on the same
> server), will I lose performance when doing a query that joins tables from
> both databases? And what about views?
> Any help our hints would be really appreciated.
> Thanks a lot in advance,
> Pieter
Hello,
You will have no performance decrease. Try to always include the owner in
the object naming (dbo I hope), to ease the work of SQL Server.
What you physically loose splitting your model, is of course the DRIs, but
you can manage it with triggers.
--
Rudi Bruchez
MCDBA|||ok! thanks a lot!
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:1goqk59i7hqzp$.146q0p9y2vigx$.dlg@.40tude.net...
> On Fri, 4 Nov 2005 17:59:02 +0100, Pieter wrote:
>> Hi,
>> My application consist of 2 big parts, which work together but are in
>> some
>> way seperate.
>> In case I should store the 2 parts in different databases (but on the
>> same
>> server), will I lose performance when doing a query that joins tables
>> from
>> both databases? And what about views?
>> Any help our hints would be really appreciated.
>> Thanks a lot in advance,
>> Pieter
> Hello,
> You will have no performance decrease. Try to always include the owner in
> the object naming (dbo I hope), to ease the work of SQL Server.
> What you physically loose splitting your model, is of course the DRIs, but
> you can manage it with triggers.
> --
> Rudi Bruchez
> MCDBA
Does it store all the results to tempdb database when I query against a large table which joins
Hi, all experts here,
I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.
With best regards,
Yours sincerely,
It doesn't store all the results - but it may store some intermediate results if it needs to.You really shouldn't be putting tempdb on your local system drive. tempdb always has a lot of activity (typically the busiest database in any system), and ideally should be on its own disk - a fast one.
If you have over 4 million records in a single table, 1GB doesn't sound too big for tempdb. But I appreciate this doesn't fix your immediate problem.
Rob|||
Hi, Rob,
Thank you for your kind advices.
But how can we change the storage location of the tempdb database? It looks like tempdb database can not be detached.
I am looking forward to hearing from you further for your kind advices.
With best regards,
Yours sincerely,
|||And also, though I have moved the data files of tempdb to other places, but when I restarted the database engine, the system automatically generates the data files of tempdb again back to its root data files?
Please give me some more advices for this.
Thanks a lot in advance.
With best regards,
Yours sincerely,
|||I believe moving tempdb is as simple as altering the file paths and restarting SQL Server. It'll create the new files automatically. I'm not sure if it removes the old ones automatically, so you may have to to some brief housekeeping once the database is up and running in its new location.ALTER DATABASE tempdb MODIFY FILE (NAME = logical_filename, FILENAME = 'new_physical_file_path')
Just make sure you do the log in addition to all the data files you have.
|||
Hi, davidbrit2,
Thank you for your kind attention.
But I failed to modify the files paths for the data files of the tempdb database, I did restart the service, but the data files of tempdb again went back to its original file paths. Therefore I does not seem like we are able to change the file paths of the data files of tempdb.
Any more ideas? Thanks a lot in advance.
With best regards,
Yours sincerely,
|||You need to alter the database to tell it to have its files in a different location. But then you need to restart SQL Server (ie, the service - use SQL Configuration Manager) to actually have it start using those new locations. Once you've restarted SQL, you should see the new files start to be used. Then you can delete the old ones.Hope this helps,
Rob|||
Hello Helen999888,
Let me start with giving you a brief of what is tempdb.
The tempdb system database is a global resource that is available to all users that are connected to an instance of SQL Server. The tempdb database is used to store the following objects: user objects, internal objects, and version stores.
User Objects
User objects are explicitly created by the user. These objects may be in the scope of a user session or in the scope of the routine in which the object is created. A routine is a stored procedure, trigger, or user-defined function. User objects can be one of the following:
User-defined tables and indexesSystem tables and indexes
Global temporary tables and indexes
Local temporary tables and indexes
Table variables
Tables returned in table-valued functions
|||
Hi, HS_DJ,
Thanks a lot for your very kind and helpful advices. Got it done now.
With best regards,
Yours sincerely,
Wednesday, March 7, 2012
Does anyone use SSIS for database schema maintenance?
We currently use SSIS to build DTS packages in which we store changes
to our database schema, as well as scripts that need to be run upon
each release. This works well for small sets of changes that never
need to be updated or for architectures with only one database.
Hi Ben,
to our database schema, as well as scripts that need to be run upon
each release. This works well for small sets of changes that never
need to be updated or for architectures with only one database.
We store each of the changes included in the package in separate
files, which are tracked using version control. It is growing time
consuming to maintain parity between those files and what is in the
SSIS.
Furthermore, we have been unable to discover an easy way to load a
file's contents into a package SQL Task without opening the file and
copy-pasting the contents into a new SQL task.
ANY information at all would be extremely appreciated!On Feb 28, 2:54 pm, "Ben" <vanev...@.gmail.comwrote:
Quote:
Originally Posted by
We currently use SSIS to build DTS packages in which we store changes
to our database schema, as well as scripts that need to be run upon
each release. This works well for small sets of changes that never
need to be updated or for architectures with only one database.
>
We store each of the changes included in the package in separate
files, which are tracked using version control. It is growing time
consuming to maintain parity between those files and what is in the
SSIS.
>
Furthermore, we have been unable to discover an easy way to load a
file's contents into a package SQL Task without opening the file and
copy-pasting the contents into a new SQL task.
>
ANY information at all would be extremely appreciated!
Hi Ben,
There is a rock solid change management process for SQL Server
2000/2005 and it is provided by the DB Ghost toolset from
Innovartis.
The essence of the process is that you script out all the database
objects and lookup (static) data into individual CREATE / INSERT
scripts and put them under source control. The whole dev team then
checks these files out, makes the required changes to the CREATE
statements and checks them back in again (this can scale to thousand
of developers). Once you're ready to release the schema to the test
environment you use the DB Ghost Change Manager tool to make the
target database match the set of source scripts. If, for example, a
developer added a column to a table CREATE script then the Change
Manager would detect this and add the column to the target database
seamlessly.
Basically, DB Ghost enables you to develop in the same way as you do
for a greenfield (release 1) database for every subsequent release of
your schema without losing any data in the target database.
Our customers rave about DB Ghost and can't believe the cost savings
it brings - have a look for yourself :)
www.dbghost.com
Kind regards,
Malcolm
Friday, February 24, 2012
Does a large amount 'image' data affect overall SQL performance?
Recently we added a new table into our SQL2000 database specifically to store scanned in images of documents. This new table contains a PK field, a couple of datetime fields, a couple of char(1) fields and one 'image' field.
Before adding this table, the database size was approx 6GB. Six months after adding this new table, the database has grown to 18GB - 11GB of this is due to the scanned in images.
Would this new table affect the SQL performance with regards to accessing other data in the database that has nothing related to the new table?
If so, would moving this new table into it's own database be recommended?
Thanks
Rod
In order to determine the 'best' response to your question, you may find this article useful.
Images –Store in Database or Store in File System
http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45
Does "text type" belong to SQL2 standard ?
Hi,
I am developping an application that should work with different RDBMS.
I need to store a field of 500 characters.
Hence, I thought of using the type "text" that works with MySQL, but I am wondering if it is part of SQL standard or if it is supported by the main RBDMS (Orcale, DB2, SQL Server ...).
Thanks in advance,
SylvainThe "Text" datatype is not part of the ANSI SQL99 standard. Most vendors also have their own variants of datatypes. The nearest agreements in datatypes for your purposes I guess would be to use a VARCHAR. However there is still variation, in Oracle this would be a VARCHAR2.
Hope this help you on your way.|||Hi,
Originally posted by gannet
The "Text" datatype is not part of the ANSI SQL99 standard. Most vendors also have their own variants of datatypes. The nearest agreements in datatypes for your purposes I guess would be to use a VARCHAR. However there is still variation, in Oracle this would be a VARCHAR2.
But with MySQL for example VARCHAR is limited to 255. We can't do VARCHAR(500).
So you see any solution ?
Regards
Sylvain|||Unfortunately there will be no vendor independent way araound this, you will need to generate some vendor specific translations. Not what you wanted to hear I know.
I am developping an application that should work with different RDBMS.
I need to store a field of 500 characters.
Hence, I thought of using the type "text" that works with MySQL, but I am wondering if it is part of SQL standard or if it is supported by the main RBDMS (Orcale, DB2, SQL Server ...).
Thanks in advance,
SylvainThe "Text" datatype is not part of the ANSI SQL99 standard. Most vendors also have their own variants of datatypes. The nearest agreements in datatypes for your purposes I guess would be to use a VARCHAR. However there is still variation, in Oracle this would be a VARCHAR2.
Hope this help you on your way.|||Hi,
Originally posted by gannet
The "Text" datatype is not part of the ANSI SQL99 standard. Most vendors also have their own variants of datatypes. The nearest agreements in datatypes for your purposes I guess would be to use a VARCHAR. However there is still variation, in Oracle this would be a VARCHAR2.
But with MySQL for example VARCHAR is limited to 255. We can't do VARCHAR(500).
So you see any solution ?
Regards
Sylvain|||Unfortunately there will be no vendor independent way araound this, you will need to generate some vendor specific translations. Not what you wanted to hear I know.
Friday, February 17, 2012
Document Storage
Hello Everyone and thanks for your help in advance. I am developing a document storage application for an intranet that will store various Word, Excel, and PDF documents. Most of the examples I see utilize SQL Server and an image field rather than the FileSystem Object to store documents. My concern with this method is that some of the documents may be several hundred pages (not exactly sure of the actual file size yet, but they must be fairly large). My question is, where does the use of SQL Server become impractical for this type of application? Any insight would be greatly appreciated. Thanks.I'm no expert on this but the guy quoted below is :) Image is the right data type for your purpose. You should do some reading on this because there are some considerations about transferring the large amount of data along with storing it.
"In short, should you consider storing images in a database? If you need to frequently edit the images, I suggest storing the images as separate files. If the size of the images are very large (for example, hundreds of megabytes), I suggest storing the images as separate files. If your images are essentially read-only and relatively static, and if you measure the size in kilobytes or a few megabytes, you can consider storing your images in the database."
Programming Microsoft ASP.NET
Chapter 22 - Working with Images in ASP.NET
by Dino Esposito
Joe
Subscribe to:
Posts (Atom)