I've bought a book about ASP.NET with SQL Server 2000.
In one chapter, they talk about setting relations between tables, and in
another, about setting foreign keys.
But I can't understand the difference, as the purpose seems to be the same:
navigating from a parent table to a child one, and vice-versa.
Am I missing something?
Thanks!
Henri
Unless you can show more detailed context, I think they are talking about
the exact same thing. A relationship is a bit more abstract; a foreign key
represents the implementation-specific way that a child row is connected to
a parent row.
http://www.aspfaq.com/
(Reverse address to reply.)
"Henri" <hmfireball@.hotmail.com> wrote in message
news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
> I've bought a book about ASP.NET with SQL Server 2000.
> In one chapter, they talk about setting relations between tables, and in
> another, about setting foreign keys.
> But I can't understand the difference, as the purpose seems to be the
> same:
> navigating from a parent table to a child one, and vice-versa.
> Am I missing something?
> Thanks!
> Henri
>
>
|||Actually, the first chapter is about the DataRelation class, and
DataTable.ChildRelations(String) and DataRow.GetChildRows(DataRelation).
The second chapter is about ForeignKeyConstraint Class
The only difference I see is that a foreign key allows to define cascade
update and delete rules.
Actually, ASP.NET permits to define many things like indexes, primary keys,
relations, etc. but I thought that this had to be done on SQL Server, not
from ASP.NET.
That's why I'm a bit lost :-)
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a crit dans le message de
news:ORWb%23o0TEHA.2416@.TK2MSFTNGP12.phx.gbl...
> Unless you can show more detailed context, I think they are talking about
> the exact same thing. A relationship is a bit more abstract; a foreign
key
> represents the implementation-specific way that a child row is connected
to
> a parent row.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Henri" <hmfireball@.hotmail.com> wrote in message
> news:%23nSPik0TEHA.3828@.TK2MSFTNGP09.phx.gbl...
>
>
|||What you are reading about is Data Sets in ADO.Net. The
relations and constraints you define in a data set are
different and separate from relations and constraints in the
database. In ADO.Net, you can create something similar to a
database - tables (data tables), relationships (using the
DataRelation class), constraints (such as with the
ForeignKeyConstraint class). It's not the same thing as the
database you may be accessing for the data in the data sets.
For ADO.Net, you can find more information on how relations
and constraints work together, differently in .Net Framework
documentation. Check the remarks section of the definition
of the DataRelation class.
-Sue
On Fri, 11 Jun 2004 03:40:00 +0200, "Henri"
<hmfireball@.hotmail.com> wrote:
>Actually, the first chapter is about the DataRelation class, and
>DataTable.ChildRelations(String) and DataRow.GetChildRows(DataRelation).
>The second chapter is about ForeignKeyConstraint Class
>The only difference I see is that a foreign key allows to define cascade
>update and delete rules.
>Actually, ASP.NET permits to define many things like indexes, primary keys,
>relations, etc. but I thought that this had to be done on SQL Server, not
>from ASP.NET.
>That's why I'm a bit lost :-)
>
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a crit dans le message de
>news:ORWb%23o0TEHA.2416@.TK2MSFTNGP12.phx.gbl...
>key
>to
>
|||Hi,
Within ADO.Net, a Foreign Key Constaint is a subtype of the Data Relation. The FKC in ADO.Net behaves in a similar manner to a FKC within SQL Server, but it is a constraint on the Data Set, not the database, which can be an important consideration.
A Data Relation has additonal functionality that is not provided by the FKC, ( I think one is providing the Child Rows in the Data Set to a particular Master row.) I suggest you have a look at the class definitions and examine the different methods and pr
operties each suppiles
Showing posts with label ive. Show all posts
Showing posts with label ive. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Difference between indexing txt or word doc files, help!
Hi,
I'm putting together a system and one of the requirements is to have a
searchable CV function.
I've got all the code to load the files on to the image fields, I've indexed
and got it kinda working.
Before I go to far down the road what is your opinion on having txt files
instead of doc files held on the table search? The SQL seems to be more
flexible on
searches rather than on the binary files and the index files themselves are
smaller
..i.e. when I tried a like clause it told me this would only work against a
varchar field
(i'm thinking this may be a schoolboy error so forgive me)
My main concern is I'd have to do the text conversion automatically, any
pointers on this?
Does anyone have any views on the best way to go about this or views on
holding and full searches again word files
Many thanks for any help you can give
Jim Florence
Text means faster indexing times, but not by much. With text you can query
the columns and read the contents, you can't do this with binary.
Search SQL is equally as flexible with text and binary. You can only do a
like against text or char columns.
To do the conversion use filtdump -b (you can get this from the Platform
SDK), or you can use ole-automation against the word documents to extract
the text paragraph by paragraph.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jim Florence" <florence_james@.hotmail.com> wrote in message
news:5IKdncUNWPRLowLeRVnyiQ@.pipex.net...
> Hi,
> I'm putting together a system and one of the requirements is to have a
> searchable CV function.
> I've got all the code to load the files on to the image fields, I've
> indexed
> and got it kinda working.
> Before I go to far down the road what is your opinion on having txt files
> instead of doc files held on the table search? The SQL seems to be more
> flexible on
> searches rather than on the binary files and the index files themselves
> are
> smaller
> .i.e. when I tried a like clause it told me this would only work against a
> varchar field
> (i'm thinking this may be a schoolboy error so forgive me)
> My main concern is I'd have to do the text conversion automatically, any
> pointers on this?
> Does anyone have any views on the best way to go about this or views on
> holding and full searches again word files
> Many thanks for any help you can give
> Jim Florence
>
>
|||Hilary,
Many thanks for that, very, very useful. I've started playing with the
indexing service as well to try and find a best fit.
I'll give this a go
many thanks for such a quick and informative response
Regards
Jim
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%236F1hKaAGHA.216@.TK2MSFTNGP15.phx.gbl...
> Text means faster indexing times, but not by much. With text you can query
> the columns and read the contents, you can't do this with binary.
> Search SQL is equally as flexible with text and binary. You can only do a
> like against text or char columns.
> To do the conversion use filtdump -b (you can get this from the Platform
> SDK), or you can use ole-automation against the word documents to extract
> the text paragraph by paragraph.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Jim Florence" <florence_james@.hotmail.com> wrote in message
> news:5IKdncUNWPRLowLeRVnyiQ@.pipex.net...
>
I'm putting together a system and one of the requirements is to have a
searchable CV function.
I've got all the code to load the files on to the image fields, I've indexed
and got it kinda working.
Before I go to far down the road what is your opinion on having txt files
instead of doc files held on the table search? The SQL seems to be more
flexible on
searches rather than on the binary files and the index files themselves are
smaller
..i.e. when I tried a like clause it told me this would only work against a
varchar field
(i'm thinking this may be a schoolboy error so forgive me)
My main concern is I'd have to do the text conversion automatically, any
pointers on this?
Does anyone have any views on the best way to go about this or views on
holding and full searches again word files
Many thanks for any help you can give
Jim Florence
Text means faster indexing times, but not by much. With text you can query
the columns and read the contents, you can't do this with binary.
Search SQL is equally as flexible with text and binary. You can only do a
like against text or char columns.
To do the conversion use filtdump -b (you can get this from the Platform
SDK), or you can use ole-automation against the word documents to extract
the text paragraph by paragraph.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jim Florence" <florence_james@.hotmail.com> wrote in message
news:5IKdncUNWPRLowLeRVnyiQ@.pipex.net...
> Hi,
> I'm putting together a system and one of the requirements is to have a
> searchable CV function.
> I've got all the code to load the files on to the image fields, I've
> indexed
> and got it kinda working.
> Before I go to far down the road what is your opinion on having txt files
> instead of doc files held on the table search? The SQL seems to be more
> flexible on
> searches rather than on the binary files and the index files themselves
> are
> smaller
> .i.e. when I tried a like clause it told me this would only work against a
> varchar field
> (i'm thinking this may be a schoolboy error so forgive me)
> My main concern is I'd have to do the text conversion automatically, any
> pointers on this?
> Does anyone have any views on the best way to go about this or views on
> holding and full searches again word files
> Many thanks for any help you can give
> Jim Florence
>
>
|||Hilary,
Many thanks for that, very, very useful. I've started playing with the
indexing service as well to try and find a best fit.
I'll give this a go
many thanks for such a quick and informative response
Regards
Jim
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%236F1hKaAGHA.216@.TK2MSFTNGP15.phx.gbl...
> Text means faster indexing times, but not by much. With text you can query
> the columns and read the contents, you can't do this with binary.
> Search SQL is equally as flexible with text and binary. You can only do a
> like against text or char columns.
> To do the conversion use filtdump -b (you can get this from the Platform
> SDK), or you can use ole-automation against the word documents to extract
> the text paragraph by paragraph.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Jim Florence" <florence_james@.hotmail.com> wrote in message
> news:5IKdncUNWPRLowLeRVnyiQ@.pipex.net...
>
Sunday, March 25, 2012
difference between 'Backup' and DB maint. plan backup?
hi.
ive not setup any backups under 'Management' -> 'Backup' on my SQL2000 box.
instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
is setting up a backup using a maintenance plan the same as setting up a
full backup under the 'Backup' tab?
thanks.Hi,
Backup tab is just creating the Logical Backup device. What you are doing is
exacltly the right procedure to take Full database
and Transaction log backup.
Looks like you are performing backup only for non system database backup,
But i recomend you to do the backup for all the system
databases as well. This will help you in the eve of crash.
Thanks
Hari
SQL Server MVP
"mb" <mb@.discussions.microsoft.com> wrote in message
news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
> hi.
> ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
> box.
> instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
> under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
> is setting up a backup using a maintenance plan the same as setting up a
> full backup under the 'Backup' tab?
> thanks.|||hmm, yes, but i dont believe i have the option of 'remove inactive entries
from transaction log' when creating transaction logs from the maintenance
plans. true?
"Hari Prasad" wrote:
> Hi,
> Backup tab is just creating the Logical Backup device. What you are doing is
> exacltly the right procedure to take Full database
> and Transaction log backup.
> Looks like you are performing backup only for non system database backup,
> But i recomend you to do the backup for all the system
> databases as well. This will help you in the eve of crash.
> Thanks
> Hari
> SQL Server MVP
>
>
> "mb" <mb@.discussions.microsoft.com> wrote in message
> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
> > hi.
> >
> > ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
> > box.
> > instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
> > under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
> >
> > is setting up a backup using a maintenance plan the same as setting up a
> > full backup under the 'Backup' tab?
> >
> > thanks.
>
>|||Btw, I've written an article about this:
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
>> Hi,
>> Backup tab is just creating the Logical Backup device. What you are doing is
>> exacltly the right procedure to take Full database
>> and Transaction log backup.
>> Looks like you are performing backup only for non system database backup,
>> But i recomend you to do the backup for all the system
>> databases as well. This will help you in the eve of crash.
>> Thanks
>> Hari
>> SQL Server MVP
>>
>>
>> "mb" <mb@.discussions.microsoft.com> wrote in message
>> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
>> > hi.
>> >
>> > ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
>> > box.
>> > instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
>> > under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
>> >
>> > is setting up a backup using a maintenance plan the same as setting up a
>> > full backup under the 'Backup' tab?
>> >
>> > thanks.
>>|||Maint plan will not add the NO_TRUNCATE option for the backup log command. This option is what
empties the log and when you uncheck in the backup dialog, this option is added. Terrible GUI design
in the backup dialog (not maint wiz) IMO, and this option has a very special purpose and is only
used for disaster scenarios.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
>> Hi,
>> Backup tab is just creating the Logical Backup device. What you are doing is
>> exacltly the right procedure to take Full database
>> and Transaction log backup.
>> Looks like you are performing backup only for non system database backup,
>> But i recomend you to do the backup for all the system
>> databases as well. This will help you in the eve of crash.
>> Thanks
>> Hari
>> SQL Server MVP
>>
>>
>> "mb" <mb@.discussions.microsoft.com> wrote in message
>> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
>> > hi.
>> >
>> > ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
>> > box.
>> > instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
>> > under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
>> >
>> > is setting up a backup using a maintenance plan the same as setting up a
>> > full backup under the 'Backup' tab?
>> >
>> > thanks.
>>
ive not setup any backups under 'Management' -> 'Backup' on my SQL2000 box.
instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
is setting up a backup using a maintenance plan the same as setting up a
full backup under the 'Backup' tab?
thanks.Hi,
Backup tab is just creating the Logical Backup device. What you are doing is
exacltly the right procedure to take Full database
and Transaction log backup.
Looks like you are performing backup only for non system database backup,
But i recomend you to do the backup for all the system
databases as well. This will help you in the eve of crash.
Thanks
Hari
SQL Server MVP
"mb" <mb@.discussions.microsoft.com> wrote in message
news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
> hi.
> ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
> box.
> instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
> under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
> is setting up a backup using a maintenance plan the same as setting up a
> full backup under the 'Backup' tab?
> thanks.|||hmm, yes, but i dont believe i have the option of 'remove inactive entries
from transaction log' when creating transaction logs from the maintenance
plans. true?
"Hari Prasad" wrote:
> Hi,
> Backup tab is just creating the Logical Backup device. What you are doing is
> exacltly the right procedure to take Full database
> and Transaction log backup.
> Looks like you are performing backup only for non system database backup,
> But i recomend you to do the backup for all the system
> databases as well. This will help you in the eve of crash.
> Thanks
> Hari
> SQL Server MVP
>
>
> "mb" <mb@.discussions.microsoft.com> wrote in message
> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
> > hi.
> >
> > ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
> > box.
> > instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
> > under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
> >
> > is setting up a backup using a maintenance plan the same as setting up a
> > full backup under the 'Backup' tab?
> >
> > thanks.
>
>|||Btw, I've written an article about this:
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
>> Hi,
>> Backup tab is just creating the Logical Backup device. What you are doing is
>> exacltly the right procedure to take Full database
>> and Transaction log backup.
>> Looks like you are performing backup only for non system database backup,
>> But i recomend you to do the backup for all the system
>> databases as well. This will help you in the eve of crash.
>> Thanks
>> Hari
>> SQL Server MVP
>>
>>
>> "mb" <mb@.discussions.microsoft.com> wrote in message
>> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
>> > hi.
>> >
>> > ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
>> > box.
>> > instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
>> > under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
>> >
>> > is setting up a backup using a maintenance plan the same as setting up a
>> > full backup under the 'Backup' tab?
>> >
>> > thanks.
>>|||Maint plan will not add the NO_TRUNCATE option for the backup log command. This option is what
empties the log and when you uncheck in the backup dialog, this option is added. Terrible GUI design
in the backup dialog (not maint wiz) IMO, and this option has a very special purpose and is only
used for disaster scenarios.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
>> Hi,
>> Backup tab is just creating the Logical Backup device. What you are doing is
>> exacltly the right procedure to take Full database
>> and Transaction log backup.
>> Looks like you are performing backup only for non system database backup,
>> But i recomend you to do the backup for all the system
>> databases as well. This will help you in the eve of crash.
>> Thanks
>> Hari
>> SQL Server MVP
>>
>>
>> "mb" <mb@.discussions.microsoft.com> wrote in message
>> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
>> > hi.
>> >
>> > ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
>> > box.
>> > instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
>> > under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
>> >
>> > is setting up a backup using a maintenance plan the same as setting up a
>> > full backup under the 'Backup' tab?
>> >
>> > thanks.
>>
difference between 'Backup' and DB maint. plan backup?
hi.
ive not setup any backups under 'Management' -> 'Backup' on my SQL2000 box.
instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
is setting up a backup using a maintenance plan the same as setting up a
full backup under the 'Backup' tab?
thanks.Hi,
Backup tab is just creating the Logical Backup device. What you are doing is
exacltly the right procedure to take Full database
and Transaction log backup.
Looks like you are performing backup only for non system database backup,
But i recomend you to do the backup for all the system
databases as well. This will help you in the eve of crash.
Thanks
Hari
SQL Server MVP
"mb" <mb@.discussions.microsoft.com> wrote in message
news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
> hi.
> ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
> box.
> instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
> under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
> is setting up a backup using a maintenance plan the same as setting up a
> full backup under the 'Backup' tab?
> thanks.|||hmm, yes, but i dont believe i have the option of 'remove inactive entries
from transaction log' when creating transaction logs from the maintenance
plans. true?
"Hari Prasad" wrote:
> Hi,
> Backup tab is just creating the Logical Backup device. What you are doing
is
> exacltly the right procedure to take Full database
> and Transaction log backup.
> Looks like you are performing backup only for non system database backup,
> But i recomend you to do the backup for all the system
> databases as well. This will help you in the eve of crash.
> Thanks
> Hari
> SQL Server MVP
>
>
> "mb" <mb@.discussions.microsoft.com> wrote in message
> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
>
>|||Maint plan will not add the NO_TRUNCATE option for the backup log command. T
his option is what
empties the log and when you uncheck in the backup dialog, this option is ad
ded. Terrible GUI design
in the backup dialog (not maint wiz) IMO, and this option has a very special
purpose and is only
used for disaster scenarios.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...[vbcol=seagreen]
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
>|||Btw, I've written an article about this:
http://www.karaszi.com/SQLServer/in...no_truncate.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...[vbcol=seagreen]
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
>
ive not setup any backups under 'Management' -> 'Backup' on my SQL2000 box.
instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
is setting up a backup using a maintenance plan the same as setting up a
full backup under the 'Backup' tab?
thanks.Hi,
Backup tab is just creating the Logical Backup device. What you are doing is
exacltly the right procedure to take Full database
and Transaction log backup.
Looks like you are performing backup only for non system database backup,
But i recomend you to do the backup for all the system
databases as well. This will help you in the eve of crash.
Thanks
Hari
SQL Server MVP
"mb" <mb@.discussions.microsoft.com> wrote in message
news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
> hi.
> ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
> box.
> instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
> under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
> is setting up a backup using a maintenance plan the same as setting up a
> full backup under the 'Backup' tab?
> thanks.|||hmm, yes, but i dont believe i have the option of 'remove inactive entries
from transaction log' when creating transaction logs from the maintenance
plans. true?
"Hari Prasad" wrote:
> Hi,
> Backup tab is just creating the Logical Backup device. What you are doing
is
> exacltly the right procedure to take Full database
> and Transaction log backup.
> Looks like you are performing backup only for non system database backup,
> But i recomend you to do the backup for all the system
> databases as well. This will help you in the eve of crash.
> Thanks
> Hari
> SQL Server MVP
>
>
> "mb" <mb@.discussions.microsoft.com> wrote in message
> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
>
>|||Maint plan will not add the NO_TRUNCATE option for the backup log command. T
his option is what
empties the log and when you uncheck in the backup dialog, this option is ad
ded. Terrible GUI design
in the backup dialog (not maint wiz) IMO, and this option has a very special
purpose and is only
used for disaster scenarios.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...[vbcol=seagreen]
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
>|||Btw, I've written an article about this:
http://www.karaszi.com/SQLServer/in...no_truncate.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...[vbcol=seagreen]
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
>
difference between 'Backup' and DB maint. plan backup?
hi.
ive not setup any backups under 'Management' -> 'Backup' on my SQL2000 box.
instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
is setting up a backup using a maintenance plan the same as setting up a
full backup under the 'Backup' tab?
thanks.
Hi,
Backup tab is just creating the Logical Backup device. What you are doing is
exacltly the right procedure to take Full database
and Transaction log backup.
Looks like you are performing backup only for non system database backup,
But i recomend you to do the backup for all the system
databases as well. This will help you in the eve of crash.
Thanks
Hari
SQL Server MVP
"mb" <mb@.discussions.microsoft.com> wrote in message
news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
> hi.
> ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
> box.
> instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
> under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
> is setting up a backup using a maintenance plan the same as setting up a
> full backup under the 'Backup' tab?
> thanks.
|||hmm, yes, but i dont believe i have the option of 'remove inactive entries
from transaction log' when creating transaction logs from the maintenance
plans. true?
"Hari Prasad" wrote:
> Hi,
> Backup tab is just creating the Logical Backup device. What you are doing is
> exacltly the right procedure to take Full database
> and Transaction log backup.
> Looks like you are performing backup only for non system database backup,
> But i recomend you to do the backup for all the system
> databases as well. This will help you in the eve of crash.
> Thanks
> Hari
> SQL Server MVP
>
>
> "mb" <mb@.discussions.microsoft.com> wrote in message
> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
>
>
|||Maint plan will not add the NO_TRUNCATE option for the backup log command. This option is what
empties the log and when you uncheck in the backup dialog, this option is added. Terrible GUI design
in the backup dialog (not maint wiz) IMO, and this option has a very special purpose and is only
used for disaster scenarios.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...[vbcol=seagreen]
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
|||Btw, I've written an article about this:
http://www.karaszi.com/SQLServer/inf...o_truncate.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...[vbcol=seagreen]
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
ive not setup any backups under 'Management' -> 'Backup' on my SQL2000 box.
instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
is setting up a backup using a maintenance plan the same as setting up a
full backup under the 'Backup' tab?
thanks.
Hi,
Backup tab is just creating the Logical Backup device. What you are doing is
exacltly the right procedure to take Full database
and Transaction log backup.
Looks like you are performing backup only for non system database backup,
But i recomend you to do the backup for all the system
databases as well. This will help you in the eve of crash.
Thanks
Hari
SQL Server MVP
"mb" <mb@.discussions.microsoft.com> wrote in message
news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
> hi.
> ive not setup any backups under 'Management' -> 'Backup' on my SQL2000
> box.
> instead ive used the 'Complete Backup' and ' Transaction Log Backup' tab
> under Database Maintenance Plans -> 'NonSystemDatabaseMaintenance'.
> is setting up a backup using a maintenance plan the same as setting up a
> full backup under the 'Backup' tab?
> thanks.
|||hmm, yes, but i dont believe i have the option of 'remove inactive entries
from transaction log' when creating transaction logs from the maintenance
plans. true?
"Hari Prasad" wrote:
> Hi,
> Backup tab is just creating the Logical Backup device. What you are doing is
> exacltly the right procedure to take Full database
> and Transaction log backup.
> Looks like you are performing backup only for non system database backup,
> But i recomend you to do the backup for all the system
> databases as well. This will help you in the eve of crash.
> Thanks
> Hari
> SQL Server MVP
>
>
> "mb" <mb@.discussions.microsoft.com> wrote in message
> news:C67C8B52-07C6-40D0-A722-2BCE97582372@.microsoft.com...
>
>
|||Maint plan will not add the NO_TRUNCATE option for the backup log command. This option is what
empties the log and when you uncheck in the backup dialog, this option is added. Terrible GUI design
in the backup dialog (not maint wiz) IMO, and this option has a very special purpose and is only
used for disaster scenarios.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...[vbcol=seagreen]
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
|||Btw, I've written an article about this:
http://www.karaszi.com/SQLServer/inf...o_truncate.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mb" <mb@.discussions.microsoft.com> wrote in message
news:5E78EC73-4EBA-4DAD-836E-3EBAD9D3BFA8@.microsoft.com...[vbcol=seagreen]
> hmm, yes, but i dont believe i have the option of 'remove inactive entries
> from transaction log' when creating transaction logs from the maintenance
> plans. true?
> "Hari Prasad" wrote:
Wednesday, March 7, 2012
Development & Lives databases
Hello all, apologies if i've posted this in the wrong forum!
I have 2 databases development and live. Development has the correct
structure (SP's and tables) and live has the correct data. I would like to
make live match the development database but not to loose any data. Is
replication the best method?
If so, any simple www's that could help me?
Thanks all,
Jon
you have to pick an authority. Sounds like you have to ensure that live has
the correct schema, and then push your live data to development.
Transactional replication is ideal for this. It does require that every
table has a pk though, which it should.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:128F3236-EBD2-4034-8A3A-A72248CD3011@.microsoft.com...
> Hello all, apologies if i've posted this in the wrong forum!
> I have 2 databases development and live. Development has the correct
> structure (SP's and tables) and live has the correct data. I would like to
> make live match the development database but not to loose any data. Is
> replication the best method?
> If so, any simple www's that could help me?
> Thanks all,
> Jon
I have 2 databases development and live. Development has the correct
structure (SP's and tables) and live has the correct data. I would like to
make live match the development database but not to loose any data. Is
replication the best method?
If so, any simple www's that could help me?
Thanks all,
Jon
you have to pick an authority. Sounds like you have to ensure that live has
the correct schema, and then push your live data to development.
Transactional replication is ideal for this. It does require that every
table has a pk though, which it should.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:128F3236-EBD2-4034-8A3A-A72248CD3011@.microsoft.com...
> Hello all, apologies if i've posted this in the wrong forum!
> I have 2 databases development and live. Development has the correct
> structure (SP's and tables) and live has the correct data. I would like to
> make live match the development database but not to loose any data. Is
> replication the best method?
> If so, any simple www's that could help me?
> Thanks all,
> Jon
Development - Test Environment Questions
Hi,
I've been given the task of creating a dev/test environment. Currently we
have several production applications using databases on a common SQL Server.
If changes are required, the developers are performing the changes on the
production system - yea I know BAD,BAD,BAD - but I didn't set this up but
instead inherited it. I'd like to configure a dev/test environment to move
the devs off of the production system and to faciliate their development of
future projects coming up soon.
How is your dev/test environment configured? I'm looking for a few examples
here that I can work with to implement our dev/test based on our budget and
system capabilities.
I.e., Each dev has their own sandbox or each dev shares a common dev env -
then changes are implemented to test env (and by who - dev or dba - and
how - scripts) etc... then scripted to deploy on prod systems etc... also
we image/ghost systems or use VMs and also we use Visual SourceSafe to...
Trying to come up with a solid game plan here.
Thanks for any input.
Jerry
Jerry Spivey wrote:
> Hi,
> I've been given the task of creating a dev/test environment. Currently
> we have several production applications using databases on
> a common SQL Server. If changes are required, the developers are
> performing the changes on the production system - yea I know
> BAD,BAD,BAD - but I didn't set this up but instead inherited it. I'd
> like to configure a dev/test environment to move the devs off of the
> production system and to faciliate their development of future
> projects coming up soon.
> How is your dev/test environment configured? I'm looking for a few
> examples here that I can work with to implement our dev/test based on
> our budget and system capabilities.
> I.e., Each dev has their own sandbox or each dev shares a common dev
> env - then changes are implemented to test env (and by who - dev or
> dba - and how - scripts) etc... then scripted to deploy on prod
> systems etc... also we image/ghost systems or use VMs and also we use
> Visual
> SourceSafe to...
> Trying to come up with a solid game plan here.
> Thanks for any input.
> Jerry
At most companies where I've worked in the past, we had development
servers that were used strictly for development. They generally
contained stripped down data from the production databases with any
customer sensitive data masked. Lead developers generally had dbo rights
and may even have admin rights depending on the size of the company.
Version control software was used for all object changes. Initial
application testing was done on the dev servers.
QA/Test servers were not managed by development. They were usually owned
by QA group. We would provide detailed scripts to update QA servers with
the necessary changes. Users would test the applications on QA servers.
QA servers have data that more closely mimics production in terms of
data value distribution and quantity and may even be created from
production backups. Sensitive data was not masked back then, as I
recall, but it may have to be today. QA has the ability to reload the
database in case the migration to QA fails. It's important to be able to
always start from an exact copy of the production database schema.
These days, you can use multiple SQL Server Instances to save hardware
(assuming you have the necessary memory). VMs, while convenient, are
probably not ideal for performance testing. But I'm not well informed
about the capabilites of the server VM products.
If everything tested ok, the updates were escalated to production.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Thanks David.
Looking into using named instances with EE to help control costs. Will be
looking into implementing Visual SourceSafe as well.
I noticed you work for Quest. I have a few questions about the Quest
Central if you're open to them. Please email me if so.
Thanks
Jerry
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23YygmKIaFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Jerry Spivey wrote:
> At most companies where I've worked in the past, we had development
> servers that were used strictly for development. They generally contained
> stripped down data from the production databases with any customer
> sensitive data masked. Lead developers generally had dbo rights and may
> even have admin rights depending on the size of the company. Version
> control software was used for all object changes. Initial application
> testing was done on the dev servers.
> QA/Test servers were not managed by development. They were usually owned
> by QA group. We would provide detailed scripts to update QA servers with
> the necessary changes. Users would test the applications on QA servers. QA
> servers have data that more closely mimics production in terms of data
> value distribution and quantity and may even be created from production
> backups. Sensitive data was not masked back then, as I recall, but it may
> have to be today. QA has the ability to reload the database in case the
> migration to QA fails. It's important to be able to always start from an
> exact copy of the production database schema.
> These days, you can use multiple SQL Server Instances to save hardware
> (assuming you have the necessary memory). VMs, while convenient, are
> probably not ideal for performance testing. But I'm not well informed
> about the capabilites of the server VM products.
> If everything tested ok, the updates were escalated to production.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Jerry Spivey wrote:
> Thanks David.
> Looking into using named instances with EE to help control costs. Will
> be looking into implementing Visual SourceSafe as well.
> I noticed you work for Quest. I have a few questions about the Quest
> Central if you're open to them. Please email me if so.
> Thanks
> Jerry
>
The best way for you to get information and help with the Quest product
line is to contact sales. Our offices and numbers are located here:
http://www.quest.com/company/us_offices.asp
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Change management is the term.
http://www.innovartis.co.uk/pdf/Inno...ange_Mgt. pdf
This is a white paper on the subject using Source Control (Visual Source
Safe) as the back bone of the approach. The application DB Ghost
(www.dbghost.com) was built using this methodology.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Jerry Spivey" wrote:
> Hi,
> I've been given the task of creating a dev/test environment. Currently we
> have several production applications using databases on a common SQL Server.
> If changes are required, the developers are performing the changes on the
> production system - yea I know BAD,BAD,BAD - but I didn't set this up but
> instead inherited it. I'd like to configure a dev/test environment to move
> the devs off of the production system and to faciliate their development of
> future projects coming up soon.
>
> How is your dev/test environment configured? I'm looking for a few examples
> here that I can work with to implement our dev/test based on our budget and
> system capabilities.
> I.e., Each dev has their own sandbox or each dev shares a common dev env -
> then changes are implemented to test env (and by who - dev or dba - and
> how - scripts) etc... then scripted to deploy on prod systems etc... also
> we image/ghost systems or use VMs and also we use Visual SourceSafe to...
> Trying to come up with a solid game plan here.
> Thanks for any input.
> Jerry
>
>
I've been given the task of creating a dev/test environment. Currently we
have several production applications using databases on a common SQL Server.
If changes are required, the developers are performing the changes on the
production system - yea I know BAD,BAD,BAD - but I didn't set this up but
instead inherited it. I'd like to configure a dev/test environment to move
the devs off of the production system and to faciliate their development of
future projects coming up soon.
How is your dev/test environment configured? I'm looking for a few examples
here that I can work with to implement our dev/test based on our budget and
system capabilities.
I.e., Each dev has their own sandbox or each dev shares a common dev env -
then changes are implemented to test env (and by who - dev or dba - and
how - scripts) etc... then scripted to deploy on prod systems etc... also
we image/ghost systems or use VMs and also we use Visual SourceSafe to...
Trying to come up with a solid game plan here.
Thanks for any input.
Jerry
Jerry Spivey wrote:
> Hi,
> I've been given the task of creating a dev/test environment. Currently
> we have several production applications using databases on
> a common SQL Server. If changes are required, the developers are
> performing the changes on the production system - yea I know
> BAD,BAD,BAD - but I didn't set this up but instead inherited it. I'd
> like to configure a dev/test environment to move the devs off of the
> production system and to faciliate their development of future
> projects coming up soon.
> How is your dev/test environment configured? I'm looking for a few
> examples here that I can work with to implement our dev/test based on
> our budget and system capabilities.
> I.e., Each dev has their own sandbox or each dev shares a common dev
> env - then changes are implemented to test env (and by who - dev or
> dba - and how - scripts) etc... then scripted to deploy on prod
> systems etc... also we image/ghost systems or use VMs and also we use
> Visual
> SourceSafe to...
> Trying to come up with a solid game plan here.
> Thanks for any input.
> Jerry
At most companies where I've worked in the past, we had development
servers that were used strictly for development. They generally
contained stripped down data from the production databases with any
customer sensitive data masked. Lead developers generally had dbo rights
and may even have admin rights depending on the size of the company.
Version control software was used for all object changes. Initial
application testing was done on the dev servers.
QA/Test servers were not managed by development. They were usually owned
by QA group. We would provide detailed scripts to update QA servers with
the necessary changes. Users would test the applications on QA servers.
QA servers have data that more closely mimics production in terms of
data value distribution and quantity and may even be created from
production backups. Sensitive data was not masked back then, as I
recall, but it may have to be today. QA has the ability to reload the
database in case the migration to QA fails. It's important to be able to
always start from an exact copy of the production database schema.
These days, you can use multiple SQL Server Instances to save hardware
(assuming you have the necessary memory). VMs, while convenient, are
probably not ideal for performance testing. But I'm not well informed
about the capabilites of the server VM products.
If everything tested ok, the updates were escalated to production.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Thanks David.
Looking into using named instances with EE to help control costs. Will be
looking into implementing Visual SourceSafe as well.
I noticed you work for Quest. I have a few questions about the Quest
Central if you're open to them. Please email me if so.
Thanks
Jerry
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23YygmKIaFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Jerry Spivey wrote:
> At most companies where I've worked in the past, we had development
> servers that were used strictly for development. They generally contained
> stripped down data from the production databases with any customer
> sensitive data masked. Lead developers generally had dbo rights and may
> even have admin rights depending on the size of the company. Version
> control software was used for all object changes. Initial application
> testing was done on the dev servers.
> QA/Test servers were not managed by development. They were usually owned
> by QA group. We would provide detailed scripts to update QA servers with
> the necessary changes. Users would test the applications on QA servers. QA
> servers have data that more closely mimics production in terms of data
> value distribution and quantity and may even be created from production
> backups. Sensitive data was not masked back then, as I recall, but it may
> have to be today. QA has the ability to reload the database in case the
> migration to QA fails. It's important to be able to always start from an
> exact copy of the production database schema.
> These days, you can use multiple SQL Server Instances to save hardware
> (assuming you have the necessary memory). VMs, while convenient, are
> probably not ideal for performance testing. But I'm not well informed
> about the capabilites of the server VM products.
> If everything tested ok, the updates were escalated to production.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Jerry Spivey wrote:
> Thanks David.
> Looking into using named instances with EE to help control costs. Will
> be looking into implementing Visual SourceSafe as well.
> I noticed you work for Quest. I have a few questions about the Quest
> Central if you're open to them. Please email me if so.
> Thanks
> Jerry
>
The best way for you to get information and help with the Quest product
line is to contact sales. Our offices and numbers are located here:
http://www.quest.com/company/us_offices.asp
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Change management is the term.
http://www.innovartis.co.uk/pdf/Inno...ange_Mgt. pdf
This is a white paper on the subject using Source Control (Visual Source
Safe) as the back bone of the approach. The application DB Ghost
(www.dbghost.com) was built using this methodology.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Jerry Spivey" wrote:
> Hi,
> I've been given the task of creating a dev/test environment. Currently we
> have several production applications using databases on a common SQL Server.
> If changes are required, the developers are performing the changes on the
> production system - yea I know BAD,BAD,BAD - but I didn't set this up but
> instead inherited it. I'd like to configure a dev/test environment to move
> the devs off of the production system and to faciliate their development of
> future projects coming up soon.
>
> How is your dev/test environment configured? I'm looking for a few examples
> here that I can work with to implement our dev/test based on our budget and
> system capabilities.
> I.e., Each dev has their own sandbox or each dev shares a common dev env -
> then changes are implemented to test env (and by who - dev or dba - and
> how - scripts) etc... then scripted to deploy on prod systems etc... also
> we image/ghost systems or use VMs and also we use Visual SourceSafe to...
> Trying to come up with a solid game plan here.
> Thanks for any input.
> Jerry
>
>
Saturday, February 25, 2012
Developing the clever backup strategy
Hi there...
I've been reading around in the BOL to figure out what backup strategy is the
"best" available. The database I want to backup, is ~4.5 after a shrink, but it
of course grows after a while. Same with the log.
The database runs in "full recovery" mode currently, and it is being used during
a normal workday, by around 15 people. What backup setup is the recommended in
this situation?
I've tried setting it up to backup the database every 4 hours, and the log every
30 minutes. This generates a /lot/ of *.trn files, is there anyway to
"consolidate" those after a day? Secondly, when I do a database backup every 24
hours, does that mean that I can then afterward safely truncate the log (I like
keeping things neat
? What do I do with the *.bak and *.trn when they are over
two days old?
I doubt, therefore I might be.
I'd suggest a good, thorough read through of books on line to make sure you
understand things like truncating the logs, full recovery more, simple
recovery mode, etc.
Truncating the logs should probably never be done which is why I suggest this.
burt_king@.yahoo.com
"Kim Noer" wrote:
> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is the
> "best" available. The database I want to backup, is ~4.5 after a shrink, but it
> of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used during
> a normal workday, by around 15 people. What backup setup is the recommended in
> this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log every
> 30 minutes. This generates a /lot/ of *.trn files, is there anyway to
> "consolidate" those after a day? Secondly, when I do a database backup every 24
> hours, does that mean that I can then afterward safely truncate the log (I like
> keeping things neat? What do I do with the *.bak and *.trn when they are over
> two days old?
> --
> I doubt, therefore I might be.
>
|||I definitely agree. Also, be restrictive using shrink, for more info, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"burt_king" <burt_king@.yahoo.com> wrote in message
news:193B7308-3D54-426E-AF7A-B9983D509983@.microsoft.com...[vbcol=seagreen]
> I'd suggest a good, thorough read through of books on line to make sure you
> understand things like truncating the logs, full recovery more, simple
> recovery mode, etc.
> Truncating the logs should probably never be done which is why I suggest this.
>
> --
> burt_king@.yahoo.com
>
> "Kim Noer" wrote:
|||Usually in a smaller environment like you describe I reccomend a daily full
backup and then differential backups throughout the day. With this scenario
you can put the database in simple recovery mode and not have to worry about
log file growth. I have seen too many environments where they left the
databases in Full Recovery (default) and never back up the logs. A couple
weeks go by and the database is refusing transactions because the disk is
full.
I truncate and shrink the logs every night to make sure it gets done. One
big Insert or something else can throw your log files out of whack. With
this you should make sure your minimum log file is big enough that it does
not have to grow signifigantly during a normal work day.
When you say your DB is 4.5G (I assume G) after the shrink, how big is it
before the shrink? If your database files are shrinking signifigantly
(>20%) every night you need to look at your clustered indexes and fill
factors to make sure there is enough room that the database is not having to
auto grow throughout the day. Also, if you have not done reindexed in a
while, you should look at doing that - see DBCC Reindex (note that this
should be done when the database is not being accessed).
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%231ouK%2327FHA.2816@.tk2msftngp13.phx.gbl...
> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is
> the "best" available. The database I want to backup, is ~4.5 after a
> shrink, but it of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used
> during a normal workday, by around 15 people. What backup setup is the
> recommended in this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log
> every 30 minutes. This generates a /lot/ of *.trn files, is there anyway
> to "consolidate" those after a day? Secondly, when I do a database backup
> every 24 hours, does that mean that I can then afterward safely truncate
> the log (I like keeping things neat? What do I do with the *.bak and
> *.trn when they are over two days old?
> --
> I doubt, therefore I might be.
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in message news:uHbwqP37FHA.1140@.tk2msftngp13.phx.gbl
> I definitely agree. Also, be restrictive using shrink, for more info,
> see http://www.karaszi.com/SQLServer/info_dont_shrink.asp
I've read your excellent guide, and I think I actually more now than before.
Anyway. Currently the database runs in "full recovery" mode. Then I did a
complete backup of the database, and then I tried a dbcc loginfo('database')
Now, according to your guide, status 2 means the VLF is in use. The result is
this -
2 253952 8192 65770 0 64 0
2 253952 262144 65769 0 64 0
2 253952 516096 65768 0 128 0
2 278528 770048 65771 0 64 0
2 262144 1048576 65772 0 128 40875000000042200043
...
And this continues all the way to row 308 (your guide tells me I should set the
allocated size way above the current) -
2 27000832 2188771328 65773 2 64 65760000004588300008
2 27000832 2215772160 65766 0 64 65760000004588300008
As you might be able to see, row 308 have the status 2. Doesn't this mean that
this logfile will never go below 2.06GB unless I truncate it manually? Secondly,
what about the VLF's below, since they are unused will this cause SQL Server to
reuse the VLF, or will it proceed from CreateLSN 65760000004588300008?
I doubt, therefore I might be.
|||"Kim Noer" <kn@.nospam.dk> wrote in message
news:OZD%23gFE8FHA.1140@.tk2msftngp13.phx.gbl
> Secondly, what about the VLF's below, since they are
> unused will this cause SQL Server to reuse the VLF, or will it
> proceed from CreateLSN 65760000004588300008?
Nevermind, I decided to use your guide as test, that is, create a table, then
insert a lot of records, which showed me that SQL Server reuses unused VLF, even
when they are "before" used VLF's.
I doubt, therefore I might be.
I've been reading around in the BOL to figure out what backup strategy is the
"best" available. The database I want to backup, is ~4.5 after a shrink, but it
of course grows after a while. Same with the log.
The database runs in "full recovery" mode currently, and it is being used during
a normal workday, by around 15 people. What backup setup is the recommended in
this situation?
I've tried setting it up to backup the database every 4 hours, and the log every
30 minutes. This generates a /lot/ of *.trn files, is there anyway to
"consolidate" those after a day? Secondly, when I do a database backup every 24
hours, does that mean that I can then afterward safely truncate the log (I like
keeping things neat
? What do I do with the *.bak and *.trn when they are overtwo days old?
I doubt, therefore I might be.
I'd suggest a good, thorough read through of books on line to make sure you
understand things like truncating the logs, full recovery more, simple
recovery mode, etc.
Truncating the logs should probably never be done which is why I suggest this.
burt_king@.yahoo.com
"Kim Noer" wrote:
> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is the
> "best" available. The database I want to backup, is ~4.5 after a shrink, but it
> of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used during
> a normal workday, by around 15 people. What backup setup is the recommended in
> this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log every
> 30 minutes. This generates a /lot/ of *.trn files, is there anyway to
> "consolidate" those after a day? Secondly, when I do a database backup every 24
> hours, does that mean that I can then afterward safely truncate the log (I like
> keeping things neat
? What do I do with the *.bak and *.trn when they are over> two days old?
> --
> I doubt, therefore I might be.
>
|||I definitely agree. Also, be restrictive using shrink, for more info, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"burt_king" <burt_king@.yahoo.com> wrote in message
news:193B7308-3D54-426E-AF7A-B9983D509983@.microsoft.com...[vbcol=seagreen]
> I'd suggest a good, thorough read through of books on line to make sure you
> understand things like truncating the logs, full recovery more, simple
> recovery mode, etc.
> Truncating the logs should probably never be done which is why I suggest this.
>
> --
> burt_king@.yahoo.com
>
> "Kim Noer" wrote:
|||Usually in a smaller environment like you describe I reccomend a daily full
backup and then differential backups throughout the day. With this scenario
you can put the database in simple recovery mode and not have to worry about
log file growth. I have seen too many environments where they left the
databases in Full Recovery (default) and never back up the logs. A couple
weeks go by and the database is refusing transactions because the disk is
full.
I truncate and shrink the logs every night to make sure it gets done. One
big Insert or something else can throw your log files out of whack. With
this you should make sure your minimum log file is big enough that it does
not have to grow signifigantly during a normal work day.
When you say your DB is 4.5G (I assume G) after the shrink, how big is it
before the shrink? If your database files are shrinking signifigantly
(>20%) every night you need to look at your clustered indexes and fill
factors to make sure there is enough room that the database is not having to
auto grow throughout the day. Also, if you have not done reindexed in a
while, you should look at doing that - see DBCC Reindex (note that this
should be done when the database is not being accessed).
"Kim Noer" <kn@.nospam.dk> wrote in message
news:%231ouK%2327FHA.2816@.tk2msftngp13.phx.gbl...
> Hi there...
> I've been reading around in the BOL to figure out what backup strategy is
> the "best" available. The database I want to backup, is ~4.5 after a
> shrink, but it of course grows after a while. Same with the log.
> The database runs in "full recovery" mode currently, and it is being used
> during a normal workday, by around 15 people. What backup setup is the
> recommended in this situation?
>
> I've tried setting it up to backup the database every 4 hours, and the log
> every 30 minutes. This generates a /lot/ of *.trn files, is there anyway
> to "consolidate" those after a day? Secondly, when I do a database backup
> every 24 hours, does that mean that I can then afterward safely truncate
> the log (I like keeping things neat
? What do I do with the *.bak and> *.trn when they are over two days old?
> --
> I doubt, therefore I might be.
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in message news:uHbwqP37FHA.1140@.tk2msftngp13.phx.gbl
> I definitely agree. Also, be restrictive using shrink, for more info,
> see http://www.karaszi.com/SQLServer/info_dont_shrink.asp
I've read your excellent guide, and I think I actually more now than before
.Anyway. Currently the database runs in "full recovery" mode. Then I did a
complete backup of the database, and then I tried a dbcc loginfo('database')
Now, according to your guide, status 2 means the VLF is in use. The result is
this -
2 253952 8192 65770 0 64 0
2 253952 262144 65769 0 64 0
2 253952 516096 65768 0 128 0
2 278528 770048 65771 0 64 0
2 262144 1048576 65772 0 128 40875000000042200043
...
And this continues all the way to row 308 (your guide tells me I should set the
allocated size way above the current) -
2 27000832 2188771328 65773 2 64 65760000004588300008
2 27000832 2215772160 65766 0 64 65760000004588300008
As you might be able to see, row 308 have the status 2. Doesn't this mean that
this logfile will never go below 2.06GB unless I truncate it manually? Secondly,
what about the VLF's below, since they are unused will this cause SQL Server to
reuse the VLF, or will it proceed from CreateLSN 65760000004588300008?
I doubt, therefore I might be.
|||"Kim Noer" <kn@.nospam.dk> wrote in message
news:OZD%23gFE8FHA.1140@.tk2msftngp13.phx.gbl
> Secondly, what about the VLF's below, since they are
> unused will this cause SQL Server to reuse the VLF, or will it
> proceed from CreateLSN 65760000004588300008?
Nevermind, I decided to use your guide as test, that is, create a table, then
insert a lot of records, which showed me that SQL Server reuses unused VLF, even
when they are "before" used VLF's.
I doubt, therefore I might be.
Friday, February 24, 2012
Developer to DBA role
This is probably a wide open question but:
I'm coming from a developer role for the pass 12 years. I've created
everything from, components, web app, windows apps, mobile device
applications, etc. Now I'm getting ready to take over work on our databases,
Our DBA has left and I'm kind of stepping in to take on that role. So my
question is, coming from developer background, what do I need to know beyond
creating stored procedures and select statements to be a successful DBA? I
know from in the pass in working with DBA's that they do more then just
create stored procedures and database, what do I need to know such as,
replication, mirroring, security, performance, etc. where do I begin, is
there anything out there to help me get an understanding of a DBA role, or
should I just 'fly by the seam of my pants' on this one? (probably not a
good idea, but willing to try it)
thanks for any help and or suggestions you may have to offer, (good or bad
are accepted)
just to add I know how to create users in the database, SSIS packages, I
guess its more on triggers, indexes, performance issues, replication,
mirroring, etc, suff like that I guess is what i'm looking for.
and other things a DBA does within this role.
"Mike" <Mike@.community.nospam.com> wrote in message
news:%23G$Syd3rHHA.1168@.TK2MSFTNGP03.phx.gbl...
> This is probably a wide open question but:
> I'm coming from a developer role for the pass 12 years. I've created
> everything from, components, web app, windows apps, mobile device
> applications, etc. Now I'm getting ready to take over work on our
> databases, Our DBA has left and I'm kind of stepping in to take on that
> role. So my question is, coming from developer background, what do I need
> to know beyond creating stored procedures and select statements to be a
> successful DBA? I know from in the pass in working with DBA's that they do
> more then just create stored procedures and database, what do I need to
> know such as, replication, mirroring, security, performance, etc. where do
> I begin, is there anything out there to help me get an understanding of a
> DBA role, or should I just 'fly by the seam of my pants' on this one?
> (probably not a good idea, but willing to try it)
>
> thanks for any help and or suggestions you may have to offer, (good or
> bad are accepted)
>
>
|||http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/f696a9ae540bf1a0/01482ed3de91192f?hl=en#01482ed3de91192f
|||I was in the same boat. Microsoft has some really good training
classes and I'd highly recommend getting into one of those as soon as
you can. They'll cover a broad range of your day to day operations
like backups and security. Also, get familiar with some networking
knowledge as well. You're going to be walking the line between
hardware and software. But, the first thing is with only SQL Dev
experience I'd see what, if any, training classes you can get into. If
anything the book can be used for reference for TSQL statements.
|||First and most importantly, you need to be able to put the system back the
way it was yesterday, data and all. Fully document the system and make sure
your recovery strategy is in place and tested.
Second, all developers are now your natural enemy. They write code to
destroy the data and performance. It is your job to limit the damage they
can do. See requirement #1.
Performance monitoring and tuning must now be regular parts of your routine.
Security just became your problem. You don't want to be the next headline
on data lost or hacked.
There are lots more, but this should keep you up nights for the next few
months.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Mike" <Mike@.community.nospam.com> wrote in message
news:OinW2i3rHHA.632@.TK2MSFTNGP03.phx.gbl...
> just to add I know how to create users in the database, SSIS packages, I
> guess its more on triggers, indexes, performance issues, replication,
> mirroring, etc, suff like that I guess is what i'm looking for.
> and other things a DBA does within this role.
>
> "Mike" <Mike@.community.nospam.com> wrote in message
> news:%23G$Syd3rHHA.1168@.TK2MSFTNGP03.phx.gbl...
>
|||Thanks, I'm now second guessing myself for taken this task on and learn
something new.
> First and most importantly, you need to be able to put the system back the
> way it was yesterday, data and all. Fully document the system and make
> sure your recovery strategy is in place and tested.
I can say I was successful in restoring all the databases when the
server actually crashed. I learned that process hands on. The only thing
though I had to add all of the logins again manually. Is there a way to back
up the 'Security' folder so I can all Login ID and Privilages or no?
> Second, all developers are now your natural enemy. They write code to
> destroy the data and performance. It is your job to limit the damage they
> can do. See requirement #1.
Coming from development, I admit I either brought down a DB or locked a db
in my development days.
> Security just became your problem. You don't want to be the next headline
> on data lost or hacked.
this part I like because I handled alot of the security for the
applications I developed. Though this is a different level of security.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OtZbfu3rHHA.1476@.TK2MSFTNGP06.phx.gbl...
> First and most importantly, you need to be able to put the system back the
> way it was yesterday, data and all. Fully document the system and make
> sure your recovery strategy is in place and tested.
> Second, all developers are now your natural enemy. They write code to
> destroy the data and performance. It is your job to limit the damage they
> can do. See requirement #1.
> Performance monitoring and tuning must now be regular parts of your
> routine.
> Security just became your problem. You don't want to be the next headline
> on data lost or hacked.
> There are lots more, but this should keep you up nights for the next few
> months.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
>
> "Mike" <Mike@.community.nospam.com> wrote in message
> news:OinW2i3rHHA.632@.TK2MSFTNGP03.phx.gbl...
>
|||what about replication, mirroring, or clustering? Or should I not worry
about that until the time comes? According to the mgr none of the above are
used Just backups of the db's on a nightly basis.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OtZbfu3rHHA.1476@.TK2MSFTNGP06.phx.gbl...
> First and most importantly, you need to be able to put the system back the
> way it was yesterday, data and all. Fully document the system and make
> sure your recovery strategy is in place and tested.
> Second, all developers are now your natural enemy. They write code to
> destroy the data and performance. It is your job to limit the damage they
> can do. See requirement #1.
> Performance monitoring and tuning must now be regular parts of your
> routine.
> Security just became your problem. You don't want to be the next headline
> on data lost or hacked.
> There are lots more, but this should keep you up nights for the next few
> months.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
>
> "Mike" <Mike@.community.nospam.com> wrote in message
> news:OinW2i3rHHA.632@.TK2MSFTNGP03.phx.gbl...
>
|||Hey, not ALL developers are bad. Just the great majority. Coming out
of the trenches yourself you probably get along well with them so when
you see a performance problem they will most likely be very receptive
to your suggestions on how to fix it. SQL Profiler and Performance
Monitor have just become some of your best friends. Though they do add
overhead to the server so use them sparingly.
This site may help you figure out some more details about performance
monitors:
http://www.sql-server-performance.com/
Here's a link on what your day-to-day's may include:
http://www.databasedesign-resource.com/sql-server-dba.html
On Jun 15, 2:33 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:[vbcol=seagreen]
> First and most importantly, you need to be able to put the system back the
> way it was yesterday, data and all. Fully document the system and make sure
> your recovery strategy is in place and tested.
> Second, all developers are now your natural enemy. They write code to
> destroy the data and performance. It is your job to limit the damage they
> can do. See requirement #1.
> Performance monitoring and tuning must now be regular parts of your routine.
> Security just became your problem. You don't want to be the next headline
> on data lost or hacked.
> There are lots more, but this should keep you up nights for the next few
> months.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Mike" <M...@.community.nospam.com> wrote in message
> news:OinW2i3rHHA.632@.TK2MSFTNGP03.phx.gbl...
>
>
|||thanks I greatly appreciate the information.
This Database stuff is a whole new world to me (from a true dba perspective)
and I was willing to give it a shot.
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181933280.917354.9000@.p77g2000hsh.googlegrou ps.com...
> Hey, not ALL developers are bad. Just the great majority. Coming out
> of the trenches yourself you probably get along well with them so when
> you see a performance problem they will most likely be very receptive
> to your suggestions on how to fix it. SQL Profiler and Performance
> Monitor have just become some of your best friends. Though they do add
> overhead to the server so use them sparingly.
> This site may help you figure out some more details about performance
> monitors:
> http://www.sql-server-performance.com/
> Here's a link on what your day-to-day's may include:
> http://www.databasedesign-resource.com/sql-server-dba.html
> On Jun 15, 2:33 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>
>
|||Unless you are currently using or expect to use a technology, I would not
worry about it on day one. Getting a handle on managing, recovering, and
operating the server AS IS should be your top priority. Learn about what
new technologies can and cannot do so you can make intelligent
recommendations, but unless they are part of your current environment, they
are not part of your current problem set.
As for the developer comments, I have worked as a developer, a system admin,
and as a DBA over the course of my career. Each has a different perspective
on the system and different goals. Sometimes those goals are in wildly
different directions.
Don't rely on luck to restore a system. Have a plan and practice it. Test
your backups regularly. Training classes will help give you the knowledge
to build an effective operations and recovery plan, but you have to do the
heavy lifting. Remember, management does not care about backups, they care
about restores.
Good luck. I figure we will see you around here quite a bit in the months
to come.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Mike" <Mike@.community.nospam.com> wrote in message
news:O70I913rHHA.4548@.TK2MSFTNGP03.phx.gbl...
> what about replication, mirroring, or clustering? Or should I not worry
> about that until the time comes? According to the mgr none of the above
> are used Just backups of the db's on a nightly basis.
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:OtZbfu3rHHA.1476@.TK2MSFTNGP06.phx.gbl...
>
I'm coming from a developer role for the pass 12 years. I've created
everything from, components, web app, windows apps, mobile device
applications, etc. Now I'm getting ready to take over work on our databases,
Our DBA has left and I'm kind of stepping in to take on that role. So my
question is, coming from developer background, what do I need to know beyond
creating stored procedures and select statements to be a successful DBA? I
know from in the pass in working with DBA's that they do more then just
create stored procedures and database, what do I need to know such as,
replication, mirroring, security, performance, etc. where do I begin, is
there anything out there to help me get an understanding of a DBA role, or
should I just 'fly by the seam of my pants' on this one? (probably not a
good idea, but willing to try it)
thanks for any help and or suggestions you may have to offer, (good or bad
are accepted)
just to add I know how to create users in the database, SSIS packages, I
guess its more on triggers, indexes, performance issues, replication,
mirroring, etc, suff like that I guess is what i'm looking for.
and other things a DBA does within this role.
"Mike" <Mike@.community.nospam.com> wrote in message
news:%23G$Syd3rHHA.1168@.TK2MSFTNGP03.phx.gbl...
> This is probably a wide open question but:
> I'm coming from a developer role for the pass 12 years. I've created
> everything from, components, web app, windows apps, mobile device
> applications, etc. Now I'm getting ready to take over work on our
> databases, Our DBA has left and I'm kind of stepping in to take on that
> role. So my question is, coming from developer background, what do I need
> to know beyond creating stored procedures and select statements to be a
> successful DBA? I know from in the pass in working with DBA's that they do
> more then just create stored procedures and database, what do I need to
> know such as, replication, mirroring, security, performance, etc. where do
> I begin, is there anything out there to help me get an understanding of a
> DBA role, or should I just 'fly by the seam of my pants' on this one?
> (probably not a good idea, but willing to try it)
>
> thanks for any help and or suggestions you may have to offer, (good or
> bad are accepted)
>
>
|||http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/f696a9ae540bf1a0/01482ed3de91192f?hl=en#01482ed3de91192f
|||I was in the same boat. Microsoft has some really good training
classes and I'd highly recommend getting into one of those as soon as
you can. They'll cover a broad range of your day to day operations
like backups and security. Also, get familiar with some networking
knowledge as well. You're going to be walking the line between
hardware and software. But, the first thing is with only SQL Dev
experience I'd see what, if any, training classes you can get into. If
anything the book can be used for reference for TSQL statements.
|||First and most importantly, you need to be able to put the system back the
way it was yesterday, data and all. Fully document the system and make sure
your recovery strategy is in place and tested.
Second, all developers are now your natural enemy. They write code to
destroy the data and performance. It is your job to limit the damage they
can do. See requirement #1.
Performance monitoring and tuning must now be regular parts of your routine.
Security just became your problem. You don't want to be the next headline
on data lost or hacked.
There are lots more, but this should keep you up nights for the next few
months.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Mike" <Mike@.community.nospam.com> wrote in message
news:OinW2i3rHHA.632@.TK2MSFTNGP03.phx.gbl...
> just to add I know how to create users in the database, SSIS packages, I
> guess its more on triggers, indexes, performance issues, replication,
> mirroring, etc, suff like that I guess is what i'm looking for.
> and other things a DBA does within this role.
>
> "Mike" <Mike@.community.nospam.com> wrote in message
> news:%23G$Syd3rHHA.1168@.TK2MSFTNGP03.phx.gbl...
>
|||Thanks, I'm now second guessing myself for taken this task on and learn
something new.
> First and most importantly, you need to be able to put the system back the
> way it was yesterday, data and all. Fully document the system and make
> sure your recovery strategy is in place and tested.
I can say I was successful in restoring all the databases when the
server actually crashed. I learned that process hands on. The only thing
though I had to add all of the logins again manually. Is there a way to back
up the 'Security' folder so I can all Login ID and Privilages or no?
> Second, all developers are now your natural enemy. They write code to
> destroy the data and performance. It is your job to limit the damage they
> can do. See requirement #1.
Coming from development, I admit I either brought down a DB or locked a db
in my development days.
> Security just became your problem. You don't want to be the next headline
> on data lost or hacked.
this part I like because I handled alot of the security for the
applications I developed. Though this is a different level of security.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OtZbfu3rHHA.1476@.TK2MSFTNGP06.phx.gbl...
> First and most importantly, you need to be able to put the system back the
> way it was yesterday, data and all. Fully document the system and make
> sure your recovery strategy is in place and tested.
> Second, all developers are now your natural enemy. They write code to
> destroy the data and performance. It is your job to limit the damage they
> can do. See requirement #1.
> Performance monitoring and tuning must now be regular parts of your
> routine.
> Security just became your problem. You don't want to be the next headline
> on data lost or hacked.
> There are lots more, but this should keep you up nights for the next few
> months.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
>
> "Mike" <Mike@.community.nospam.com> wrote in message
> news:OinW2i3rHHA.632@.TK2MSFTNGP03.phx.gbl...
>
|||what about replication, mirroring, or clustering? Or should I not worry
about that until the time comes? According to the mgr none of the above are
used Just backups of the db's on a nightly basis.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OtZbfu3rHHA.1476@.TK2MSFTNGP06.phx.gbl...
> First and most importantly, you need to be able to put the system back the
> way it was yesterday, data and all. Fully document the system and make
> sure your recovery strategy is in place and tested.
> Second, all developers are now your natural enemy. They write code to
> destroy the data and performance. It is your job to limit the damage they
> can do. See requirement #1.
> Performance monitoring and tuning must now be regular parts of your
> routine.
> Security just became your problem. You don't want to be the next headline
> on data lost or hacked.
> There are lots more, but this should keep you up nights for the next few
> months.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
>
> "Mike" <Mike@.community.nospam.com> wrote in message
> news:OinW2i3rHHA.632@.TK2MSFTNGP03.phx.gbl...
>
|||Hey, not ALL developers are bad. Just the great majority. Coming out
of the trenches yourself you probably get along well with them so when
you see a performance problem they will most likely be very receptive
to your suggestions on how to fix it. SQL Profiler and Performance
Monitor have just become some of your best friends. Though they do add
overhead to the server so use them sparingly.
This site may help you figure out some more details about performance
monitors:
http://www.sql-server-performance.com/
Here's a link on what your day-to-day's may include:
http://www.databasedesign-resource.com/sql-server-dba.html
On Jun 15, 2:33 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:[vbcol=seagreen]
> First and most importantly, you need to be able to put the system back the
> way it was yesterday, data and all. Fully document the system and make sure
> your recovery strategy is in place and tested.
> Second, all developers are now your natural enemy. They write code to
> destroy the data and performance. It is your job to limit the damage they
> can do. See requirement #1.
> Performance monitoring and tuning must now be regular parts of your routine.
> Security just became your problem. You don't want to be the next headline
> on data lost or hacked.
> There are lots more, but this should keep you up nights for the next few
> months.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Mike" <M...@.community.nospam.com> wrote in message
> news:OinW2i3rHHA.632@.TK2MSFTNGP03.phx.gbl...
>
>
|||thanks I greatly appreciate the information.
This Database stuff is a whole new world to me (from a true dba perspective)
and I was willing to give it a shot.
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:1181933280.917354.9000@.p77g2000hsh.googlegrou ps.com...
> Hey, not ALL developers are bad. Just the great majority. Coming out
> of the trenches yourself you probably get along well with them so when
> you see a performance problem they will most likely be very receptive
> to your suggestions on how to fix it. SQL Profiler and Performance
> Monitor have just become some of your best friends. Though they do add
> overhead to the server so use them sparingly.
> This site may help you figure out some more details about performance
> monitors:
> http://www.sql-server-performance.com/
> Here's a link on what your day-to-day's may include:
> http://www.databasedesign-resource.com/sql-server-dba.html
> On Jun 15, 2:33 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>
>
|||Unless you are currently using or expect to use a technology, I would not
worry about it on day one. Getting a handle on managing, recovering, and
operating the server AS IS should be your top priority. Learn about what
new technologies can and cannot do so you can make intelligent
recommendations, but unless they are part of your current environment, they
are not part of your current problem set.
As for the developer comments, I have worked as a developer, a system admin,
and as a DBA over the course of my career. Each has a different perspective
on the system and different goals. Sometimes those goals are in wildly
different directions.
Don't rely on luck to restore a system. Have a plan and practice it. Test
your backups regularly. Training classes will help give you the knowledge
to build an effective operations and recovery plan, but you have to do the
heavy lifting. Remember, management does not care about backups, they care
about restores.
Good luck. I figure we will see you around here quite a bit in the months
to come.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Mike" <Mike@.community.nospam.com> wrote in message
news:O70I913rHHA.4548@.TK2MSFTNGP03.phx.gbl...
> what about replication, mirroring, or clustering? Or should I not worry
> about that until the time comes? According to the mgr none of the above
> are used Just backups of the db's on a nightly basis.
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:OtZbfu3rHHA.1476@.TK2MSFTNGP06.phx.gbl...
>
Developer Edition Product Key
Ive got a server which I want to install the SQL Server Developer Edition on but it previously had the full version on which has been uninstalled but every time I try to install the dev edition it it its prompting me for a Product key which I know for the developer edition isnt needed. Is there a registry key from the previous install which is triggering this ?
Developer edition does require a product key. The only editions that don't are Eval and Express. Enter the key supplied when you purchased the Dev edition and you're good to go.
Thanks,
Sam Lester (MSFT)
Developer Edition Product Key
Ive got a server which I want to install the SQL Server Developer Edition on but it previously had the full version on which has been uninstalled but every time I try to install the dev edition it it its prompting me for a Product key which I know for the developer edition isnt needed. Is there a registry key from the previous install which is triggering this ?
Developer edition does require a product key. The only editions that don't are Eval and Express. Enter the key supplied when you purchased the Dev edition and you're good to go.
Thanks,
Sam Lester (MSFT)
Developer Edition Install
Hi, folks.
I've just reached the end of my frustration with trying to learn MSDE with no interface (in addition to ASP.NET), so I'm going to install 2K Developer Edition. However, I'd like to know if it's safe install it on top of MSDE. I'm currently running XP Pr
o, VS.NET, IIS 5.x, so I'm afraid that if I have uninstall MSDE that I'll break a bunch things that I've painstakingly configured. ;-)
Advice?
TIA,
Bob
What have you painstakingly configured? You might be able to easily =
configure these within SQL Server Developer Edition. =20
The other option: install the Developer Edition of SQL Server as a named =
instance. That will allow you to keep MSDE installed as well as give =
you the ability to use SQL Server (Developer Edition) on your computer.
--=20
Keith
"WebGuyBob" <anonymous@.discussions.microsoft.com> wrote in message =
news:1BD19D74-8A5A-4F9C-8FD6-D013309B7A45@.microsoft.com...
> Hi, folks.
>=20
> I've just reached the end of my frustration with trying to learn MSDE =
with no interface (in addition to ASP.NET), so I'm going to install 2K =
Developer Edition. However, I'd like to know if it's safe install it on =
top of MSDE. I'm currently running XP Pro, VS.NET, IIS 5.x, so I'm =
afraid that if I have uninstall MSDE that I'll break a bunch things that =
I've painstakingly configured. ;-)
>=20
> Advice?
>=20
> TIA,
>=20
> Bob
|||Keith,
The configuration statement actually refers to the installation and configuration of IIS and VS.NET. I feared that when I installed MSDE, it dug its hooks into those applications (inherent in many MS apps) and that uninstalling MSDE would "break" things.
I would prefer to have only one DB application on my machine. With that said, since it appears from your comments, because SQL2k DevEd does not install "on top" of MSDE, I will attempt to uninstall it and then install SQL2k DevEd.
I have developed any databases yet, but either VS.NET, MSDE, or the .NET SDK installed various sample databases. I was hoping to use those to learn from. If those happen to get deleted resulting from the uninstall, at least I've found a place where I ca
n get updated ones.
Any parting words of wisdom before I do the uninstall of MSDE and install of SQL2k DevEd?
Thanks,
Bob
|||The many "sample" databases should still exist -- in mdf and ldf form. =
Hopefully you will be able to use sp_attach_db to attach those databases =
to SQL Server Developer Edition.
Words of wisdom: Just do it.
Good luck. Post back if you have any questions along the way. There =
are many helpful people in the newsgroups that should be able to provide =
assistance should you need it.
--=20
Keith
"WebGuyBob" <anonymous@.discussions.microsoft.com> wrote in message =
news:4A5D898D-9A17-4C87-8554-AF6EFBF0F4DB@.microsoft.com...
> Keith,
>=20
> The configuration statement actually refers to the installation and =
configuration of IIS and VS.NET. I feared that when I installed MSDE, =
it dug its hooks into those applications (inherent in many MS apps) and =
that uninstalling MSDE would "break" things.
>=20
> I would prefer to have only one DB application on my machine. With =
that said, since it appears from your comments, because SQL2k DevEd does =
not install "on top" of MSDE, I will attempt to uninstall it and then =
install SQL2k DevEd.
>=20
> I have developed any databases yet, but either VS.NET, MSDE, or the =
..NET SDK installed various sample databases. I was hoping to use those =
to learn from. If those happen to get deleted resulting from the =
uninstall, at least I've found a place where I can get updated ones.
>=20
> Any parting words of wisdom before I do the uninstall of MSDE and =
install of SQL2k DevEd?
>=20
> Thanks,
>=20
> Bob
|||Or you could run the pubsinst.sql scripts from another sql server standard
install.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
I've just reached the end of my frustration with trying to learn MSDE with no interface (in addition to ASP.NET), so I'm going to install 2K Developer Edition. However, I'd like to know if it's safe install it on top of MSDE. I'm currently running XP Pr
o, VS.NET, IIS 5.x, so I'm afraid that if I have uninstall MSDE that I'll break a bunch things that I've painstakingly configured. ;-)
Advice?
TIA,
Bob
What have you painstakingly configured? You might be able to easily =
configure these within SQL Server Developer Edition. =20
The other option: install the Developer Edition of SQL Server as a named =
instance. That will allow you to keep MSDE installed as well as give =
you the ability to use SQL Server (Developer Edition) on your computer.
--=20
Keith
"WebGuyBob" <anonymous@.discussions.microsoft.com> wrote in message =
news:1BD19D74-8A5A-4F9C-8FD6-D013309B7A45@.microsoft.com...
> Hi, folks.
>=20
> I've just reached the end of my frustration with trying to learn MSDE =
with no interface (in addition to ASP.NET), so I'm going to install 2K =
Developer Edition. However, I'd like to know if it's safe install it on =
top of MSDE. I'm currently running XP Pro, VS.NET, IIS 5.x, so I'm =
afraid that if I have uninstall MSDE that I'll break a bunch things that =
I've painstakingly configured. ;-)
>=20
> Advice?
>=20
> TIA,
>=20
> Bob
|||Keith,
The configuration statement actually refers to the installation and configuration of IIS and VS.NET. I feared that when I installed MSDE, it dug its hooks into those applications (inherent in many MS apps) and that uninstalling MSDE would "break" things.
I would prefer to have only one DB application on my machine. With that said, since it appears from your comments, because SQL2k DevEd does not install "on top" of MSDE, I will attempt to uninstall it and then install SQL2k DevEd.
I have developed any databases yet, but either VS.NET, MSDE, or the .NET SDK installed various sample databases. I was hoping to use those to learn from. If those happen to get deleted resulting from the uninstall, at least I've found a place where I ca
n get updated ones.
Any parting words of wisdom before I do the uninstall of MSDE and install of SQL2k DevEd?
Thanks,
Bob
|||The many "sample" databases should still exist -- in mdf and ldf form. =
Hopefully you will be able to use sp_attach_db to attach those databases =
to SQL Server Developer Edition.
Words of wisdom: Just do it.
Good luck. Post back if you have any questions along the way. There =
are many helpful people in the newsgroups that should be able to provide =
assistance should you need it.
--=20
Keith
"WebGuyBob" <anonymous@.discussions.microsoft.com> wrote in message =
news:4A5D898D-9A17-4C87-8554-AF6EFBF0F4DB@.microsoft.com...
> Keith,
>=20
> The configuration statement actually refers to the installation and =
configuration of IIS and VS.NET. I feared that when I installed MSDE, =
it dug its hooks into those applications (inherent in many MS apps) and =
that uninstalling MSDE would "break" things.
>=20
> I would prefer to have only one DB application on my machine. With =
that said, since it appears from your comments, because SQL2k DevEd does =
not install "on top" of MSDE, I will attempt to uninstall it and then =
install SQL2k DevEd.
>=20
> I have developed any databases yet, but either VS.NET, MSDE, or the =
..NET SDK installed various sample databases. I was hoping to use those =
to learn from. If those happen to get deleted resulting from the =
uninstall, at least I've found a place where I can get updated ones.
>=20
> Any parting words of wisdom before I do the uninstall of MSDE and =
install of SQL2k DevEd?
>=20
> Thanks,
>=20
> Bob
|||Or you could run the pubsinst.sql scripts from another sql server standard
install.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Friday, February 17, 2012
developed in 2005, need to go back to 2000
I've developed a web application and to be honest I've been a bit dumb and not checked with my hosts. I assumed they used SQL Server 2005. But no, they only use 2000.
I'm sure I'll come across more problems but the first I'm having is I've used the ROW_NUMBER() function in a few stored procedures, but in 2000 I'm getting this error message
'ROW_NUMBER' is not a recognized function name.
Here is the stored procedure for reference
anyone know the SQL Server 2000 equivalent?SELECT RowNumFROM (SELECT id, category, ROW_NUMBER() OVER(ORDER BY id) as RowNum FROM custrecords ) as CustRecord
Regrettably ROW_NUMBER is a new feature with SQL2005.
What precisely are you trying to achive with your SELECT statement?
|||there isn't a equivalent of row_number() in SQL Server 2000. Depending on your requirement, you can use a subquery something like
select count(*) from tbl where <condition>
to do it.
Maybe you can explain what are you trying to do here. Or alternatively do the row numbering in your ASP.NET
thanks khtan, I'll try using the Count keyword method instead|||
khtan:
there isn't a equivalent of row_number() in SQL Server 2000. Depending on your requirement, you can use a subquery something like
select count(*) from tbl where <condition>?
to do it.
Maybe you can explain what are you trying to do here. Or alternatively do the row numbering in your ASP.NET
?
Provided you read the dataset first, you can also have output parameters on the stored procedure that gets your batch size. Have a look at http://www.4guysfromrolla.com/webtech/041206-1.shtml (Efficiently Paging Through Large Result Sets in SQL Server 2000)
|||
Provided you read the dataset first, you can also have output parameters on the stored procedure that gets your batch size. Have a look at http://www.4guysfromrolla.com/webtech/041206-1.shtml ( Efficiently Paging Through Large Result Sets in SQL Server 2000)
wow, thanks tatworth. I started on the tutorial link you supplied above and eventually found myself doing the tutorial (A more efficent method for paging through large result sets : link below)
http://www.4guysfromrolla.com/webtech/042606-1.shtml
But I've hit a problem. Like I say I've done the tut above and it works great...if you only want the results to be in order of id.
I'm trying to change the order by other fields (name, date of birth) but I'm not getting anywhere.
Any one any tips?
|||
Please always remember when using output parameters and a dataset, to read the dataset before the output parameters - there is a "bug"/"feature" that only populates the output parameters after the dataset has been read.
As to your sort problem, the solution pobably lies along using "date of birth and id" instead of just "date of birth" - please post an example of the revised TSQL together with a script to create the table. I will look at it and attempt a fix the problem; however it may not be until next weekend that I can look at it.
|||
Please always remember when using output parameters and a dataset, to read the dataset before the output parameters - there is a "bug"/"feature" that only populates the output parameters after the dataset has been read.
As to your sort problem, the solution pobably lies along using "date of birth and id" instead of just "date of birth" - please post an example of the revised TSQL together with a script to create the table. I will look at it and attempt a fix the problem; however it may not be until next weekend that I can look at it.
wow, thanks tatworth
This is the stored procedure I created from the tutorial above, like I say it works if the order by is id, anything else and it doesn't work
ALTER PROCEDURE uk_members
(
@.username VarChar(20),
@.startRowIndex int,
@.maximumRows int
)
AS
DECLARE @.first_id int
SET ROWCOUNT @.startRowIndexSELECT @.first_id = id FROM uk_orders
WHERE (username=@.username) AND (itemstat <> 'DELETE') ORDER BY idSET ROWCOUNT @.maximumRows
SELECT id, username, suppliername, product, quantity
FROM uk_orders WHERE
(id >= @.first_id) AND (username=@.username) AND (suppliername <> 'SMITHS') ORDER BY idSET ROWCOUNT 0
I've been messing around with the code but not got anywhere.
I really appreciate your help and of course anyone else offering any help is also really appreciated.
Subscribe to:
Posts (Atom)