Can anyone point me to a good resource to create managed stored procedures in SQL 2005 Express using VB.NET and VS2003?You could create managed stored procedures only using .NET 2.0.
VS2003 use .NET 1.1.
Good article about VB.NET and SQL/CLR: http://www.devx.com/dotnet/Article/21286
Showing posts with label point. Show all posts
Showing posts with label point. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Malicious User
Not sure if its the right place..but need help cracking this...Just
inherited a bad place......
Users access a certain share point site and browse a directory for a host of
folders.This afternoon one of the folders was deleted which has loads of
subfolders(as it is a sharepoint server).....now I need to find out who thi
s
kool dude is!...
What I have now: System state backup of the Machine,SQL full backup and the
backup(SQL and System) just after the files have
been deleted.
All I have is just Auditing for success and failure but nothing with object
access,didnt think if it would matter even if
object acess was enabled...
now...with the given situation...how do I get to this dude?Can someone
enrich my novice knowledge please?SQL server security...what do i look for
and where?
thanks
maverickHi maverick,
Not sure how Sharepoint works with SQL Server in this case but to find
out what people are using on the SQL Server level you should be using
SQL Profiler to capture a trace of all activity carried out against the
SQL Server. Hope this helps.
Jonathan
maverick wrote:
> Not sure if its the right place..but need help cracking this...Just
> inherited a bad place......
> Users access a certain share point site and browse a directory for a host
of
> folders.This afternoon one of the folders was deleted which has loads of
> subfolders(as it is a sharepoint server).....now I need to find out who t
his
> kool dude is!...
>
> What I have now: System state backup of the Machine,SQL full backup and th
e
> backup(SQL and System) just after the files have
> been deleted.
> All I have is just Auditing for success and failure but nothing with objec
t
> access,didnt think if it would matter even if
> object acess was enabled...
> now...with the given situation...how do I get to this dude?Can someone
> enrich my novice knowledge please?SQL server security...what do i look for
> and where?
>
> thanks
> maverick
inherited a bad place......
Users access a certain share point site and browse a directory for a host of
folders.This afternoon one of the folders was deleted which has loads of
subfolders(as it is a sharepoint server).....now I need to find out who thi
s
kool dude is!...
What I have now: System state backup of the Machine,SQL full backup and the
backup(SQL and System) just after the files have
been deleted.
All I have is just Auditing for success and failure but nothing with object
access,didnt think if it would matter even if
object acess was enabled...
now...with the given situation...how do I get to this dude?Can someone
enrich my novice knowledge please?SQL server security...what do i look for
and where?
thanks
maverickHi maverick,
Not sure how Sharepoint works with SQL Server in this case but to find
out what people are using on the SQL Server level you should be using
SQL Profiler to capture a trace of all activity carried out against the
SQL Server. Hope this helps.
Jonathan
maverick wrote:
> Not sure if its the right place..but need help cracking this...Just
> inherited a bad place......
> Users access a certain share point site and browse a directory for a host
of
> folders.This afternoon one of the folders was deleted which has loads of
> subfolders(as it is a sharepoint server).....now I need to find out who t
his
> kool dude is!...
>
> What I have now: System state backup of the Machine,SQL full backup and th
e
> backup(SQL and System) just after the files have
> been deleted.
> All I have is just Auditing for success and failure but nothing with objec
t
> access,didnt think if it would matter even if
> object acess was enabled...
> now...with the given situation...how do I get to this dude?Can someone
> enrich my novice knowledge please?SQL server security...what do i look for
> and where?
>
> thanks
> maverick
Wednesday, March 21, 2012
Make the Login box go away!
I'm trying to incorporate the /ReportServer virtual into our application web
site so we can point the Report Viewer control to /ReportServer rather than
<default web site's IP>/ReportServer. I'm trying to remove authentication
from <app>/ReportServer, but I've been unable to keep it from popping up
that blasted login dialog box. I've briefly tried using the FormsAuth
sample and even rewrote it to always return True from all the authentication
functions, but neither solved my problem.
The application is under Forms Auth. I just want the ReportServer virtual
to run in that context. At this point, I don't even care if it's really
keeping authentication or just letting anyone who happens to type in
http://<our_app>/ReportServer go uncontested. It's extremely frustrating,
and it doesn't work for any users as it is now.
This is SQL Server Standard, so I don't know if custom security extensions
(assuming I really had any clue as to how they worked or how to set them up)
would even work (I came across a page that says only Enterprise supports
them).To my knowledge, for IE to give your windows authentication credentials
automatically to IIS, you have to adresse the website using a machine name.
Use something like http://computername/ReportServer
Suppose your server is named BigBox.leetdomain.com
Address it using http://BigBox/ReportServer
I never had to mess around with the report server security as we want
windows authentication and the report server will not be used externally. It
seems to be configured fine by default.
"DJM" wrote:
> I'm trying to incorporate the /ReportServer virtual into our application web
> site so we can point the Report Viewer control to /ReportServer rather than
> <default web site's IP>/ReportServer. I'm trying to remove authentication
> from <app>/ReportServer, but I've been unable to keep it from popping up
> that blasted login dialog box. I've briefly tried using the FormsAuth
> sample and even rewrote it to always return True from all the authentication
> functions, but neither solved my problem.
> The application is under Forms Auth. I just want the ReportServer virtual
> to run in that context. At this point, I don't even care if it's really
> keeping authentication or just letting anyone who happens to type in
> http://<our_app>/ReportServer go uncontested. It's extremely frustrating,
> and it doesn't work for any users as it is now.
> This is SQL Server Standard, so I don't know if custom security extensions
> (assuming I really had any clue as to how they worked or how to set them up)
> would even work (I came across a page that says only Enterprise supports
> them).
>
>|||The Forms Authentication sample from MSDN gives you instructions on how to
restore to default non-forms-authentication settings.
Basically, it's not recommended since you'll have to re-do your report
permissions. And if you don't have the original .config files backed up,
you'll have a VERY difficult time.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"DJM" <msnews@.puddlestheshark.com> wrote in message
news:e6Cz03NzEHA.2600@.TK2MSFTNGP09.phx.gbl...
> I'm trying to incorporate the /ReportServer virtual into our application
> web
> site so we can point the Report Viewer control to /ReportServer rather
> than
> <default web site's IP>/ReportServer. I'm trying to remove authentication
> from <app>/ReportServer, but I've been unable to keep it from popping up
> that blasted login dialog box. I've briefly tried using the FormsAuth
> sample and even rewrote it to always return True from all the
> authentication
> functions, but neither solved my problem.
> The application is under Forms Auth. I just want the ReportServer virtual
> to run in that context. At this point, I don't even care if it's really
> keeping authentication or just letting anyone who happens to type in
> http://<our_app>/ReportServer go uncontested. It's extremely frustrating,
> and it doesn't work for any users as it is now.
> This is SQL Server Standard, so I don't know if custom security extensions
> (assuming I really had any clue as to how they worked or how to set them
> up)
> would even work (I came across a page that says only Enterprise supports
> them).
>
>|||But the standard is Windows Authentication. I still get the
username/password dialog when trying to access a report via report viewer
control from my website.
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:O$FUn9OzEHA.3708@.TK2MSFTNGP14.phx.gbl...
> The Forms Authentication sample from MSDN gives you instructions on how to
> restore to default non-forms-authentication settings.
> Basically, it's not recommended since you'll have to re-do your report
> permissions. And if you don't have the original .config files backed up,
> you'll have a VERY difficult time.
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "DJM" <msnews@.puddlestheshark.com> wrote in message
> news:e6Cz03NzEHA.2600@.TK2MSFTNGP09.phx.gbl...
>> I'm trying to incorporate the /ReportServer virtual into our application
>> web
>> site so we can point the Report Viewer control to /ReportServer rather
>> than
>> <default web site's IP>/ReportServer. I'm trying to remove
>> authentication
>> from <app>/ReportServer, but I've been unable to keep it from popping up
>> that blasted login dialog box. I've briefly tried using the FormsAuth
>> sample and even rewrote it to always return True from all the
>> authentication
>> functions, but neither solved my problem.
>> The application is under Forms Auth. I just want the ReportServer
>> virtual
>> to run in that context. At this point, I don't even care if it's really
>> keeping authentication or just letting anyone who happens to type in
>> http://<our_app>/ReportServer go uncontested. It's extremely
>> frustrating,
>> and it doesn't work for any users as it is now.
>> This is SQL Server Standard, so I don't know if custom security
>> extensions
>> (assuming I really had any clue as to how they worked or how to set them
>> up)
>> would even work (I came across a page that says only Enterprise supports
>> them).
>>
>|||And since the report server *is* being accessed externally, it's quite
definitely not fine for our needs.
"/dev/null" <devnull@.discussions.microsoft.com> wrote in message
news:2BD4677A-BEF2-4048-92B9-316F38813E85@.microsoft.com...
> To my knowledge, for IE to give your windows authentication credentials
> automatically to IIS, you have to adresse the website using a machine
> name.
> Use something like http://computername/ReportServer
> Suppose your server is named BigBox.leetdomain.com
> Address it using http://BigBox/ReportServer
> I never had to mess around with the report server security as we want
> windows authentication and the report server will not be used externally.
> It
> seems to be configured fine by default.
>
> "DJM" wrote:
>> I'm trying to incorporate the /ReportServer virtual into our application
>> web
>> site so we can point the Report Viewer control to /ReportServer rather
>> than
>> <default web site's IP>/ReportServer. I'm trying to remove
>> authentication
>> from <app>/ReportServer, but I've been unable to keep it from popping up
>> that blasted login dialog box. I've briefly tried using the FormsAuth
>> sample and even rewrote it to always return True from all the
>> authentication
>> functions, but neither solved my problem.
>> The application is under Forms Auth. I just want the ReportServer
>> virtual
>> to run in that context. At this point, I don't even care if it's really
>> keeping authentication or just letting anyone who happens to type in
>> http://<our_app>/ReportServer go uncontested. It's extremely
>> frustrating,
>> and it doesn't work for any users as it is now.
>> This is SQL Server Standard, so I don't know if custom security
>> extensions
>> (assuming I really had any clue as to how they worked or how to set them
>> up)
>> would even work (I came across a page that says only Enterprise supports
>> them).
>>
>>|||Ok, I've set Authentication mode="None", I've set the /ReportServer virtual
to allow Anonymous access as IUSR, I've granted IUSR_GROUP (local group
containing the IUSR, IWAM, and ASP.NET users) access to the \MSSQL\Reporting
Services\ReportServer folder (and files and subfolders), and I've granted
the IUSR_GROUP the Browser role to all reports.
So why, when using the Report Viewer web control, do I see a login box?!?|||Does anyone have any advice or suggestions here? I still haven't resolved
this.
"DJM" <msnews@.puddlestheshark.com> wrote in message
news:uLeyyLnzEHA.352@.TK2MSFTNGP14.phx.gbl...
> Ok, I've set Authentication mode="None", I've set the /ReportServer
> virtual to allow Anonymous access as IUSR, I've granted IUSR_GROUP (local
> group containing the IUSR, IWAM, and ASP.NET users) access to the
> \MSSQL\Reporting Services\ReportServer folder (and files and subfolders),
> and I've granted the IUSR_GROUP the Browser role to all reports.
> So why, when using the Report Viewer web control, do I see a login box?!?
>|||You have two options:
1. Generating the report on the server side of the application by using the
Render SOAP API. The advantage of this approach is that it is more secure
since the user doesn't see the report URL (everything takes place on the
server). The tradeoff is that the interactive features (drilldown,
drillthrough, etc.) will not work with SOAP since their require direct
access to the Report Server by URL. If you decide to take this approach, you
can pass the web app identity to the Report Server and grant a minimum set
of permissions in RS to this account.
2. Replace the RS Windows security with Forms Authentication by writing a
custom security extension. This will allow you to incorporate interactive
features in your reports. In this scenario, the reports will be requested on
the client side of the application (e.g. by using the Report Viewer sample
control). If you decide to take this approach check out the sample security
extension from MS at
(http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.a
sp?frame=true#ufairs_topic3).
So, you have to carefully weight out your requirements for security,
reporting features and your application architecture to determine the best
integration scenario.
--
Hope this helps.
Rags Iyer
"DJM" wrote:
> I'm trying to incorporate the /ReportServer virtual into our application web
> site so we can point the Report Viewer control to /ReportServer rather than
> <default web site's IP>/ReportServer. I'm trying to remove authentication
> from <app>/ReportServer, but I've been unable to keep it from popping up
> that blasted login dialog box. I've briefly tried using the FormsAuth
> sample and even rewrote it to always return True from all the authentication
> functions, but neither solved my problem.
> The application is under Forms Auth. I just want the ReportServer virtual
> to run in that context. At this point, I don't even care if it's really
> keeping authentication or just letting anyone who happens to type in
> http://<our_app>/ReportServer go uncontested. It's extremely frustrating,
> and it doesn't work for any users as it is now.
> This is SQL Server Standard, so I don't know if custom security extensions
> (assuming I really had any clue as to how they worked or how to set them up)
> would even work (I came across a page that says only Enterprise supports
> them).
>
>|||The second option would be ideal, but if you'll notice, I tried that and was
unable to get it to make a shred of difference. Maybe I'm just not smart
enough to figure it out, but it's not working for me.
"Rags Iyer" <RagsIyer@.discussions.microsoft.com> wrote in message
news:A2AD6EB1-4CE8-4305-8063-8EE6AC9E1828@.microsoft.com...
> You have two options:
> 1. Generating the report on the server side of the application by using
> the
> Render SOAP API. The advantage of this approach is that it is more secure
> since the user doesn't see the report URL (everything takes place on the
> server). The tradeoff is that the interactive features (drilldown,
> drillthrough, etc.) will not work with SOAP since their require direct
> access to the Report Server by URL. If you decide to take this approach,
> you
> can pass the web app identity to the Report Server and grant a minimum set
> of permissions in RS to this account.
> 2. Replace the RS Windows security with Forms Authentication by writing a
> custom security extension. This will allow you to incorporate interactive
> features in your reports. In this scenario, the reports will be requested
> on
> the client side of the application (e.g. by using the Report Viewer sample
> control). If you decide to take this approach check out the sample
> security
> extension from MS at
> (http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.a
> sp?frame=true#ufairs_topic3).
> So, you have to carefully weight out your requirements for security,
> reporting features and your application architecture to determine the best
> integration scenario.
> --
> Hope this helps.
> Rags Iyer
> "DJM" wrote:
>> I'm trying to incorporate the /ReportServer virtual into our application
>> web
>> site so we can point the Report Viewer control to /ReportServer rather
>> than
>> <default web site's IP>/ReportServer. I'm trying to remove
>> authentication
>> from <app>/ReportServer, but I've been unable to keep it from popping up
>> that blasted login dialog box. I've briefly tried using the FormsAuth
>> sample and even rewrote it to always return True from all the
>> authentication
>> functions, but neither solved my problem.
>> The application is under Forms Auth. I just want the ReportServer
>> virtual
>> to run in that context. At this point, I don't even care if it's really
>> keeping authentication or just letting anyone who happens to type in
>> http://<our_app>/ReportServer go uncontested. It's extremely
>> frustrating,
>> and it doesn't work for any users as it is now.
>> This is SQL Server Standard, so I don't know if custom security
>> extensions
>> (assuming I really had any clue as to how they worked or how to set them
>> up)
>> would even work (I came across a page that says only Enterprise supports
>> them).
>>
>>|||Is the Login Box still popping up when Forms Authetication is Implemented.?
"DJM" wrote:
> The second option would be ideal, but if you'll notice, I tried that and was
> unable to get it to make a shred of difference. Maybe I'm just not smart
> enough to figure it out, but it's not working for me.
> "Rags Iyer" <RagsIyer@.discussions.microsoft.com> wrote in message
> news:A2AD6EB1-4CE8-4305-8063-8EE6AC9E1828@.microsoft.com...
> > You have two options:
> >
> > 1. Generating the report on the server side of the application by using
> > the
> > Render SOAP API. The advantage of this approach is that it is more secure
> > since the user doesn't see the report URL (everything takes place on the
> > server). The tradeoff is that the interactive features (drilldown,
> > drillthrough, etc.) will not work with SOAP since their require direct
> > access to the Report Server by URL. If you decide to take this approach,
> > you
> > can pass the web app identity to the Report Server and grant a minimum set
> > of permissions in RS to this account.
> >
> > 2. Replace the RS Windows security with Forms Authentication by writing a
> > custom security extension. This will allow you to incorporate interactive
> > features in your reports. In this scenario, the reports will be requested
> > on
> > the client side of the application (e.g. by using the Report Viewer sample
> > control). If you decide to take this approach check out the sample
> > security
> > extension from MS at
> > (http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.a
> > sp?frame=true#ufairs_topic3).
> >
> > So, you have to carefully weight out your requirements for security,
> > reporting features and your application architecture to determine the best
> > integration scenario.
> > --
> > Hope this helps.
> >
> > Rags Iyer
> >
> > "DJM" wrote:
> >
> >> I'm trying to incorporate the /ReportServer virtual into our application
> >> web
> >> site so we can point the Report Viewer control to /ReportServer rather
> >> than
> >> <default web site's IP>/ReportServer. I'm trying to remove
> >> authentication
> >> from <app>/ReportServer, but I've been unable to keep it from popping up
> >> that blasted login dialog box. I've briefly tried using the FormsAuth
> >> sample and even rewrote it to always return True from all the
> >> authentication
> >> functions, but neither solved my problem.
> >>
> >> The application is under Forms Auth. I just want the ReportServer
> >> virtual
> >> to run in that context. At this point, I don't even care if it's really
> >> keeping authentication or just letting anyone who happens to type in
> >> http://<our_app>/ReportServer go uncontested. It's extremely
> >> frustrating,
> >> and it doesn't work for any users as it is now.
> >>
> >> This is SQL Server Standard, so I don't know if custom security
> >> extensions
> >> (assuming I really had any clue as to how they worked or how to set them
> >> up)
> >> would even work (I came across a page that says only Enterprise supports
> >> them).
> >>
> >>
> >>
> >>
>
>|||Yes.
"Rags Iyer" <RagsIyer@.discussions.microsoft.com> wrote in message
news:C574F74E-C580-47A4-86AD-BCD62253C700@.microsoft.com...
> Is the Login Box still popping up when Forms Authetication is
> Implemented.?|||Please check wherther the Security implemenation for Forms Authetication was
properly implemented.The authetication in the Rs Config File needs to be
changed to Forms Authentication and all the required policy is set.
Regards,
Rags Iyer
"DJM" wrote:
> Yes.
> "Rags Iyer" <RagsIyer@.discussions.microsoft.com> wrote in message
> news:C574F74E-C580-47A4-86AD-BCD62253C700@.microsoft.com...
> >
> > Is the Login Box still popping up when Forms Authetication is
> > Implemented.?
>
>|||Please check wherther the Security implemenation for Forms Authetication was
properly implemented.The authetication in the Rs Config File needs to be
changed to Forms Authentication and all the required policy is set.
Regards,
Rags Iyer
"DJM" wrote:
> Yes.
> "Rags Iyer" <RagsIyer@.discussions.microsoft.com> wrote in message
> news:C574F74E-C580-47A4-86AD-BCD62253C700@.microsoft.com...
> >
> > Is the Login Box still popping up when Forms Authetication is
> > Implemented.?
>
>|||I did that according to the documentation (adding Forms to the
authentication types or wherever that is, and changing authentication mode
to Forms in the web.config), and I was still getting the login box.
What gets me is that box is still appearing even when I set the
authentication mode to None.
"Rags Iyer" <RagsIyer@.discussions.microsoft.com> wrote in message
news:E5169863-A011-43A4-BAB3-A0627C0A0272@.microsoft.com...
> Please check wherther the Security implemenation for Forms Authetication
> was
> properly implemented.The authetication in the Rs Config File needs to be
> changed to Forms Authentication and all the required policy is set.
> Regards,
> Rags Iyer
> "DJM" wrote:
>> Yes.
>> "Rags Iyer" <RagsIyer@.discussions.microsoft.com> wrote in message
>> news:C574F74E-C580-47A4-86AD-BCD62253C700@.microsoft.com...
>> >
>> > Is the Login Box still popping up when Forms Authetication is
>> > Implemented.?
>>
site so we can point the Report Viewer control to /ReportServer rather than
<default web site's IP>/ReportServer. I'm trying to remove authentication
from <app>/ReportServer, but I've been unable to keep it from popping up
that blasted login dialog box. I've briefly tried using the FormsAuth
sample and even rewrote it to always return True from all the authentication
functions, but neither solved my problem.
The application is under Forms Auth. I just want the ReportServer virtual
to run in that context. At this point, I don't even care if it's really
keeping authentication or just letting anyone who happens to type in
http://<our_app>/ReportServer go uncontested. It's extremely frustrating,
and it doesn't work for any users as it is now.
This is SQL Server Standard, so I don't know if custom security extensions
(assuming I really had any clue as to how they worked or how to set them up)
would even work (I came across a page that says only Enterprise supports
them).To my knowledge, for IE to give your windows authentication credentials
automatically to IIS, you have to adresse the website using a machine name.
Use something like http://computername/ReportServer
Suppose your server is named BigBox.leetdomain.com
Address it using http://BigBox/ReportServer
I never had to mess around with the report server security as we want
windows authentication and the report server will not be used externally. It
seems to be configured fine by default.
"DJM" wrote:
> I'm trying to incorporate the /ReportServer virtual into our application web
> site so we can point the Report Viewer control to /ReportServer rather than
> <default web site's IP>/ReportServer. I'm trying to remove authentication
> from <app>/ReportServer, but I've been unable to keep it from popping up
> that blasted login dialog box. I've briefly tried using the FormsAuth
> sample and even rewrote it to always return True from all the authentication
> functions, but neither solved my problem.
> The application is under Forms Auth. I just want the ReportServer virtual
> to run in that context. At this point, I don't even care if it's really
> keeping authentication or just letting anyone who happens to type in
> http://<our_app>/ReportServer go uncontested. It's extremely frustrating,
> and it doesn't work for any users as it is now.
> This is SQL Server Standard, so I don't know if custom security extensions
> (assuming I really had any clue as to how they worked or how to set them up)
> would even work (I came across a page that says only Enterprise supports
> them).
>
>|||The Forms Authentication sample from MSDN gives you instructions on how to
restore to default non-forms-authentication settings.
Basically, it's not recommended since you'll have to re-do your report
permissions. And if you don't have the original .config files backed up,
you'll have a VERY difficult time.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"DJM" <msnews@.puddlestheshark.com> wrote in message
news:e6Cz03NzEHA.2600@.TK2MSFTNGP09.phx.gbl...
> I'm trying to incorporate the /ReportServer virtual into our application
> web
> site so we can point the Report Viewer control to /ReportServer rather
> than
> <default web site's IP>/ReportServer. I'm trying to remove authentication
> from <app>/ReportServer, but I've been unable to keep it from popping up
> that blasted login dialog box. I've briefly tried using the FormsAuth
> sample and even rewrote it to always return True from all the
> authentication
> functions, but neither solved my problem.
> The application is under Forms Auth. I just want the ReportServer virtual
> to run in that context. At this point, I don't even care if it's really
> keeping authentication or just letting anyone who happens to type in
> http://<our_app>/ReportServer go uncontested. It's extremely frustrating,
> and it doesn't work for any users as it is now.
> This is SQL Server Standard, so I don't know if custom security extensions
> (assuming I really had any clue as to how they worked or how to set them
> up)
> would even work (I came across a page that says only Enterprise supports
> them).
>
>|||But the standard is Windows Authentication. I still get the
username/password dialog when trying to access a report via report viewer
control from my website.
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:O$FUn9OzEHA.3708@.TK2MSFTNGP14.phx.gbl...
> The Forms Authentication sample from MSDN gives you instructions on how to
> restore to default non-forms-authentication settings.
> Basically, it's not recommended since you'll have to re-do your report
> permissions. And if you don't have the original .config files backed up,
> you'll have a VERY difficult time.
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "DJM" <msnews@.puddlestheshark.com> wrote in message
> news:e6Cz03NzEHA.2600@.TK2MSFTNGP09.phx.gbl...
>> I'm trying to incorporate the /ReportServer virtual into our application
>> web
>> site so we can point the Report Viewer control to /ReportServer rather
>> than
>> <default web site's IP>/ReportServer. I'm trying to remove
>> authentication
>> from <app>/ReportServer, but I've been unable to keep it from popping up
>> that blasted login dialog box. I've briefly tried using the FormsAuth
>> sample and even rewrote it to always return True from all the
>> authentication
>> functions, but neither solved my problem.
>> The application is under Forms Auth. I just want the ReportServer
>> virtual
>> to run in that context. At this point, I don't even care if it's really
>> keeping authentication or just letting anyone who happens to type in
>> http://<our_app>/ReportServer go uncontested. It's extremely
>> frustrating,
>> and it doesn't work for any users as it is now.
>> This is SQL Server Standard, so I don't know if custom security
>> extensions
>> (assuming I really had any clue as to how they worked or how to set them
>> up)
>> would even work (I came across a page that says only Enterprise supports
>> them).
>>
>|||And since the report server *is* being accessed externally, it's quite
definitely not fine for our needs.
"/dev/null" <devnull@.discussions.microsoft.com> wrote in message
news:2BD4677A-BEF2-4048-92B9-316F38813E85@.microsoft.com...
> To my knowledge, for IE to give your windows authentication credentials
> automatically to IIS, you have to adresse the website using a machine
> name.
> Use something like http://computername/ReportServer
> Suppose your server is named BigBox.leetdomain.com
> Address it using http://BigBox/ReportServer
> I never had to mess around with the report server security as we want
> windows authentication and the report server will not be used externally.
> It
> seems to be configured fine by default.
>
> "DJM" wrote:
>> I'm trying to incorporate the /ReportServer virtual into our application
>> web
>> site so we can point the Report Viewer control to /ReportServer rather
>> than
>> <default web site's IP>/ReportServer. I'm trying to remove
>> authentication
>> from <app>/ReportServer, but I've been unable to keep it from popping up
>> that blasted login dialog box. I've briefly tried using the FormsAuth
>> sample and even rewrote it to always return True from all the
>> authentication
>> functions, but neither solved my problem.
>> The application is under Forms Auth. I just want the ReportServer
>> virtual
>> to run in that context. At this point, I don't even care if it's really
>> keeping authentication or just letting anyone who happens to type in
>> http://<our_app>/ReportServer go uncontested. It's extremely
>> frustrating,
>> and it doesn't work for any users as it is now.
>> This is SQL Server Standard, so I don't know if custom security
>> extensions
>> (assuming I really had any clue as to how they worked or how to set them
>> up)
>> would even work (I came across a page that says only Enterprise supports
>> them).
>>
>>|||Ok, I've set Authentication mode="None", I've set the /ReportServer virtual
to allow Anonymous access as IUSR, I've granted IUSR_GROUP (local group
containing the IUSR, IWAM, and ASP.NET users) access to the \MSSQL\Reporting
Services\ReportServer folder (and files and subfolders), and I've granted
the IUSR_GROUP the Browser role to all reports.
So why, when using the Report Viewer web control, do I see a login box?!?|||Does anyone have any advice or suggestions here? I still haven't resolved
this.
"DJM" <msnews@.puddlestheshark.com> wrote in message
news:uLeyyLnzEHA.352@.TK2MSFTNGP14.phx.gbl...
> Ok, I've set Authentication mode="None", I've set the /ReportServer
> virtual to allow Anonymous access as IUSR, I've granted IUSR_GROUP (local
> group containing the IUSR, IWAM, and ASP.NET users) access to the
> \MSSQL\Reporting Services\ReportServer folder (and files and subfolders),
> and I've granted the IUSR_GROUP the Browser role to all reports.
> So why, when using the Report Viewer web control, do I see a login box?!?
>|||You have two options:
1. Generating the report on the server side of the application by using the
Render SOAP API. The advantage of this approach is that it is more secure
since the user doesn't see the report URL (everything takes place on the
server). The tradeoff is that the interactive features (drilldown,
drillthrough, etc.) will not work with SOAP since their require direct
access to the Report Server by URL. If you decide to take this approach, you
can pass the web app identity to the Report Server and grant a minimum set
of permissions in RS to this account.
2. Replace the RS Windows security with Forms Authentication by writing a
custom security extension. This will allow you to incorporate interactive
features in your reports. In this scenario, the reports will be requested on
the client side of the application (e.g. by using the Report Viewer sample
control). If you decide to take this approach check out the sample security
extension from MS at
(http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.a
sp?frame=true#ufairs_topic3).
So, you have to carefully weight out your requirements for security,
reporting features and your application architecture to determine the best
integration scenario.
--
Hope this helps.
Rags Iyer
"DJM" wrote:
> I'm trying to incorporate the /ReportServer virtual into our application web
> site so we can point the Report Viewer control to /ReportServer rather than
> <default web site's IP>/ReportServer. I'm trying to remove authentication
> from <app>/ReportServer, but I've been unable to keep it from popping up
> that blasted login dialog box. I've briefly tried using the FormsAuth
> sample and even rewrote it to always return True from all the authentication
> functions, but neither solved my problem.
> The application is under Forms Auth. I just want the ReportServer virtual
> to run in that context. At this point, I don't even care if it's really
> keeping authentication or just letting anyone who happens to type in
> http://<our_app>/ReportServer go uncontested. It's extremely frustrating,
> and it doesn't work for any users as it is now.
> This is SQL Server Standard, so I don't know if custom security extensions
> (assuming I really had any clue as to how they worked or how to set them up)
> would even work (I came across a page that says only Enterprise supports
> them).
>
>|||The second option would be ideal, but if you'll notice, I tried that and was
unable to get it to make a shred of difference. Maybe I'm just not smart
enough to figure it out, but it's not working for me.
"Rags Iyer" <RagsIyer@.discussions.microsoft.com> wrote in message
news:A2AD6EB1-4CE8-4305-8063-8EE6AC9E1828@.microsoft.com...
> You have two options:
> 1. Generating the report on the server side of the application by using
> the
> Render SOAP API. The advantage of this approach is that it is more secure
> since the user doesn't see the report URL (everything takes place on the
> server). The tradeoff is that the interactive features (drilldown,
> drillthrough, etc.) will not work with SOAP since their require direct
> access to the Report Server by URL. If you decide to take this approach,
> you
> can pass the web app identity to the Report Server and grant a minimum set
> of permissions in RS to this account.
> 2. Replace the RS Windows security with Forms Authentication by writing a
> custom security extension. This will allow you to incorporate interactive
> features in your reports. In this scenario, the reports will be requested
> on
> the client side of the application (e.g. by using the Report Viewer sample
> control). If you decide to take this approach check out the sample
> security
> extension from MS at
> (http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.a
> sp?frame=true#ufairs_topic3).
> So, you have to carefully weight out your requirements for security,
> reporting features and your application architecture to determine the best
> integration scenario.
> --
> Hope this helps.
> Rags Iyer
> "DJM" wrote:
>> I'm trying to incorporate the /ReportServer virtual into our application
>> web
>> site so we can point the Report Viewer control to /ReportServer rather
>> than
>> <default web site's IP>/ReportServer. I'm trying to remove
>> authentication
>> from <app>/ReportServer, but I've been unable to keep it from popping up
>> that blasted login dialog box. I've briefly tried using the FormsAuth
>> sample and even rewrote it to always return True from all the
>> authentication
>> functions, but neither solved my problem.
>> The application is under Forms Auth. I just want the ReportServer
>> virtual
>> to run in that context. At this point, I don't even care if it's really
>> keeping authentication or just letting anyone who happens to type in
>> http://<our_app>/ReportServer go uncontested. It's extremely
>> frustrating,
>> and it doesn't work for any users as it is now.
>> This is SQL Server Standard, so I don't know if custom security
>> extensions
>> (assuming I really had any clue as to how they worked or how to set them
>> up)
>> would even work (I came across a page that says only Enterprise supports
>> them).
>>
>>|||Is the Login Box still popping up when Forms Authetication is Implemented.?
"DJM" wrote:
> The second option would be ideal, but if you'll notice, I tried that and was
> unable to get it to make a shred of difference. Maybe I'm just not smart
> enough to figure it out, but it's not working for me.
> "Rags Iyer" <RagsIyer@.discussions.microsoft.com> wrote in message
> news:A2AD6EB1-4CE8-4305-8063-8EE6AC9E1828@.microsoft.com...
> > You have two options:
> >
> > 1. Generating the report on the server side of the application by using
> > the
> > Render SOAP API. The advantage of this approach is that it is more secure
> > since the user doesn't see the report URL (everything takes place on the
> > server). The tradeoff is that the interactive features (drilldown,
> > drillthrough, etc.) will not work with SOAP since their require direct
> > access to the Report Server by URL. If you decide to take this approach,
> > you
> > can pass the web app identity to the Report Server and grant a minimum set
> > of permissions in RS to this account.
> >
> > 2. Replace the RS Windows security with Forms Authentication by writing a
> > custom security extension. This will allow you to incorporate interactive
> > features in your reports. In this scenario, the reports will be requested
> > on
> > the client side of the application (e.g. by using the Report Viewer sample
> > control). If you decide to take this approach check out the sample
> > security
> > extension from MS at
> > (http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.a
> > sp?frame=true#ufairs_topic3).
> >
> > So, you have to carefully weight out your requirements for security,
> > reporting features and your application architecture to determine the best
> > integration scenario.
> > --
> > Hope this helps.
> >
> > Rags Iyer
> >
> > "DJM" wrote:
> >
> >> I'm trying to incorporate the /ReportServer virtual into our application
> >> web
> >> site so we can point the Report Viewer control to /ReportServer rather
> >> than
> >> <default web site's IP>/ReportServer. I'm trying to remove
> >> authentication
> >> from <app>/ReportServer, but I've been unable to keep it from popping up
> >> that blasted login dialog box. I've briefly tried using the FormsAuth
> >> sample and even rewrote it to always return True from all the
> >> authentication
> >> functions, but neither solved my problem.
> >>
> >> The application is under Forms Auth. I just want the ReportServer
> >> virtual
> >> to run in that context. At this point, I don't even care if it's really
> >> keeping authentication or just letting anyone who happens to type in
> >> http://<our_app>/ReportServer go uncontested. It's extremely
> >> frustrating,
> >> and it doesn't work for any users as it is now.
> >>
> >> This is SQL Server Standard, so I don't know if custom security
> >> extensions
> >> (assuming I really had any clue as to how they worked or how to set them
> >> up)
> >> would even work (I came across a page that says only Enterprise supports
> >> them).
> >>
> >>
> >>
> >>
>
>|||Yes.
"Rags Iyer" <RagsIyer@.discussions.microsoft.com> wrote in message
news:C574F74E-C580-47A4-86AD-BCD62253C700@.microsoft.com...
> Is the Login Box still popping up when Forms Authetication is
> Implemented.?|||Please check wherther the Security implemenation for Forms Authetication was
properly implemented.The authetication in the Rs Config File needs to be
changed to Forms Authentication and all the required policy is set.
Regards,
Rags Iyer
"DJM" wrote:
> Yes.
> "Rags Iyer" <RagsIyer@.discussions.microsoft.com> wrote in message
> news:C574F74E-C580-47A4-86AD-BCD62253C700@.microsoft.com...
> >
> > Is the Login Box still popping up when Forms Authetication is
> > Implemented.?
>
>|||Please check wherther the Security implemenation for Forms Authetication was
properly implemented.The authetication in the Rs Config File needs to be
changed to Forms Authentication and all the required policy is set.
Regards,
Rags Iyer
"DJM" wrote:
> Yes.
> "Rags Iyer" <RagsIyer@.discussions.microsoft.com> wrote in message
> news:C574F74E-C580-47A4-86AD-BCD62253C700@.microsoft.com...
> >
> > Is the Login Box still popping up when Forms Authetication is
> > Implemented.?
>
>|||I did that according to the documentation (adding Forms to the
authentication types or wherever that is, and changing authentication mode
to Forms in the web.config), and I was still getting the login box.
What gets me is that box is still appearing even when I set the
authentication mode to None.
"Rags Iyer" <RagsIyer@.discussions.microsoft.com> wrote in message
news:E5169863-A011-43A4-BAB3-A0627C0A0272@.microsoft.com...
> Please check wherther the Security implemenation for Forms Authetication
> was
> properly implemented.The authetication in the Rs Config File needs to be
> changed to Forms Authentication and all the required policy is set.
> Regards,
> Rags Iyer
> "DJM" wrote:
>> Yes.
>> "Rags Iyer" <RagsIyer@.discussions.microsoft.com> wrote in message
>> news:C574F74E-C580-47A4-86AD-BCD62253C700@.microsoft.com...
>> >
>> > Is the Login Box still popping up when Forms Authetication is
>> > Implemented.?
>>
Wednesday, March 7, 2012
Maintenance Routine - Best Practice
Please point me in the right direction. I am not a SQL DBA, nor am I very
experienced with SQL, nor does our budget allow for someone who is. I have
an experienced SQL pro available to me but believe I can handle the task at
hand with a little help.
I need to set up maintenance and backup routines for my SQL databases. I am
relatively familiar with Enterprise Manager and think I can see my way
through using a wizard to set up a proper maintenance plan. Problem is I
don't know what a proper plan is and I've heard that the wizards don't always
offer choices that fall in line with the best practices or pro's general
consensus on how things should be done.
I believe that part of the maintenance plan should be to truncate our logs .
.. . but I don't know why we would do that given that the log files would be
used to recover the databases if something went wrong. I'm interested in
knowing if you'll share.
I am not opposed to reading and learning so please feel free to point me to
a recommended document or best practices article. Please bear in mind,
however, that I am not a DBA and (I am also very aware of our need for one)
as such the easier the reading the more helpful it will be.
Thanks for the help,
Po
"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> Please point me in the right direction. I am not a SQL DBA, nor am I very
> experienced with SQL, nor does our budget allow for someone who is. I
> have
> an experienced SQL pro available to me but believe I can handle the task
> at
> hand with a little help.
> I need to set up maintenance and backup routines for my SQL databases. I
> am
> relatively familiar with Enterprise Manager and think I can see my way
> through using a wizard to set up a proper maintenance plan. Problem is I
> don't know what a proper plan is and I've heard that the wizards don't
> always
> offer choices that fall in line with the best practices or pro's general
> consensus on how things should be done.
If it's "good enough" you can do a lot of this with Enterprise manager.
Typically you'll do a FULL backup every "X days" (I tend to do once a night,
my old company we did every other night, etc. Depends a lot on different
factors).
Then do a TRANSACTION backup every "Y minutes" (it could be 60, 120, 5,
etc.)
The transaction log will "truncate" the log. Note that it will NOT shrink
the physical log file nor do you normally want to.
Typically figure "how long can I be down and how much data can I afford to
lose?"
When you recover from a failure, you'll restore the FULL backup first with
NORECOVERY and then EACH AND EVERY transaction log since that backup. This
is where those various factors come into play. For example, if you're doing
a transaction log backup every 5 minutes, you probably don't want to do a
FULL backup once a week as the worse case recovery scenario can be nasty.
(btw, you can do incremental backups betwene full ones which would help
there, but that starts to get complicated.)
Once you've got your backup scheme in place... TEST IT! I can't tell you
the number of times that I've seen people do these beautiful backup
schemes... only to have them fail when they need them. And once you test
it, practice it. Nothing is worse than in the heat of an emergency to
forget the critical steps required (e.g. forgetting to add NORECOVERY when
restoring the 50 gig DB :-)
As for other items, others can give advice on rebuilding indexes, etc.
(Though I often tend to a simple job of doing a 10% update on stats on all
tables if nothing else.)
> I believe that part of the maintenance plan should be to truncate our logs
> .
> . . but I don't know why we would do that given that the log files would
> be
> used to recover the databases if something went wrong. I'm interested in
> knowing if you'll share.
> I am not opposed to reading and learning so please feel free to point me
> to
> a recommended document or best practices article. Please bear in mind,
> however, that I am not a DBA and (I am also very aware of our need for
> one)
> as such the easier the reading the more helpful it will be.
> Thanks for the help,
> Po
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Backing up the log truncates in most situations. If the database is small
and it is all you have to manage, I would just create 3 separate maint
plans.
Backups:
Nightly full's
Hourly tlogs
Indexes\Stats
Weekly or nighly depending on the impact you can handle
Checkdb
Weekly
The idea is to keep it simple but depending on your hardware, you may have
to use a custom solution once you get to 10-50GB.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> Please point me in the right direction. I am not a SQL DBA, nor am I very
> experienced with SQL, nor does our budget allow for someone who is. I
> have
> an experienced SQL pro available to me but believe I can handle the task
> at
> hand with a little help.
> I need to set up maintenance and backup routines for my SQL databases. I
> am
> relatively familiar with Enterprise Manager and think I can see my way
> through using a wizard to set up a proper maintenance plan. Problem is I
> don't know what a proper plan is and I've heard that the wizards don't
> always
> offer choices that fall in line with the best practices or pro's general
> consensus on how things should be done.
> I believe that part of the maintenance plan should be to truncate our logs
> .
> . . but I don't know why we would do that given that the log files would
> be
> used to recover the databases if something went wrong. I'm interested in
> knowing if you'll share.
> I am not opposed to reading and learning so please feel free to point me
> to
> a recommended document or best practices article. Please bear in mind,
> however, that I am not a DBA and (I am also very aware of our need for
> one)
> as such the easier the reading the more helpful it will be.
> Thanks for the help,
> Po
|||Pretty standard backup:
large databases:
Full database - weekly
Differentials - daily
T-logs - daily every 15 - 60 minutes
Small databases:
Full - daily
T-logs - daily every hour
Let the MP "Integrity check" option run once a week
Let the "Optimizations" part run once a week for small databases, more often
if they are large or high transaction (this is the index rebuilding part,
which is important for performance)
These are very generic guidelines.
3 seperate plans:
1 for system databases only
1 for databases in full recovery model
1 for databases in Simple recovery model
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> Please point me in the right direction. I am not a SQL DBA, nor am I very
> experienced with SQL, nor does our budget allow for someone who is. I
> have
> an experienced SQL pro available to me but believe I can handle the task
> at
> hand with a little help.
> I need to set up maintenance and backup routines for my SQL databases. I
> am
> relatively familiar with Enterprise Manager and think I can see my way
> through using a wizard to set up a proper maintenance plan. Problem is I
> don't know what a proper plan is and I've heard that the wizards don't
> always
> offer choices that fall in line with the best practices or pro's general
> consensus on how things should be done.
> I believe that part of the maintenance plan should be to truncate our logs
> .
> . . but I don't know why we would do that given that the log files would
> be
> used to recover the databases if something went wrong. I'm interested in
> knowing if you'll share.
> I am not opposed to reading and learning so please feel free to point me
> to
> a recommended document or best practices article. Please bear in mind,
> however, that I am not a DBA and (I am also very aware of our need for
> one)
> as such the easier the reading the more helpful it will be.
> Thanks for the help,
> Po
|||Thank you everyone for the help. I noticed that all the posts referred in
one way or another to the size of the databases. I right-clicked on my
biggest database - it's about 57GB. How does that affect the advice given?
Please accept my apologies for not including the above information in the
first place. Also the server is a newer Dell 2950
2 Dual Core Xeon Processors (2.67GHz)
4GB RAM
RAID 1 OS Partition
RAID 5 Data Partition
We use the SonicWall CDP appliance for backup - so maintaining the database
is my bigger concern. Although I do plan to use the SQL native backup just
to be extra safe. I'm mentioning this because we don't have a 3rd partition
for log files (CDP handles SQL backup and restoration differently than SQL's
native backup I'm told) nor do I ship the log files - but that might only be
because I don't know what that means.
Testing in our environment is difficult as we are a 24hr company and taking
down this server costs us the use of our main application. I suppose I could
use a spare server to restore the database but I'm not sure of the best way
to confirm that the restoration was successful and the data is most up to
date. I'm not a regular user of the application so I wonder is there a way
to query the database to get the latest information written to it?
Sorry I'm growing this thread - I very much appreciate the help though.
Po
"Kevin3NF" wrote:
> Pretty standard backup:
> large databases:
> Full database - weekly
> Differentials - daily
> T-logs - daily every 15 - 60 minutes
> Small databases:
> Full - daily
> T-logs - daily every hour
> Let the MP "Integrity check" option run once a week
> Let the "Optimizations" part run once a week for small databases, more often
> if they are large or high transaction (this is the index rebuilding part,
> which is important for performance)
> These are very generic guidelines.
> 3 seperate plans:
> 1 for system databases only
> 1 for databases in full recovery model
> 1 for databases in Simple recovery model
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
>
>
|||No idea what a Sonic Wall CDP is, but if its doing SQL Server
backups/restores for you, your supportability by Microsoft and those of us
that know SQL pretty well might be more limited by that fact. Advice I give
everyone...Use SQL Native backup or a WELL-KNOWN 3rd party utility such as
Litespeed to do the backups, then move/copy/archive the resulting backup
files.
57GB is a good sized database. If it is transactional, I would almost
certainly go with the Weekly full, daily Differential, hourly T-log method.
I don't think a maintenance plan offers Differentials as part of the wizard,
so you may have to create a new job with a Backup command in it:
BACKUP DATABASE MyNwind
TO MyNwind_1
WITH DIFFERENTIAL
GO
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:2562F745-F3B4-493A-AE4E-2359F245E017@.microsoft.com...[vbcol=seagreen]
> Thank you everyone for the help. I noticed that all the posts referred in
> one way or another to the size of the databases. I right-clicked on my
> biggest database - it's about 57GB. How does that affect the advice
> given?
> Please accept my apologies for not including the above information in the
> first place. Also the server is a newer Dell 2950
> 2 Dual Core Xeon Processors (2.67GHz)
> 4GB RAM
> RAID 1 OS Partition
> RAID 5 Data Partition
> We use the SonicWall CDP appliance for backup - so maintaining the
> database
> is my bigger concern. Although I do plan to use the SQL native backup
> just
> to be extra safe. I'm mentioning this because we don't have a 3rd
> partition
> for log files (CDP handles SQL backup and restoration differently than
> SQL's
> native backup I'm told) nor do I ship the log files - but that might only
> be
> because I don't know what that means.
> Testing in our environment is difficult as we are a 24hr company and
> taking
> down this server costs us the use of our main application. I suppose I
> could
> use a spare server to restore the database but I'm not sure of the best
> way
> to confirm that the restoration was successful and the data is most up to
> date. I'm not a regular user of the application so I wonder is there a
> way
> to query the database to get the latest information written to it?
> Sorry I'm growing this thread - I very much appreciate the help though.
> Po
> "Kevin3NF" wrote:
|||"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:2562F745-F3B4-493A-AE4E-2359F245E017@.microsoft.com...
> Thank you everyone for the help. I noticed that all the posts referred in
> one way or another to the size of the databases. I right-clicked on my
> biggest database - it's about 57GB. How does that affect the advice
> given?
>
Size only partly comes into play.
The factor is related to how long it takes to backup and restore. If you
have slow disks and it takes 8 hours to restore, you've got problems.
And how much the DB changes.
If it's a 57GB DB but there's 100GB of transactions a day, that can make a
difference. If there's 1MB of transactions, that can make things simpler.
> We use the SonicWall CDP appliance for backup - so maintaining the
> database
> is my bigger concern. Although I do plan to use the SQL native backup
> just
> to be extra safe. I'm mentioning this because we don't have a 3rd
> partition
> for log files (CDP handles SQL backup and restoration differently than
> SQL's
> native backup I'm told) nor do I ship the log files - but that might only
> be
> because I don't know what that means.
>
Not familiar with Sonicwall.
I tend to turst the SQL Server native backup.
Shipping basically means restoring the logs automatically to another server.
It provides faster recovery time AND provides testing of the backup/restore
process.
> Testing in our environment is difficult as we are a 24hr company and
> taking
> down this server costs us the use of our main application. I suppose I
> could
> use a spare server to restore the database but I'm not sure of the best
> way
> to confirm that the restoration was successful and the data is most up to
> date.
That's really the way to do it since if your main server fails, you're going
to need to use the second server anyway.
> I'm not a regular user of the application so I wonder is there a way
> to query the database to get the latest information written to it?
>
That's pretty much application specific. But 'yes' is the basic answer.
[vbcol=seagreen]
> Sorry I'm growing this thread - I very much appreciate the help though.
> Po
> "Kevin3NF" wrote:
|||Guys, thanks for the great information. The CDP is a pretty nice real-time
backup appliance. It does the job however it conflicts in a big way with the
SQL native routines. I've been using its SQL agent because I didn't have
backups and maintenance properly configured in SQL. Thanks to your help I'm
that much closer and will probably use the CDP to backup the SQL natiive data
and log backup files.
One or two more questions, though. 1. - How do I know if my database is
transactional? I think because it is written to regularly that it is. Am I
right?
2. How do I determine HOW transactional it is (1MB or 100MB)?
Now a better question (I hope). If log shipping happens extremely often -
every 5 minutes what is the point of replication software like DoubleTake?
Automatic failover, maybe? Or is it that the 5 minutes between log backups
isn't lost if something happens?
Thanks for your continued help.
Po
"Greg D. Moore (Strider)" wrote:
> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> news:2562F745-F3B4-493A-AE4E-2359F245E017@.microsoft.com...
> Size only partly comes into play.
> The factor is related to how long it takes to backup and restore. If you
> have slow disks and it takes 8 hours to restore, you've got problems.
> And how much the DB changes.
> If it's a 57GB DB but there's 100GB of transactions a day, that can make a
> difference. If there's 1MB of transactions, that can make things simpler.
>
> Not familiar with Sonicwall.
> I tend to turst the SQL Server native backup.
> Shipping basically means restoring the logs automatically to another server.
> It provides faster recovery time AND provides testing of the backup/restore
> process.
>
> That's really the way to do it since if your main server fails, you're going
> to need to use the second server anyway.
>
> That's pretty much application specific. But 'yes' is the basic answer.
>
>
|||"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:476126FC-F71F-42A8-A293-7FA7713B50D1@.microsoft.com...
> Guys, thanks for the great information. The CDP is a pretty nice
> real-time
> backup appliance. It does the job however it conflicts in a big way with
> the
> SQL native routines. I've been using its SQL agent because I didn't have
> backups and maintenance properly configured in SQL. Thanks to your help
> I'm
> that much closer and will probably use the CDP to backup the SQL natiive
> data
> and log backup files.
> One or two more questions, though. 1. - How do I know if my database is
> transactional? I think because it is written to regularly that it is. Am
> I
> right?
Yeah, basically if you're doing queries and updates, it's transactional.
If it's really only for reports and the like, it's more likely an OLAP.
(there's other guidelines, but that's the simple version :-)
> 2. How do I determine HOW transactional it is (1MB or 100MB)?
>
Monitor the size of the log and see how fast it grows.
> Now a better question (I hope). If log shipping happens extremely often -
> every 5 minutes what is the point of replication software like DoubleTake?
> Automatic failover, maybe? Or is it that the 5 minutes between log
> backups
> isn't lost if something happens?
>
Not famliar DoubleTake. But replication is "different". Generally used for
creating extra copies of data to scale out.
It can be part of a DR solution, but it's not a complete solution in and of
itself.
> Thanks for your continued help.
>
No problem.
> Po
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||DoubleTake is a drive level copy process...if a bit changes, that is
replicated. Slick stuff from the marketing materials
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:udR5OWAVIHA.4440@.TK2MSFTNGP06.phx.gbl...
> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> news:476126FC-F71F-42A8-A293-7FA7713B50D1@.microsoft.com...
> Yeah, basically if you're doing queries and updates, it's transactional.
> If it's really only for reports and the like, it's more likely an OLAP.
> (there's other guidelines, but that's the simple version :-)
>
> Monitor the size of the log and see how fast it grows.
>
> Not famliar DoubleTake. But replication is "different". Generally used
> for creating extra copies of data to scale out.
> It can be part of a DR solution, but it's not a complete solution in and
> of itself.
>
>
> No problem.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
experienced with SQL, nor does our budget allow for someone who is. I have
an experienced SQL pro available to me but believe I can handle the task at
hand with a little help.
I need to set up maintenance and backup routines for my SQL databases. I am
relatively familiar with Enterprise Manager and think I can see my way
through using a wizard to set up a proper maintenance plan. Problem is I
don't know what a proper plan is and I've heard that the wizards don't always
offer choices that fall in line with the best practices or pro's general
consensus on how things should be done.
I believe that part of the maintenance plan should be to truncate our logs .
.. . but I don't know why we would do that given that the log files would be
used to recover the databases if something went wrong. I'm interested in
knowing if you'll share.
I am not opposed to reading and learning so please feel free to point me to
a recommended document or best practices article. Please bear in mind,
however, that I am not a DBA and (I am also very aware of our need for one)
as such the easier the reading the more helpful it will be.
Thanks for the help,
Po
"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> Please point me in the right direction. I am not a SQL DBA, nor am I very
> experienced with SQL, nor does our budget allow for someone who is. I
> have
> an experienced SQL pro available to me but believe I can handle the task
> at
> hand with a little help.
> I need to set up maintenance and backup routines for my SQL databases. I
> am
> relatively familiar with Enterprise Manager and think I can see my way
> through using a wizard to set up a proper maintenance plan. Problem is I
> don't know what a proper plan is and I've heard that the wizards don't
> always
> offer choices that fall in line with the best practices or pro's general
> consensus on how things should be done.
If it's "good enough" you can do a lot of this with Enterprise manager.
Typically you'll do a FULL backup every "X days" (I tend to do once a night,
my old company we did every other night, etc. Depends a lot on different
factors).
Then do a TRANSACTION backup every "Y minutes" (it could be 60, 120, 5,
etc.)
The transaction log will "truncate" the log. Note that it will NOT shrink
the physical log file nor do you normally want to.
Typically figure "how long can I be down and how much data can I afford to
lose?"
When you recover from a failure, you'll restore the FULL backup first with
NORECOVERY and then EACH AND EVERY transaction log since that backup. This
is where those various factors come into play. For example, if you're doing
a transaction log backup every 5 minutes, you probably don't want to do a
FULL backup once a week as the worse case recovery scenario can be nasty.
(btw, you can do incremental backups betwene full ones which would help
there, but that starts to get complicated.)
Once you've got your backup scheme in place... TEST IT! I can't tell you
the number of times that I've seen people do these beautiful backup
schemes... only to have them fail when they need them. And once you test
it, practice it. Nothing is worse than in the heat of an emergency to
forget the critical steps required (e.g. forgetting to add NORECOVERY when
restoring the 50 gig DB :-)
As for other items, others can give advice on rebuilding indexes, etc.
(Though I often tend to a simple job of doing a 10% update on stats on all
tables if nothing else.)
> I believe that part of the maintenance plan should be to truncate our logs
> .
> . . but I don't know why we would do that given that the log files would
> be
> used to recover the databases if something went wrong. I'm interested in
> knowing if you'll share.
> I am not opposed to reading and learning so please feel free to point me
> to
> a recommended document or best practices article. Please bear in mind,
> however, that I am not a DBA and (I am also very aware of our need for
> one)
> as such the easier the reading the more helpful it will be.
> Thanks for the help,
> Po
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Backing up the log truncates in most situations. If the database is small
and it is all you have to manage, I would just create 3 separate maint
plans.
Backups:
Nightly full's
Hourly tlogs
Indexes\Stats
Weekly or nighly depending on the impact you can handle
Checkdb
Weekly
The idea is to keep it simple but depending on your hardware, you may have
to use a custom solution once you get to 10-50GB.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> Please point me in the right direction. I am not a SQL DBA, nor am I very
> experienced with SQL, nor does our budget allow for someone who is. I
> have
> an experienced SQL pro available to me but believe I can handle the task
> at
> hand with a little help.
> I need to set up maintenance and backup routines for my SQL databases. I
> am
> relatively familiar with Enterprise Manager and think I can see my way
> through using a wizard to set up a proper maintenance plan. Problem is I
> don't know what a proper plan is and I've heard that the wizards don't
> always
> offer choices that fall in line with the best practices or pro's general
> consensus on how things should be done.
> I believe that part of the maintenance plan should be to truncate our logs
> .
> . . but I don't know why we would do that given that the log files would
> be
> used to recover the databases if something went wrong. I'm interested in
> knowing if you'll share.
> I am not opposed to reading and learning so please feel free to point me
> to
> a recommended document or best practices article. Please bear in mind,
> however, that I am not a DBA and (I am also very aware of our need for
> one)
> as such the easier the reading the more helpful it will be.
> Thanks for the help,
> Po
|||Pretty standard backup:
large databases:
Full database - weekly
Differentials - daily
T-logs - daily every 15 - 60 minutes
Small databases:
Full - daily
T-logs - daily every hour
Let the MP "Integrity check" option run once a week
Let the "Optimizations" part run once a week for small databases, more often
if they are large or high transaction (this is the index rebuilding part,
which is important for performance)
These are very generic guidelines.
3 seperate plans:
1 for system databases only
1 for databases in full recovery model
1 for databases in Simple recovery model
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> Please point me in the right direction. I am not a SQL DBA, nor am I very
> experienced with SQL, nor does our budget allow for someone who is. I
> have
> an experienced SQL pro available to me but believe I can handle the task
> at
> hand with a little help.
> I need to set up maintenance and backup routines for my SQL databases. I
> am
> relatively familiar with Enterprise Manager and think I can see my way
> through using a wizard to set up a proper maintenance plan. Problem is I
> don't know what a proper plan is and I've heard that the wizards don't
> always
> offer choices that fall in line with the best practices or pro's general
> consensus on how things should be done.
> I believe that part of the maintenance plan should be to truncate our logs
> .
> . . but I don't know why we would do that given that the log files would
> be
> used to recover the databases if something went wrong. I'm interested in
> knowing if you'll share.
> I am not opposed to reading and learning so please feel free to point me
> to
> a recommended document or best practices article. Please bear in mind,
> however, that I am not a DBA and (I am also very aware of our need for
> one)
> as such the easier the reading the more helpful it will be.
> Thanks for the help,
> Po
|||Thank you everyone for the help. I noticed that all the posts referred in
one way or another to the size of the databases. I right-clicked on my
biggest database - it's about 57GB. How does that affect the advice given?
Please accept my apologies for not including the above information in the
first place. Also the server is a newer Dell 2950
2 Dual Core Xeon Processors (2.67GHz)
4GB RAM
RAID 1 OS Partition
RAID 5 Data Partition
We use the SonicWall CDP appliance for backup - so maintaining the database
is my bigger concern. Although I do plan to use the SQL native backup just
to be extra safe. I'm mentioning this because we don't have a 3rd partition
for log files (CDP handles SQL backup and restoration differently than SQL's
native backup I'm told) nor do I ship the log files - but that might only be
because I don't know what that means.
Testing in our environment is difficult as we are a 24hr company and taking
down this server costs us the use of our main application. I suppose I could
use a spare server to restore the database but I'm not sure of the best way
to confirm that the restoration was successful and the data is most up to
date. I'm not a regular user of the application so I wonder is there a way
to query the database to get the latest information written to it?
Sorry I'm growing this thread - I very much appreciate the help though.
Po
"Kevin3NF" wrote:
> Pretty standard backup:
> large databases:
> Full database - weekly
> Differentials - daily
> T-logs - daily every 15 - 60 minutes
> Small databases:
> Full - daily
> T-logs - daily every hour
> Let the MP "Integrity check" option run once a week
> Let the "Optimizations" part run once a week for small databases, more often
> if they are large or high transaction (this is the index rebuilding part,
> which is important for performance)
> These are very generic guidelines.
> 3 seperate plans:
> 1 for system databases only
> 1 for databases in full recovery model
> 1 for databases in Simple recovery model
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
>
>
|||No idea what a Sonic Wall CDP is, but if its doing SQL Server
backups/restores for you, your supportability by Microsoft and those of us
that know SQL pretty well might be more limited by that fact. Advice I give
everyone...Use SQL Native backup or a WELL-KNOWN 3rd party utility such as
Litespeed to do the backups, then move/copy/archive the resulting backup
files.
57GB is a good sized database. If it is transactional, I would almost
certainly go with the Weekly full, daily Differential, hourly T-log method.
I don't think a maintenance plan offers Differentials as part of the wizard,
so you may have to create a new job with a Backup command in it:
BACKUP DATABASE MyNwind
TO MyNwind_1
WITH DIFFERENTIAL
GO
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:2562F745-F3B4-493A-AE4E-2359F245E017@.microsoft.com...[vbcol=seagreen]
> Thank you everyone for the help. I noticed that all the posts referred in
> one way or another to the size of the databases. I right-clicked on my
> biggest database - it's about 57GB. How does that affect the advice
> given?
> Please accept my apologies for not including the above information in the
> first place. Also the server is a newer Dell 2950
> 2 Dual Core Xeon Processors (2.67GHz)
> 4GB RAM
> RAID 1 OS Partition
> RAID 5 Data Partition
> We use the SonicWall CDP appliance for backup - so maintaining the
> database
> is my bigger concern. Although I do plan to use the SQL native backup
> just
> to be extra safe. I'm mentioning this because we don't have a 3rd
> partition
> for log files (CDP handles SQL backup and restoration differently than
> SQL's
> native backup I'm told) nor do I ship the log files - but that might only
> be
> because I don't know what that means.
> Testing in our environment is difficult as we are a 24hr company and
> taking
> down this server costs us the use of our main application. I suppose I
> could
> use a spare server to restore the database but I'm not sure of the best
> way
> to confirm that the restoration was successful and the data is most up to
> date. I'm not a regular user of the application so I wonder is there a
> way
> to query the database to get the latest information written to it?
> Sorry I'm growing this thread - I very much appreciate the help though.
> Po
> "Kevin3NF" wrote:
|||"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:2562F745-F3B4-493A-AE4E-2359F245E017@.microsoft.com...
> Thank you everyone for the help. I noticed that all the posts referred in
> one way or another to the size of the databases. I right-clicked on my
> biggest database - it's about 57GB. How does that affect the advice
> given?
>
Size only partly comes into play.
The factor is related to how long it takes to backup and restore. If you
have slow disks and it takes 8 hours to restore, you've got problems.
And how much the DB changes.
If it's a 57GB DB but there's 100GB of transactions a day, that can make a
difference. If there's 1MB of transactions, that can make things simpler.
> We use the SonicWall CDP appliance for backup - so maintaining the
> database
> is my bigger concern. Although I do plan to use the SQL native backup
> just
> to be extra safe. I'm mentioning this because we don't have a 3rd
> partition
> for log files (CDP handles SQL backup and restoration differently than
> SQL's
> native backup I'm told) nor do I ship the log files - but that might only
> be
> because I don't know what that means.
>
Not familiar with Sonicwall.
I tend to turst the SQL Server native backup.
Shipping basically means restoring the logs automatically to another server.
It provides faster recovery time AND provides testing of the backup/restore
process.
> Testing in our environment is difficult as we are a 24hr company and
> taking
> down this server costs us the use of our main application. I suppose I
> could
> use a spare server to restore the database but I'm not sure of the best
> way
> to confirm that the restoration was successful and the data is most up to
> date.
That's really the way to do it since if your main server fails, you're going
to need to use the second server anyway.
> I'm not a regular user of the application so I wonder is there a way
> to query the database to get the latest information written to it?
>
That's pretty much application specific. But 'yes' is the basic answer.
[vbcol=seagreen]
> Sorry I'm growing this thread - I very much appreciate the help though.
> Po
> "Kevin3NF" wrote:
|||Guys, thanks for the great information. The CDP is a pretty nice real-time
backup appliance. It does the job however it conflicts in a big way with the
SQL native routines. I've been using its SQL agent because I didn't have
backups and maintenance properly configured in SQL. Thanks to your help I'm
that much closer and will probably use the CDP to backup the SQL natiive data
and log backup files.
One or two more questions, though. 1. - How do I know if my database is
transactional? I think because it is written to regularly that it is. Am I
right?
2. How do I determine HOW transactional it is (1MB or 100MB)?
Now a better question (I hope). If log shipping happens extremely often -
every 5 minutes what is the point of replication software like DoubleTake?
Automatic failover, maybe? Or is it that the 5 minutes between log backups
isn't lost if something happens?
Thanks for your continued help.
Po
"Greg D. Moore (Strider)" wrote:
> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> news:2562F745-F3B4-493A-AE4E-2359F245E017@.microsoft.com...
> Size only partly comes into play.
> The factor is related to how long it takes to backup and restore. If you
> have slow disks and it takes 8 hours to restore, you've got problems.
> And how much the DB changes.
> If it's a 57GB DB but there's 100GB of transactions a day, that can make a
> difference. If there's 1MB of transactions, that can make things simpler.
>
> Not familiar with Sonicwall.
> I tend to turst the SQL Server native backup.
> Shipping basically means restoring the logs automatically to another server.
> It provides faster recovery time AND provides testing of the backup/restore
> process.
>
> That's really the way to do it since if your main server fails, you're going
> to need to use the second server anyway.
>
> That's pretty much application specific. But 'yes' is the basic answer.
>
>
|||"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:476126FC-F71F-42A8-A293-7FA7713B50D1@.microsoft.com...
> Guys, thanks for the great information. The CDP is a pretty nice
> real-time
> backup appliance. It does the job however it conflicts in a big way with
> the
> SQL native routines. I've been using its SQL agent because I didn't have
> backups and maintenance properly configured in SQL. Thanks to your help
> I'm
> that much closer and will probably use the CDP to backup the SQL natiive
> data
> and log backup files.
> One or two more questions, though. 1. - How do I know if my database is
> transactional? I think because it is written to regularly that it is. Am
> I
> right?
Yeah, basically if you're doing queries and updates, it's transactional.
If it's really only for reports and the like, it's more likely an OLAP.
(there's other guidelines, but that's the simple version :-)
> 2. How do I determine HOW transactional it is (1MB or 100MB)?
>
Monitor the size of the log and see how fast it grows.
> Now a better question (I hope). If log shipping happens extremely often -
> every 5 minutes what is the point of replication software like DoubleTake?
> Automatic failover, maybe? Or is it that the 5 minutes between log
> backups
> isn't lost if something happens?
>
Not famliar DoubleTake. But replication is "different". Generally used for
creating extra copies of data to scale out.
It can be part of a DR solution, but it's not a complete solution in and of
itself.
> Thanks for your continued help.
>
No problem.
> Po
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||DoubleTake is a drive level copy process...if a bit changes, that is
replicated. Slick stuff from the marketing materials
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:udR5OWAVIHA.4440@.TK2MSFTNGP06.phx.gbl...
> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> news:476126FC-F71F-42A8-A293-7FA7713B50D1@.microsoft.com...
> Yeah, basically if you're doing queries and updates, it's transactional.
> If it's really only for reports and the like, it's more likely an OLAP.
> (there's other guidelines, but that's the simple version :-)
>
> Monitor the size of the log and see how fast it grows.
>
> Not famliar DoubleTake. But replication is "different". Generally used
> for creating extra copies of data to scale out.
> It can be part of a DR solution, but it's not a complete solution in and
> of itself.
>
>
> No problem.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
Maintenance Routine - Best Practice
Please point me in the right direction. I am not a SQL DBA, nor am I very
experienced with SQL, nor does our budget allow for someone who is. I have
an experienced SQL pro available to me but believe I can handle the task at
hand with a little help.
I need to set up maintenance and backup routines for my SQL databases. I am
relatively familiar with Enterprise Manager and think I can see my way
through using a wizard to set up a proper maintenance plan. Problem is I
don't know what a proper plan is and I've heard that the wizards don't always
offer choices that fall in line with the best practices or pro's general
consensus on how things should be done.
I believe that part of the maintenance plan should be to truncate our logs .
. . but I don't know why we would do that given that the log files would be
used to recover the databases if something went wrong. I'm interested in
knowing if you'll share.
I am not opposed to reading and learning so please feel free to point me to
a recommended document or best practices article. Please bear in mind,
however, that I am not a DBA and (I am also very aware of our need for one)
as such the easier the reading the more helpful it will be.
Thanks for the help,
Po"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> Please point me in the right direction. I am not a SQL DBA, nor am I very
> experienced with SQL, nor does our budget allow for someone who is. I
> have
> an experienced SQL pro available to me but believe I can handle the task
> at
> hand with a little help.
> I need to set up maintenance and backup routines for my SQL databases. I
> am
> relatively familiar with Enterprise Manager and think I can see my way
> through using a wizard to set up a proper maintenance plan. Problem is I
> don't know what a proper plan is and I've heard that the wizards don't
> always
> offer choices that fall in line with the best practices or pro's general
> consensus on how things should be done.
If it's "good enough" you can do a lot of this with Enterprise manager.
Typically you'll do a FULL backup every "X days" (I tend to do once a night,
my old company we did every other night, etc. Depends a lot on different
factors).
Then do a TRANSACTION backup every "Y minutes" (it could be 60, 120, 5,
etc.)
The transaction log will "truncate" the log. Note that it will NOT shrink
the physical log file nor do you normally want to.
Typically figure "how long can I be down and how much data can I afford to
lose?"
When you recover from a failure, you'll restore the FULL backup first with
NORECOVERY and then EACH AND EVERY transaction log since that backup. This
is where those various factors come into play. For example, if you're doing
a transaction log backup every 5 minutes, you probably don't want to do a
FULL backup once a week as the worse case recovery scenario can be nasty.
(btw, you can do incremental backups betwene full ones which would help
there, but that starts to get complicated.)
Once you've got your backup scheme in place... TEST IT! I can't tell you
the number of times that I've seen people do these beautiful backup
schemes... only to have them fail when they need them. And once you test
it, practice it. Nothing is worse than in the heat of an emergency to
forget the critical steps required (e.g. forgetting to add NORECOVERY when
restoring the 50 gig DB :-)
As for other items, others can give advice on rebuilding indexes, etc.
(Though I often tend to a simple job of doing a 10% update on stats on all
tables if nothing else.)
> I believe that part of the maintenance plan should be to truncate our logs
> .
> . . but I don't know why we would do that given that the log files would
> be
> used to recover the databases if something went wrong. I'm interested in
> knowing if you'll share.
> I am not opposed to reading and learning so please feel free to point me
> to
> a recommended document or best practices article. Please bear in mind,
> however, that I am not a DBA and (I am also very aware of our need for
> one)
> as such the easier the reading the more helpful it will be.
> Thanks for the help,
> Po
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Backing up the log truncates in most situations. If the database is small
and it is all you have to manage, I would just create 3 separate maint
plans.
Backups:
Nightly full's
Hourly tlogs
Indexes\Stats
Weekly or nighly depending on the impact you can handle
Checkdb
Weekly
The idea is to keep it simple but depending on your hardware, you may have
to use a custom solution once you get to 10-50GB.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> Please point me in the right direction. I am not a SQL DBA, nor am I very
> experienced with SQL, nor does our budget allow for someone who is. I
> have
> an experienced SQL pro available to me but believe I can handle the task
> at
> hand with a little help.
> I need to set up maintenance and backup routines for my SQL databases. I
> am
> relatively familiar with Enterprise Manager and think I can see my way
> through using a wizard to set up a proper maintenance plan. Problem is I
> don't know what a proper plan is and I've heard that the wizards don't
> always
> offer choices that fall in line with the best practices or pro's general
> consensus on how things should be done.
> I believe that part of the maintenance plan should be to truncate our logs
> .
> . . but I don't know why we would do that given that the log files would
> be
> used to recover the databases if something went wrong. I'm interested in
> knowing if you'll share.
> I am not opposed to reading and learning so please feel free to point me
> to
> a recommended document or best practices article. Please bear in mind,
> however, that I am not a DBA and (I am also very aware of our need for
> one)
> as such the easier the reading the more helpful it will be.
> Thanks for the help,
> Po|||Pretty standard backup:
large databases:
Full database - weekly
Differentials - daily
T-logs - daily every 15 - 60 minutes
Small databases:
Full - daily
T-logs - daily every hour
Let the MP "Integrity check" option run once a week
Let the "Optimizations" part run once a week for small databases, more often
if they are large or high transaction (this is the index rebuilding part,
which is important for performance)
These are very generic guidelines.
3 seperate plans:
1 for system databases only
1 for databases in full recovery model
1 for databases in Simple recovery model
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> Please point me in the right direction. I am not a SQL DBA, nor am I very
> experienced with SQL, nor does our budget allow for someone who is. I
> have
> an experienced SQL pro available to me but believe I can handle the task
> at
> hand with a little help.
> I need to set up maintenance and backup routines for my SQL databases. I
> am
> relatively familiar with Enterprise Manager and think I can see my way
> through using a wizard to set up a proper maintenance plan. Problem is I
> don't know what a proper plan is and I've heard that the wizards don't
> always
> offer choices that fall in line with the best practices or pro's general
> consensus on how things should be done.
> I believe that part of the maintenance plan should be to truncate our logs
> .
> . . but I don't know why we would do that given that the log files would
> be
> used to recover the databases if something went wrong. I'm interested in
> knowing if you'll share.
> I am not opposed to reading and learning so please feel free to point me
> to
> a recommended document or best practices article. Please bear in mind,
> however, that I am not a DBA and (I am also very aware of our need for
> one)
> as such the easier the reading the more helpful it will be.
> Thanks for the help,
> Po|||Thank you everyone for the help. I noticed that all the posts referred in
one way or another to the size of the databases. I right-clicked on my
biggest database - it's about 57GB. How does that affect the advice given?
Please accept my apologies for not including the above information in the
first place. Also the server is a newer Dell 2950
2 Dual Core Xeon Processors (2.67GHz)
4GB RAM
RAID 1 OS Partition
RAID 5 Data Partition
We use the SonicWall CDP appliance for backup - so maintaining the database
is my bigger concern. Although I do plan to use the SQL native backup just
to be extra safe. I'm mentioning this because we don't have a 3rd partition
for log files (CDP handles SQL backup and restoration differently than SQL's
native backup I'm told) nor do I ship the log files - but that might only be
because I don't know what that means.
Testing in our environment is difficult as we are a 24hr company and taking
down this server costs us the use of our main application. I suppose I could
use a spare server to restore the database but I'm not sure of the best way
to confirm that the restoration was successful and the data is most up to
date. I'm not a regular user of the application so I wonder is there a way
to query the database to get the latest information written to it?
Sorry I'm growing this thread - I very much appreciate the help though.
Po
"Kevin3NF" wrote:
> Pretty standard backup:
> large databases:
> Full database - weekly
> Differentials - daily
> T-logs - daily every 15 - 60 minutes
> Small databases:
> Full - daily
> T-logs - daily every hour
> Let the MP "Integrity check" option run once a week
> Let the "Optimizations" part run once a week for small databases, more often
> if they are large or high transaction (this is the index rebuilding part,
> which is important for performance)
> These are very generic guidelines.
> 3 seperate plans:
> 1 for system databases only
> 1 for databases in full recovery model
> 1 for databases in Simple recovery model
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> > Please point me in the right direction. I am not a SQL DBA, nor am I very
> > experienced with SQL, nor does our budget allow for someone who is. I
> > have
> > an experienced SQL pro available to me but believe I can handle the task
> > at
> > hand with a little help.
> >
> > I need to set up maintenance and backup routines for my SQL databases. I
> > am
> > relatively familiar with Enterprise Manager and think I can see my way
> > through using a wizard to set up a proper maintenance plan. Problem is I
> > don't know what a proper plan is and I've heard that the wizards don't
> > always
> > offer choices that fall in line with the best practices or pro's general
> > consensus on how things should be done.
> >
> > I believe that part of the maintenance plan should be to truncate our logs
> > .
> > . . but I don't know why we would do that given that the log files would
> > be
> > used to recover the databases if something went wrong. I'm interested in
> > knowing if you'll share.
> >
> > I am not opposed to reading and learning so please feel free to point me
> > to
> > a recommended document or best practices article. Please bear in mind,
> > however, that I am not a DBA and (I am also very aware of our need for
> > one)
> > as such the easier the reading the more helpful it will be.
> >
> > Thanks for the help,
> >
> > Po
>
>|||No idea what a Sonic Wall CDP is, but if its doing SQL Server
backups/restores for you, your supportability by Microsoft and those of us
that know SQL pretty well might be more limited by that fact. Advice I give
everyone...Use SQL Native backup or a WELL-KNOWN 3rd party utility such as
Litespeed to do the backups, then move/copy/archive the resulting backup
files.
57GB is a good sized database. If it is transactional, I would almost
certainly go with the Weekly full, daily Differential, hourly T-log method.
I don't think a maintenance plan offers Differentials as part of the wizard,
so you may have to create a new job with a Backup command in it:
BACKUP DATABASE MyNwind
TO MyNwind_1
WITH DIFFERENTIAL
GO
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:2562F745-F3B4-493A-AE4E-2359F245E017@.microsoft.com...
> Thank you everyone for the help. I noticed that all the posts referred in
> one way or another to the size of the databases. I right-clicked on my
> biggest database - it's about 57GB. How does that affect the advice
> given?
> Please accept my apologies for not including the above information in the
> first place. Also the server is a newer Dell 2950
> 2 Dual Core Xeon Processors (2.67GHz)
> 4GB RAM
> RAID 1 OS Partition
> RAID 5 Data Partition
> We use the SonicWall CDP appliance for backup - so maintaining the
> database
> is my bigger concern. Although I do plan to use the SQL native backup
> just
> to be extra safe. I'm mentioning this because we don't have a 3rd
> partition
> for log files (CDP handles SQL backup and restoration differently than
> SQL's
> native backup I'm told) nor do I ship the log files - but that might only
> be
> because I don't know what that means.
> Testing in our environment is difficult as we are a 24hr company and
> taking
> down this server costs us the use of our main application. I suppose I
> could
> use a spare server to restore the database but I'm not sure of the best
> way
> to confirm that the restoration was successful and the data is most up to
> date. I'm not a regular user of the application so I wonder is there a
> way
> to query the database to get the latest information written to it?
> Sorry I'm growing this thread - I very much appreciate the help though.
> Po
> "Kevin3NF" wrote:
>> Pretty standard backup:
>> large databases:
>> Full database - weekly
>> Differentials - daily
>> T-logs - daily every 15 - 60 minutes
>> Small databases:
>> Full - daily
>> T-logs - daily every hour
>> Let the MP "Integrity check" option run once a week
>> Let the "Optimizations" part run once a week for small databases, more
>> often
>> if they are large or high transaction (this is the index rebuilding part,
>> which is important for performance)
>> These are very generic guidelines.
>> 3 seperate plans:
>> 1 for system databases only
>> 1 for databases in full recovery model
>> 1 for databases in Simple recovery model
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
>> news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
>> > Please point me in the right direction. I am not a SQL DBA, nor am I
>> > very
>> > experienced with SQL, nor does our budget allow for someone who is. I
>> > have
>> > an experienced SQL pro available to me but believe I can handle the
>> > task
>> > at
>> > hand with a little help.
>> >
>> > I need to set up maintenance and backup routines for my SQL databases.
>> > I
>> > am
>> > relatively familiar with Enterprise Manager and think I can see my way
>> > through using a wizard to set up a proper maintenance plan. Problem is
>> > I
>> > don't know what a proper plan is and I've heard that the wizards don't
>> > always
>> > offer choices that fall in line with the best practices or pro's
>> > general
>> > consensus on how things should be done.
>> >
>> > I believe that part of the maintenance plan should be to truncate our
>> > logs
>> > .
>> > . . but I don't know why we would do that given that the log files
>> > would
>> > be
>> > used to recover the databases if something went wrong. I'm interested
>> > in
>> > knowing if you'll share.
>> >
>> > I am not opposed to reading and learning so please feel free to point
>> > me
>> > to
>> > a recommended document or best practices article. Please bear in mind,
>> > however, that I am not a DBA and (I am also very aware of our need for
>> > one)
>> > as such the easier the reading the more helpful it will be.
>> >
>> > Thanks for the help,
>> >
>> > Po
>>|||"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:2562F745-F3B4-493A-AE4E-2359F245E017@.microsoft.com...
> Thank you everyone for the help. I noticed that all the posts referred in
> one way or another to the size of the databases. I right-clicked on my
> biggest database - it's about 57GB. How does that affect the advice
> given?
>
Size only partly comes into play.
The factor is related to how long it takes to backup and restore. If you
have slow disks and it takes 8 hours to restore, you've got problems.
And how much the DB changes.
If it's a 57GB DB but there's 100GB of transactions a day, that can make a
difference. If there's 1MB of transactions, that can make things simpler.
> We use the SonicWall CDP appliance for backup - so maintaining the
> database
> is my bigger concern. Although I do plan to use the SQL native backup
> just
> to be extra safe. I'm mentioning this because we don't have a 3rd
> partition
> for log files (CDP handles SQL backup and restoration differently than
> SQL's
> native backup I'm told) nor do I ship the log files - but that might only
> be
> because I don't know what that means.
>
Not familiar with Sonicwall.
I tend to turst the SQL Server native backup.
Shipping basically means restoring the logs automatically to another server.
It provides faster recovery time AND provides testing of the backup/restore
process.
> Testing in our environment is difficult as we are a 24hr company and
> taking
> down this server costs us the use of our main application. I suppose I
> could
> use a spare server to restore the database but I'm not sure of the best
> way
> to confirm that the restoration was successful and the data is most up to
> date.
That's really the way to do it since if your main server fails, you're going
to need to use the second server anyway.
> I'm not a regular user of the application so I wonder is there a way
> to query the database to get the latest information written to it?
>
That's pretty much application specific. But 'yes' is the basic answer.
> Sorry I'm growing this thread - I very much appreciate the help though.
> Po
> "Kevin3NF" wrote:
>> Pretty standard backup:
>> large databases:
>> Full database - weekly
>> Differentials - daily
>> T-logs - daily every 15 - 60 minutes
>> Small databases:
>> Full - daily
>> T-logs - daily every hour
>> Let the MP "Integrity check" option run once a week
>> Let the "Optimizations" part run once a week for small databases, more
>> often
>> if they are large or high transaction (this is the index rebuilding part,
>> which is important for performance)
>> These are very generic guidelines.
>> 3 seperate plans:
>> 1 for system databases only
>> 1 for databases in full recovery model
>> 1 for databases in Simple recovery model
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
>> news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
>> > Please point me in the right direction. I am not a SQL DBA, nor am I
>> > very
>> > experienced with SQL, nor does our budget allow for someone who is. I
>> > have
>> > an experienced SQL pro available to me but believe I can handle the
>> > task
>> > at
>> > hand with a little help.
>> >
>> > I need to set up maintenance and backup routines for my SQL databases.
>> > I
>> > am
>> > relatively familiar with Enterprise Manager and think I can see my way
>> > through using a wizard to set up a proper maintenance plan. Problem is
>> > I
>> > don't know what a proper plan is and I've heard that the wizards don't
>> > always
>> > offer choices that fall in line with the best practices or pro's
>> > general
>> > consensus on how things should be done.
>> >
>> > I believe that part of the maintenance plan should be to truncate our
>> > logs
>> > .
>> > . . but I don't know why we would do that given that the log files
>> > would
>> > be
>> > used to recover the databases if something went wrong. I'm interested
>> > in
>> > knowing if you'll share.
>> >
>> > I am not opposed to reading and learning so please feel free to point
>> > me
>> > to
>> > a recommended document or best practices article. Please bear in mind,
>> > however, that I am not a DBA and (I am also very aware of our need for
>> > one)
>> > as such the easier the reading the more helpful it will be.
>> >
>> > Thanks for the help,
>> >
>> > Po
>>|||Guys, thanks for the great information. The CDP is a pretty nice real-time
backup appliance. It does the job however it conflicts in a big way with the
SQL native routines. I've been using its SQL agent because I didn't have
backups and maintenance properly configured in SQL. Thanks to your help I'm
that much closer and will probably use the CDP to backup the SQL natiive data
and log backup files.
One or two more questions, though. 1. - How do I know if my database is
transactional? I think because it is written to regularly that it is. Am I
right?
2. How do I determine HOW transactional it is (1MB or 100MB)?
Now a better question (I hope). If log shipping happens extremely often -
every 5 minutes what is the point of replication software like DoubleTake?
Automatic failover, maybe? Or is it that the 5 minutes between log backups
isn't lost if something happens?
Thanks for your continued help.
Po
"Greg D. Moore (Strider)" wrote:
> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> news:2562F745-F3B4-493A-AE4E-2359F245E017@.microsoft.com...
> > Thank you everyone for the help. I noticed that all the posts referred in
> > one way or another to the size of the databases. I right-clicked on my
> > biggest database - it's about 57GB. How does that affect the advice
> > given?
> >
> Size only partly comes into play.
> The factor is related to how long it takes to backup and restore. If you
> have slow disks and it takes 8 hours to restore, you've got problems.
> And how much the DB changes.
> If it's a 57GB DB but there's 100GB of transactions a day, that can make a
> difference. If there's 1MB of transactions, that can make things simpler.
> > We use the SonicWall CDP appliance for backup - so maintaining the
> > database
> > is my bigger concern. Although I do plan to use the SQL native backup
> > just
> > to be extra safe. I'm mentioning this because we don't have a 3rd
> > partition
> > for log files (CDP handles SQL backup and restoration differently than
> > SQL's
> > native backup I'm told) nor do I ship the log files - but that might only
> > be
> > because I don't know what that means.
> >
> Not familiar with Sonicwall.
> I tend to turst the SQL Server native backup.
> Shipping basically means restoring the logs automatically to another server.
> It provides faster recovery time AND provides testing of the backup/restore
> process.
> > Testing in our environment is difficult as we are a 24hr company and
> > taking
> > down this server costs us the use of our main application. I suppose I
> > could
> > use a spare server to restore the database but I'm not sure of the best
> > way
> > to confirm that the restoration was successful and the data is most up to
> > date.
> That's really the way to do it since if your main server fails, you're going
> to need to use the second server anyway.
>
> > I'm not a regular user of the application so I wonder is there a way
> > to query the database to get the latest information written to it?
> >
> That's pretty much application specific. But 'yes' is the basic answer.
> > Sorry I'm growing this thread - I very much appreciate the help though.
> >
> > Po
> >
> > "Kevin3NF" wrote:
> >
> >> Pretty standard backup:
> >>
> >> large databases:
> >> Full database - weekly
> >> Differentials - daily
> >> T-logs - daily every 15 - 60 minutes
> >>
> >> Small databases:
> >> Full - daily
> >> T-logs - daily every hour
> >>
> >> Let the MP "Integrity check" option run once a week
> >>
> >> Let the "Optimizations" part run once a week for small databases, more
> >> often
> >> if they are large or high transaction (this is the index rebuilding part,
> >> which is important for performance)
> >>
> >> These are very generic guidelines.
> >>
> >> 3 seperate plans:
> >> 1 for system databases only
> >> 1 for databases in full recovery model
> >> 1 for databases in Simple recovery model
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> >> news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> >> > Please point me in the right direction. I am not a SQL DBA, nor am I
> >> > very
> >> > experienced with SQL, nor does our budget allow for someone who is. I
> >> > have
> >> > an experienced SQL pro available to me but believe I can handle the
> >> > task
> >> > at
> >> > hand with a little help.
> >> >
> >> > I need to set up maintenance and backup routines for my SQL databases.
> >> > I
> >> > am
> >> > relatively familiar with Enterprise Manager and think I can see my way
> >> > through using a wizard to set up a proper maintenance plan. Problem is
> >> > I
> >> > don't know what a proper plan is and I've heard that the wizards don't
> >> > always
> >> > offer choices that fall in line with the best practices or pro's
> >> > general
> >> > consensus on how things should be done.
> >> >
> >> > I believe that part of the maintenance plan should be to truncate our
> >> > logs
> >> > .
> >> > . . but I don't know why we would do that given that the log files
> >> > would
> >> > be
> >> > used to recover the databases if something went wrong. I'm interested
> >> > in
> >> > knowing if you'll share.
> >> >
> >> > I am not opposed to reading and learning so please feel free to point
> >> > me
> >> > to
> >> > a recommended document or best practices article. Please bear in mind,
> >> > however, that I am not a DBA and (I am also very aware of our need for
> >> > one)
> >> > as such the easier the reading the more helpful it will be.
> >> >
> >> > Thanks for the help,
> >> >
> >> > Po
> >>
> >>
> >>
>
>|||"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:476126FC-F71F-42A8-A293-7FA7713B50D1@.microsoft.com...
> Guys, thanks for the great information. The CDP is a pretty nice
> real-time
> backup appliance. It does the job however it conflicts in a big way with
> the
> SQL native routines. I've been using its SQL agent because I didn't have
> backups and maintenance properly configured in SQL. Thanks to your help
> I'm
> that much closer and will probably use the CDP to backup the SQL natiive
> data
> and log backup files.
> One or two more questions, though. 1. - How do I know if my database is
> transactional? I think because it is written to regularly that it is. Am
> I
> right?
Yeah, basically if you're doing queries and updates, it's transactional.
If it's really only for reports and the like, it's more likely an OLAP.
(there's other guidelines, but that's the simple version :-)
> 2. How do I determine HOW transactional it is (1MB or 100MB)?
>
Monitor the size of the log and see how fast it grows.
> Now a better question (I hope). If log shipping happens extremely often -
> every 5 minutes what is the point of replication software like DoubleTake?
> Automatic failover, maybe? Or is it that the 5 minutes between log
> backups
> isn't lost if something happens?
>
Not famliar DoubleTake. But replication is "different". Generally used for
creating extra copies of data to scale out.
It can be part of a DR solution, but it's not a complete solution in and of
itself.
> Thanks for your continued help.
>
No problem.
> Po
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||DoubleTake is a drive level copy process...if a bit changes, that is
replicated. Slick stuff from the marketing materials
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:udR5OWAVIHA.4440@.TK2MSFTNGP06.phx.gbl...
> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> news:476126FC-F71F-42A8-A293-7FA7713B50D1@.microsoft.com...
>> Guys, thanks for the great information. The CDP is a pretty nice
>> real-time
>> backup appliance. It does the job however it conflicts in a big way with
>> the
>> SQL native routines. I've been using its SQL agent because I didn't have
>> backups and maintenance properly configured in SQL. Thanks to your help
>> I'm
>> that much closer and will probably use the CDP to backup the SQL natiive
>> data
>> and log backup files.
>> One or two more questions, though. 1. - How do I know if my database is
>> transactional? I think because it is written to regularly that it is.
>> Am I
>> right?
> Yeah, basically if you're doing queries and updates, it's transactional.
> If it's really only for reports and the like, it's more likely an OLAP.
> (there's other guidelines, but that's the simple version :-)
>
>> 2. How do I determine HOW transactional it is (1MB or 100MB)?
> Monitor the size of the log and see how fast it grows.
>> Now a better question (I hope). If log shipping happens extremely
>> often -
>> every 5 minutes what is the point of replication software like
>> DoubleTake?
>> Automatic failover, maybe? Or is it that the 5 minutes between log
>> backups
>> isn't lost if something happens?
> Not famliar DoubleTake. But replication is "different". Generally used
> for creating extra copies of data to scale out.
> It can be part of a DR solution, but it's not a complete solution in and
> of itself.
>
>> Thanks for your continued help.
> No problem.
>
>> Po
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||Ah, that's right. I have seen that.
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:%233o7iXFVIHA.2368@.TK2MSFTNGP05.phx.gbl...
> DoubleTake is a drive level copy process...if a bit changes, that is
> replicated. Slick stuff from the marketing materials
> --
> Kevin3NF
> SQL Server dude
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
experienced with SQL, nor does our budget allow for someone who is. I have
an experienced SQL pro available to me but believe I can handle the task at
hand with a little help.
I need to set up maintenance and backup routines for my SQL databases. I am
relatively familiar with Enterprise Manager and think I can see my way
through using a wizard to set up a proper maintenance plan. Problem is I
don't know what a proper plan is and I've heard that the wizards don't always
offer choices that fall in line with the best practices or pro's general
consensus on how things should be done.
I believe that part of the maintenance plan should be to truncate our logs .
. . but I don't know why we would do that given that the log files would be
used to recover the databases if something went wrong. I'm interested in
knowing if you'll share.
I am not opposed to reading and learning so please feel free to point me to
a recommended document or best practices article. Please bear in mind,
however, that I am not a DBA and (I am also very aware of our need for one)
as such the easier the reading the more helpful it will be.
Thanks for the help,
Po"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> Please point me in the right direction. I am not a SQL DBA, nor am I very
> experienced with SQL, nor does our budget allow for someone who is. I
> have
> an experienced SQL pro available to me but believe I can handle the task
> at
> hand with a little help.
> I need to set up maintenance and backup routines for my SQL databases. I
> am
> relatively familiar with Enterprise Manager and think I can see my way
> through using a wizard to set up a proper maintenance plan. Problem is I
> don't know what a proper plan is and I've heard that the wizards don't
> always
> offer choices that fall in line with the best practices or pro's general
> consensus on how things should be done.
If it's "good enough" you can do a lot of this with Enterprise manager.
Typically you'll do a FULL backup every "X days" (I tend to do once a night,
my old company we did every other night, etc. Depends a lot on different
factors).
Then do a TRANSACTION backup every "Y minutes" (it could be 60, 120, 5,
etc.)
The transaction log will "truncate" the log. Note that it will NOT shrink
the physical log file nor do you normally want to.
Typically figure "how long can I be down and how much data can I afford to
lose?"
When you recover from a failure, you'll restore the FULL backup first with
NORECOVERY and then EACH AND EVERY transaction log since that backup. This
is where those various factors come into play. For example, if you're doing
a transaction log backup every 5 minutes, you probably don't want to do a
FULL backup once a week as the worse case recovery scenario can be nasty.
(btw, you can do incremental backups betwene full ones which would help
there, but that starts to get complicated.)
Once you've got your backup scheme in place... TEST IT! I can't tell you
the number of times that I've seen people do these beautiful backup
schemes... only to have them fail when they need them. And once you test
it, practice it. Nothing is worse than in the heat of an emergency to
forget the critical steps required (e.g. forgetting to add NORECOVERY when
restoring the 50 gig DB :-)
As for other items, others can give advice on rebuilding indexes, etc.
(Though I often tend to a simple job of doing a 10% update on stats on all
tables if nothing else.)
> I believe that part of the maintenance plan should be to truncate our logs
> .
> . . but I don't know why we would do that given that the log files would
> be
> used to recover the databases if something went wrong. I'm interested in
> knowing if you'll share.
> I am not opposed to reading and learning so please feel free to point me
> to
> a recommended document or best practices article. Please bear in mind,
> however, that I am not a DBA and (I am also very aware of our need for
> one)
> as such the easier the reading the more helpful it will be.
> Thanks for the help,
> Po
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Backing up the log truncates in most situations. If the database is small
and it is all you have to manage, I would just create 3 separate maint
plans.
Backups:
Nightly full's
Hourly tlogs
Indexes\Stats
Weekly or nighly depending on the impact you can handle
Checkdb
Weekly
The idea is to keep it simple but depending on your hardware, you may have
to use a custom solution once you get to 10-50GB.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> Please point me in the right direction. I am not a SQL DBA, nor am I very
> experienced with SQL, nor does our budget allow for someone who is. I
> have
> an experienced SQL pro available to me but believe I can handle the task
> at
> hand with a little help.
> I need to set up maintenance and backup routines for my SQL databases. I
> am
> relatively familiar with Enterprise Manager and think I can see my way
> through using a wizard to set up a proper maintenance plan. Problem is I
> don't know what a proper plan is and I've heard that the wizards don't
> always
> offer choices that fall in line with the best practices or pro's general
> consensus on how things should be done.
> I believe that part of the maintenance plan should be to truncate our logs
> .
> . . but I don't know why we would do that given that the log files would
> be
> used to recover the databases if something went wrong. I'm interested in
> knowing if you'll share.
> I am not opposed to reading and learning so please feel free to point me
> to
> a recommended document or best practices article. Please bear in mind,
> however, that I am not a DBA and (I am also very aware of our need for
> one)
> as such the easier the reading the more helpful it will be.
> Thanks for the help,
> Po|||Pretty standard backup:
large databases:
Full database - weekly
Differentials - daily
T-logs - daily every 15 - 60 minutes
Small databases:
Full - daily
T-logs - daily every hour
Let the MP "Integrity check" option run once a week
Let the "Optimizations" part run once a week for small databases, more often
if they are large or high transaction (this is the index rebuilding part,
which is important for performance)
These are very generic guidelines.
3 seperate plans:
1 for system databases only
1 for databases in full recovery model
1 for databases in Simple recovery model
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> Please point me in the right direction. I am not a SQL DBA, nor am I very
> experienced with SQL, nor does our budget allow for someone who is. I
> have
> an experienced SQL pro available to me but believe I can handle the task
> at
> hand with a little help.
> I need to set up maintenance and backup routines for my SQL databases. I
> am
> relatively familiar with Enterprise Manager and think I can see my way
> through using a wizard to set up a proper maintenance plan. Problem is I
> don't know what a proper plan is and I've heard that the wizards don't
> always
> offer choices that fall in line with the best practices or pro's general
> consensus on how things should be done.
> I believe that part of the maintenance plan should be to truncate our logs
> .
> . . but I don't know why we would do that given that the log files would
> be
> used to recover the databases if something went wrong. I'm interested in
> knowing if you'll share.
> I am not opposed to reading and learning so please feel free to point me
> to
> a recommended document or best practices article. Please bear in mind,
> however, that I am not a DBA and (I am also very aware of our need for
> one)
> as such the easier the reading the more helpful it will be.
> Thanks for the help,
> Po|||Thank you everyone for the help. I noticed that all the posts referred in
one way or another to the size of the databases. I right-clicked on my
biggest database - it's about 57GB. How does that affect the advice given?
Please accept my apologies for not including the above information in the
first place. Also the server is a newer Dell 2950
2 Dual Core Xeon Processors (2.67GHz)
4GB RAM
RAID 1 OS Partition
RAID 5 Data Partition
We use the SonicWall CDP appliance for backup - so maintaining the database
is my bigger concern. Although I do plan to use the SQL native backup just
to be extra safe. I'm mentioning this because we don't have a 3rd partition
for log files (CDP handles SQL backup and restoration differently than SQL's
native backup I'm told) nor do I ship the log files - but that might only be
because I don't know what that means.
Testing in our environment is difficult as we are a 24hr company and taking
down this server costs us the use of our main application. I suppose I could
use a spare server to restore the database but I'm not sure of the best way
to confirm that the restoration was successful and the data is most up to
date. I'm not a regular user of the application so I wonder is there a way
to query the database to get the latest information written to it?
Sorry I'm growing this thread - I very much appreciate the help though.
Po
"Kevin3NF" wrote:
> Pretty standard backup:
> large databases:
> Full database - weekly
> Differentials - daily
> T-logs - daily every 15 - 60 minutes
> Small databases:
> Full - daily
> T-logs - daily every hour
> Let the MP "Integrity check" option run once a week
> Let the "Optimizations" part run once a week for small databases, more often
> if they are large or high transaction (this is the index rebuilding part,
> which is important for performance)
> These are very generic guidelines.
> 3 seperate plans:
> 1 for system databases only
> 1 for databases in full recovery model
> 1 for databases in Simple recovery model
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> > Please point me in the right direction. I am not a SQL DBA, nor am I very
> > experienced with SQL, nor does our budget allow for someone who is. I
> > have
> > an experienced SQL pro available to me but believe I can handle the task
> > at
> > hand with a little help.
> >
> > I need to set up maintenance and backup routines for my SQL databases. I
> > am
> > relatively familiar with Enterprise Manager and think I can see my way
> > through using a wizard to set up a proper maintenance plan. Problem is I
> > don't know what a proper plan is and I've heard that the wizards don't
> > always
> > offer choices that fall in line with the best practices or pro's general
> > consensus on how things should be done.
> >
> > I believe that part of the maintenance plan should be to truncate our logs
> > .
> > . . but I don't know why we would do that given that the log files would
> > be
> > used to recover the databases if something went wrong. I'm interested in
> > knowing if you'll share.
> >
> > I am not opposed to reading and learning so please feel free to point me
> > to
> > a recommended document or best practices article. Please bear in mind,
> > however, that I am not a DBA and (I am also very aware of our need for
> > one)
> > as such the easier the reading the more helpful it will be.
> >
> > Thanks for the help,
> >
> > Po
>
>|||No idea what a Sonic Wall CDP is, but if its doing SQL Server
backups/restores for you, your supportability by Microsoft and those of us
that know SQL pretty well might be more limited by that fact. Advice I give
everyone...Use SQL Native backup or a WELL-KNOWN 3rd party utility such as
Litespeed to do the backups, then move/copy/archive the resulting backup
files.
57GB is a good sized database. If it is transactional, I would almost
certainly go with the Weekly full, daily Differential, hourly T-log method.
I don't think a maintenance plan offers Differentials as part of the wizard,
so you may have to create a new job with a Backup command in it:
BACKUP DATABASE MyNwind
TO MyNwind_1
WITH DIFFERENTIAL
GO
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:2562F745-F3B4-493A-AE4E-2359F245E017@.microsoft.com...
> Thank you everyone for the help. I noticed that all the posts referred in
> one way or another to the size of the databases. I right-clicked on my
> biggest database - it's about 57GB. How does that affect the advice
> given?
> Please accept my apologies for not including the above information in the
> first place. Also the server is a newer Dell 2950
> 2 Dual Core Xeon Processors (2.67GHz)
> 4GB RAM
> RAID 1 OS Partition
> RAID 5 Data Partition
> We use the SonicWall CDP appliance for backup - so maintaining the
> database
> is my bigger concern. Although I do plan to use the SQL native backup
> just
> to be extra safe. I'm mentioning this because we don't have a 3rd
> partition
> for log files (CDP handles SQL backup and restoration differently than
> SQL's
> native backup I'm told) nor do I ship the log files - but that might only
> be
> because I don't know what that means.
> Testing in our environment is difficult as we are a 24hr company and
> taking
> down this server costs us the use of our main application. I suppose I
> could
> use a spare server to restore the database but I'm not sure of the best
> way
> to confirm that the restoration was successful and the data is most up to
> date. I'm not a regular user of the application so I wonder is there a
> way
> to query the database to get the latest information written to it?
> Sorry I'm growing this thread - I very much appreciate the help though.
> Po
> "Kevin3NF" wrote:
>> Pretty standard backup:
>> large databases:
>> Full database - weekly
>> Differentials - daily
>> T-logs - daily every 15 - 60 minutes
>> Small databases:
>> Full - daily
>> T-logs - daily every hour
>> Let the MP "Integrity check" option run once a week
>> Let the "Optimizations" part run once a week for small databases, more
>> often
>> if they are large or high transaction (this is the index rebuilding part,
>> which is important for performance)
>> These are very generic guidelines.
>> 3 seperate plans:
>> 1 for system databases only
>> 1 for databases in full recovery model
>> 1 for databases in Simple recovery model
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
>> news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
>> > Please point me in the right direction. I am not a SQL DBA, nor am I
>> > very
>> > experienced with SQL, nor does our budget allow for someone who is. I
>> > have
>> > an experienced SQL pro available to me but believe I can handle the
>> > task
>> > at
>> > hand with a little help.
>> >
>> > I need to set up maintenance and backup routines for my SQL databases.
>> > I
>> > am
>> > relatively familiar with Enterprise Manager and think I can see my way
>> > through using a wizard to set up a proper maintenance plan. Problem is
>> > I
>> > don't know what a proper plan is and I've heard that the wizards don't
>> > always
>> > offer choices that fall in line with the best practices or pro's
>> > general
>> > consensus on how things should be done.
>> >
>> > I believe that part of the maintenance plan should be to truncate our
>> > logs
>> > .
>> > . . but I don't know why we would do that given that the log files
>> > would
>> > be
>> > used to recover the databases if something went wrong. I'm interested
>> > in
>> > knowing if you'll share.
>> >
>> > I am not opposed to reading and learning so please feel free to point
>> > me
>> > to
>> > a recommended document or best practices article. Please bear in mind,
>> > however, that I am not a DBA and (I am also very aware of our need for
>> > one)
>> > as such the easier the reading the more helpful it will be.
>> >
>> > Thanks for the help,
>> >
>> > Po
>>|||"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:2562F745-F3B4-493A-AE4E-2359F245E017@.microsoft.com...
> Thank you everyone for the help. I noticed that all the posts referred in
> one way or another to the size of the databases. I right-clicked on my
> biggest database - it's about 57GB. How does that affect the advice
> given?
>
Size only partly comes into play.
The factor is related to how long it takes to backup and restore. If you
have slow disks and it takes 8 hours to restore, you've got problems.
And how much the DB changes.
If it's a 57GB DB but there's 100GB of transactions a day, that can make a
difference. If there's 1MB of transactions, that can make things simpler.
> We use the SonicWall CDP appliance for backup - so maintaining the
> database
> is my bigger concern. Although I do plan to use the SQL native backup
> just
> to be extra safe. I'm mentioning this because we don't have a 3rd
> partition
> for log files (CDP handles SQL backup and restoration differently than
> SQL's
> native backup I'm told) nor do I ship the log files - but that might only
> be
> because I don't know what that means.
>
Not familiar with Sonicwall.
I tend to turst the SQL Server native backup.
Shipping basically means restoring the logs automatically to another server.
It provides faster recovery time AND provides testing of the backup/restore
process.
> Testing in our environment is difficult as we are a 24hr company and
> taking
> down this server costs us the use of our main application. I suppose I
> could
> use a spare server to restore the database but I'm not sure of the best
> way
> to confirm that the restoration was successful and the data is most up to
> date.
That's really the way to do it since if your main server fails, you're going
to need to use the second server anyway.
> I'm not a regular user of the application so I wonder is there a way
> to query the database to get the latest information written to it?
>
That's pretty much application specific. But 'yes' is the basic answer.
> Sorry I'm growing this thread - I very much appreciate the help though.
> Po
> "Kevin3NF" wrote:
>> Pretty standard backup:
>> large databases:
>> Full database - weekly
>> Differentials - daily
>> T-logs - daily every 15 - 60 minutes
>> Small databases:
>> Full - daily
>> T-logs - daily every hour
>> Let the MP "Integrity check" option run once a week
>> Let the "Optimizations" part run once a week for small databases, more
>> often
>> if they are large or high transaction (this is the index rebuilding part,
>> which is important for performance)
>> These are very generic guidelines.
>> 3 seperate plans:
>> 1 for system databases only
>> 1 for databases in full recovery model
>> 1 for databases in Simple recovery model
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
>> news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
>> > Please point me in the right direction. I am not a SQL DBA, nor am I
>> > very
>> > experienced with SQL, nor does our budget allow for someone who is. I
>> > have
>> > an experienced SQL pro available to me but believe I can handle the
>> > task
>> > at
>> > hand with a little help.
>> >
>> > I need to set up maintenance and backup routines for my SQL databases.
>> > I
>> > am
>> > relatively familiar with Enterprise Manager and think I can see my way
>> > through using a wizard to set up a proper maintenance plan. Problem is
>> > I
>> > don't know what a proper plan is and I've heard that the wizards don't
>> > always
>> > offer choices that fall in line with the best practices or pro's
>> > general
>> > consensus on how things should be done.
>> >
>> > I believe that part of the maintenance plan should be to truncate our
>> > logs
>> > .
>> > . . but I don't know why we would do that given that the log files
>> > would
>> > be
>> > used to recover the databases if something went wrong. I'm interested
>> > in
>> > knowing if you'll share.
>> >
>> > I am not opposed to reading and learning so please feel free to point
>> > me
>> > to
>> > a recommended document or best practices article. Please bear in mind,
>> > however, that I am not a DBA and (I am also very aware of our need for
>> > one)
>> > as such the easier the reading the more helpful it will be.
>> >
>> > Thanks for the help,
>> >
>> > Po
>>|||Guys, thanks for the great information. The CDP is a pretty nice real-time
backup appliance. It does the job however it conflicts in a big way with the
SQL native routines. I've been using its SQL agent because I didn't have
backups and maintenance properly configured in SQL. Thanks to your help I'm
that much closer and will probably use the CDP to backup the SQL natiive data
and log backup files.
One or two more questions, though. 1. - How do I know if my database is
transactional? I think because it is written to regularly that it is. Am I
right?
2. How do I determine HOW transactional it is (1MB or 100MB)?
Now a better question (I hope). If log shipping happens extremely often -
every 5 minutes what is the point of replication software like DoubleTake?
Automatic failover, maybe? Or is it that the 5 minutes between log backups
isn't lost if something happens?
Thanks for your continued help.
Po
"Greg D. Moore (Strider)" wrote:
> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> news:2562F745-F3B4-493A-AE4E-2359F245E017@.microsoft.com...
> > Thank you everyone for the help. I noticed that all the posts referred in
> > one way or another to the size of the databases. I right-clicked on my
> > biggest database - it's about 57GB. How does that affect the advice
> > given?
> >
> Size only partly comes into play.
> The factor is related to how long it takes to backup and restore. If you
> have slow disks and it takes 8 hours to restore, you've got problems.
> And how much the DB changes.
> If it's a 57GB DB but there's 100GB of transactions a day, that can make a
> difference. If there's 1MB of transactions, that can make things simpler.
> > We use the SonicWall CDP appliance for backup - so maintaining the
> > database
> > is my bigger concern. Although I do plan to use the SQL native backup
> > just
> > to be extra safe. I'm mentioning this because we don't have a 3rd
> > partition
> > for log files (CDP handles SQL backup and restoration differently than
> > SQL's
> > native backup I'm told) nor do I ship the log files - but that might only
> > be
> > because I don't know what that means.
> >
> Not familiar with Sonicwall.
> I tend to turst the SQL Server native backup.
> Shipping basically means restoring the logs automatically to another server.
> It provides faster recovery time AND provides testing of the backup/restore
> process.
> > Testing in our environment is difficult as we are a 24hr company and
> > taking
> > down this server costs us the use of our main application. I suppose I
> > could
> > use a spare server to restore the database but I'm not sure of the best
> > way
> > to confirm that the restoration was successful and the data is most up to
> > date.
> That's really the way to do it since if your main server fails, you're going
> to need to use the second server anyway.
>
> > I'm not a regular user of the application so I wonder is there a way
> > to query the database to get the latest information written to it?
> >
> That's pretty much application specific. But 'yes' is the basic answer.
> > Sorry I'm growing this thread - I very much appreciate the help though.
> >
> > Po
> >
> > "Kevin3NF" wrote:
> >
> >> Pretty standard backup:
> >>
> >> large databases:
> >> Full database - weekly
> >> Differentials - daily
> >> T-logs - daily every 15 - 60 minutes
> >>
> >> Small databases:
> >> Full - daily
> >> T-logs - daily every hour
> >>
> >> Let the MP "Integrity check" option run once a week
> >>
> >> Let the "Optimizations" part run once a week for small databases, more
> >> often
> >> if they are large or high transaction (this is the index rebuilding part,
> >> which is important for performance)
> >>
> >> These are very generic guidelines.
> >>
> >> 3 seperate plans:
> >> 1 for system databases only
> >> 1 for databases in full recovery model
> >> 1 for databases in Simple recovery model
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> >> news:22308F88-1DEB-4170-A08A-79486A5C2E7C@.microsoft.com...
> >> > Please point me in the right direction. I am not a SQL DBA, nor am I
> >> > very
> >> > experienced with SQL, nor does our budget allow for someone who is. I
> >> > have
> >> > an experienced SQL pro available to me but believe I can handle the
> >> > task
> >> > at
> >> > hand with a little help.
> >> >
> >> > I need to set up maintenance and backup routines for my SQL databases.
> >> > I
> >> > am
> >> > relatively familiar with Enterprise Manager and think I can see my way
> >> > through using a wizard to set up a proper maintenance plan. Problem is
> >> > I
> >> > don't know what a proper plan is and I've heard that the wizards don't
> >> > always
> >> > offer choices that fall in line with the best practices or pro's
> >> > general
> >> > consensus on how things should be done.
> >> >
> >> > I believe that part of the maintenance plan should be to truncate our
> >> > logs
> >> > .
> >> > . . but I don't know why we would do that given that the log files
> >> > would
> >> > be
> >> > used to recover the databases if something went wrong. I'm interested
> >> > in
> >> > knowing if you'll share.
> >> >
> >> > I am not opposed to reading and learning so please feel free to point
> >> > me
> >> > to
> >> > a recommended document or best practices article. Please bear in mind,
> >> > however, that I am not a DBA and (I am also very aware of our need for
> >> > one)
> >> > as such the easier the reading the more helpful it will be.
> >> >
> >> > Thanks for the help,
> >> >
> >> > Po
> >>
> >>
> >>
>
>|||"powlaz" <powlaz@.discussions.microsoft.com> wrote in message
news:476126FC-F71F-42A8-A293-7FA7713B50D1@.microsoft.com...
> Guys, thanks for the great information. The CDP is a pretty nice
> real-time
> backup appliance. It does the job however it conflicts in a big way with
> the
> SQL native routines. I've been using its SQL agent because I didn't have
> backups and maintenance properly configured in SQL. Thanks to your help
> I'm
> that much closer and will probably use the CDP to backup the SQL natiive
> data
> and log backup files.
> One or two more questions, though. 1. - How do I know if my database is
> transactional? I think because it is written to regularly that it is. Am
> I
> right?
Yeah, basically if you're doing queries and updates, it's transactional.
If it's really only for reports and the like, it's more likely an OLAP.
(there's other guidelines, but that's the simple version :-)
> 2. How do I determine HOW transactional it is (1MB or 100MB)?
>
Monitor the size of the log and see how fast it grows.
> Now a better question (I hope). If log shipping happens extremely often -
> every 5 minutes what is the point of replication software like DoubleTake?
> Automatic failover, maybe? Or is it that the 5 minutes between log
> backups
> isn't lost if something happens?
>
Not famliar DoubleTake. But replication is "different". Generally used for
creating extra copies of data to scale out.
It can be part of a DR solution, but it's not a complete solution in and of
itself.
> Thanks for your continued help.
>
No problem.
> Po
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||DoubleTake is a drive level copy process...if a bit changes, that is
replicated. Slick stuff from the marketing materials
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:udR5OWAVIHA.4440@.TK2MSFTNGP06.phx.gbl...
> "powlaz" <powlaz@.discussions.microsoft.com> wrote in message
> news:476126FC-F71F-42A8-A293-7FA7713B50D1@.microsoft.com...
>> Guys, thanks for the great information. The CDP is a pretty nice
>> real-time
>> backup appliance. It does the job however it conflicts in a big way with
>> the
>> SQL native routines. I've been using its SQL agent because I didn't have
>> backups and maintenance properly configured in SQL. Thanks to your help
>> I'm
>> that much closer and will probably use the CDP to backup the SQL natiive
>> data
>> and log backup files.
>> One or two more questions, though. 1. - How do I know if my database is
>> transactional? I think because it is written to regularly that it is.
>> Am I
>> right?
> Yeah, basically if you're doing queries and updates, it's transactional.
> If it's really only for reports and the like, it's more likely an OLAP.
> (there's other guidelines, but that's the simple version :-)
>
>> 2. How do I determine HOW transactional it is (1MB or 100MB)?
> Monitor the size of the log and see how fast it grows.
>> Now a better question (I hope). If log shipping happens extremely
>> often -
>> every 5 minutes what is the point of replication software like
>> DoubleTake?
>> Automatic failover, maybe? Or is it that the 5 minutes between log
>> backups
>> isn't lost if something happens?
> Not famliar DoubleTake. But replication is "different". Generally used
> for creating extra copies of data to scale out.
> It can be part of a DR solution, but it's not a complete solution in and
> of itself.
>
>> Thanks for your continued help.
> No problem.
>
>> Po
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||Ah, that's right. I have seen that.
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:%233o7iXFVIHA.2368@.TK2MSFTNGP05.phx.gbl...
> DoubleTake is a drive level copy process...if a bit changes, that is
> replicated. Slick stuff from the marketing materials
> --
> Kevin3NF
> SQL Server dude
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Saturday, February 25, 2012
Maintenance Plans - Restrict users from deleting or editing
Hi,
Is it possible to restrict users that use Enterprise Manager from deleting
maintenance plans? Could anyone please point me to a good URL that talks
about this.
thanks a lot,1) Try placing strict security on the msdb tables related to jobs/plans?
2) Not sure if you can do this but possibly triggers on same tables to check
login and rollback if appropriate.
TheSQLGuru
President
Indicium Resources, Inc.
"Uday" <Uday@.discussions.microsoft.com> wrote in message
news:60A6EF35-0306-428E-993B-04A76B9484B8@.microsoft.com...
> Hi,
> Is it possible to restrict users that use Enterprise Manager from deleting
> maintenance plans? Could anyone please point me to a good URL that talks
> about this.
>
> thanks a lot,
>
Is it possible to restrict users that use Enterprise Manager from deleting
maintenance plans? Could anyone please point me to a good URL that talks
about this.
thanks a lot,1) Try placing strict security on the msdb tables related to jobs/plans?
2) Not sure if you can do this but possibly triggers on same tables to check
login and rollback if appropriate.
TheSQLGuru
President
Indicium Resources, Inc.
"Uday" <Uday@.discussions.microsoft.com> wrote in message
news:60A6EF35-0306-428E-993B-04A76B9484B8@.microsoft.com...
> Hi,
> Is it possible to restrict users that use Enterprise Manager from deleting
> maintenance plans? Could anyone please point me to a good URL that talks
> about this.
>
> thanks a lot,
>
Maintenance Plans - Restrict users from deleting or editing
Hi,
Is it possible to restrict users that use Enterprise Manager from deleting
maintenance plans? Could anyone please point me to a good URL that talks
about this.
thanks a lot,
1) Try placing strict security on the msdb tables related to jobs/plans?
2) Not sure if you can do this but possibly triggers on same tables to check
login and rollback if appropriate.
TheSQLGuru
President
Indicium Resources, Inc.
"Uday" <Uday@.discussions.microsoft.com> wrote in message
news:60A6EF35-0306-428E-993B-04A76B9484B8@.microsoft.com...
> Hi,
> Is it possible to restrict users that use Enterprise Manager from deleting
> maintenance plans? Could anyone please point me to a good URL that talks
> about this.
>
> thanks a lot,
>
Is it possible to restrict users that use Enterprise Manager from deleting
maintenance plans? Could anyone please point me to a good URL that talks
about this.
thanks a lot,
1) Try placing strict security on the msdb tables related to jobs/plans?
2) Not sure if you can do this but possibly triggers on same tables to check
login and rollback if appropriate.
TheSQLGuru
President
Indicium Resources, Inc.
"Uday" <Uday@.discussions.microsoft.com> wrote in message
news:60A6EF35-0306-428E-993B-04A76B9484B8@.microsoft.com...
> Hi,
> Is it possible to restrict users that use Enterprise Manager from deleting
> maintenance plans? Could anyone please point me to a good URL that talks
> about this.
>
> thanks a lot,
>
Maintenance Plans - Restrict users from deleting or editing
Hi,
Is it possible to restrict users that use Enterprise Manager from deleting
maintenance plans? Could anyone please point me to a good URL that talks
about this.
thanks a lot,1) Try placing strict security on the msdb tables related to jobs/plans?
2) Not sure if you can do this but possibly triggers on same tables to check
login and rollback if appropriate.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Uday" <Uday@.discussions.microsoft.com> wrote in message
news:60A6EF35-0306-428E-993B-04A76B9484B8@.microsoft.com...
> Hi,
> Is it possible to restrict users that use Enterprise Manager from deleting
> maintenance plans? Could anyone please point me to a good URL that talks
> about this.
>
> thanks a lot,
>
Is it possible to restrict users that use Enterprise Manager from deleting
maintenance plans? Could anyone please point me to a good URL that talks
about this.
thanks a lot,1) Try placing strict security on the msdb tables related to jobs/plans?
2) Not sure if you can do this but possibly triggers on same tables to check
login and rollback if appropriate.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Uday" <Uday@.discussions.microsoft.com> wrote in message
news:60A6EF35-0306-428E-993B-04A76B9484B8@.microsoft.com...
> Hi,
> Is it possible to restrict users that use Enterprise Manager from deleting
> maintenance plans? Could anyone please point me to a good URL that talks
> about this.
>
> thanks a lot,
>
Subscribe to:
Comments (Atom)