Reporting Services and FXCop

When I run FxCop on an ASP.NET app that has a reference to the reporting
services web reference, I get a very long list of 'errors' that are not in
my code, but in the web service. As there isn't anything that I can do about
these 'errors' is there a way to tell FxCop to ignore the reference?
--
Brendan Reynoldswell, in the target you can expand and exlude certain assemblies/namespaces.
It's funny though, FxCop doesn't automatically check my referenced dlls.
What version are you using? Are you simply adding the 1 assembly to fxcop?
Karl
--
MY ASP.Net tutorials
http://www.openmymind.net/
http://openmymind.net/redirector.aspx?documentId=51 - Learn about AJAX!
"Brendan Reynolds" <brenreyn@.discussions.microsoft.com> wrote in message
news:OzV9%239p7FHA.1032@.TK2MSFTNGP11.phx.gbl...
> When I run FxCop on an ASP.NET app that has a reference to the reporting
> services web reference, I get a very long list of 'errors' that are not in
> my code, but in the web service. As there isn't anything that I can do
> about these 'errors' is there a way to tell FxCop to ignore the reference?
> --
> Brendan Reynolds
>
>|||"Karl Seguin" <karl REMOVE @. REMOVE openmymind REMOVEMETOO . ANDME net>
wrote in message news:eVoqesq7FHA.3388@.TK2MSFTNGP11.phx.gbl...
> well, in the target you can expand and exlude certain
> assemblies/namespaces.
Ah! Got it, thanks! :-)
That solved the problem for me, but if you're still curious here are the
answers to your questions ...
> It's funny though, FxCop doesn't automatically check my referenced dlls.
Just the web reference, not other references.
> What version are you using?
1.32
> Are you simply adding the 1 assembly to fxcop?
Yes.
> "Brendan Reynolds" <brenreyn@.discussions.microsoft.com> wrote in message
> news:OzV9%239p7FHA.1032@.TK2MSFTNGP11.phx.gbl...
>> When I run FxCop on an ASP.NET app that has a reference to the reporting
>> services web reference, I get a very long list of 'errors' that are not
>> in my code, but in the web service. As there isn't anything that I can do
>> about these 'errors' is there a way to tell FxCop to ignore the
>> reference?
>> --
>> Brendan Reynolds
>>
>

Reporting Services and Analysis Services - help please - desperate!

Hi folks,
Please please can someone help, this problem has just been going on for too
long.We need to run reports off RS using AS as the data source.
Our setup is as follows
RS - on seperate web server (srvWeb)
RS DB - on seperate SQL server (srvDB)
No problem - everything has worked fine connecting directly to the databases
to extract data for the reports.
However, when I connect to Analysis services on the RS DB (srvDB) server I
get the following error
a.. An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
a.. Cannot create a connection to data source 'AS_Admin'.
(rsErrorOpeningConnection) Get Online Help
a.. Database 'BI_Admin' does not exist.
If I connect using localhost as the datasource name - it works 100% on my
machine but the minute I try to connect to a server (srvDB) that is not on
my machine I get the error.
We have installed Analysis Services on the web server (srvWeb) as well
because it didn't have the correct drivers but this has not helped at all -
I also registered the servers on AS (srvWeb) for what it's worth.
Does anyone please please know what I should do to get this working.
Thanks
KIf you're running RS on a Windows 2000 Server, and trying to use Stored
Windows Credentials to access a remote AS data source, this might help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rstshoot/htm/trs_tshootdev_v1_3dcz.asp
Troubleshooting Server and Database Problems
...
You can get an rsConnectionError error that shows a failed login for ASP.NET
when you configure a data source to use prompted or stored Windows
credentials, and the report server hosting the report runs under a domain
user account on a Windows 2000 server. When installing Reporting Services on
a Windows 2000 server, Microsoft recommends that you use the built-in account
(NT AUTHORITY/SYSTEM). If you use a domain user account (even one that has
local administrator privileges), it will limit your choices on how to
configure a data source connection for a report. Specifically, you cannot use
prompted or stored Windows credentials to connect to external data sources.
...
"Kathy" wrote:
> Hi folks,
> Please please can someone help, this problem has just been going on for too
> long.We need to run reports off RS using AS as the data source.
> Our setup is as follows
> RS - on seperate web server (srvWeb)
> RS DB - on seperate SQL server (srvDB)
> No problem - everything has worked fine connecting directly to the databases
> to extract data for the reports.
> However, when I connect to Analysis services on the RS DB (srvDB) server I
> get the following error
> a.. An error has occurred during report processing. (rsProcessingAborted)
> Get Online Help
> a.. Cannot create a connection to data source 'AS_Admin'.
> (rsErrorOpeningConnection) Get Online Help
> a.. Database 'BI_Admin' does not exist.
> If I connect using localhost as the datasource name - it works 100% on my
> machine but the minute I try to connect to a server (srvDB) that is not on
> my machine I get the error.
> We have installed Analysis Services on the web server (srvWeb) as well
> because it didn't have the correct drivers but this has not helped at all -
> I also registered the servers on AS (srvWeb) for what it's worth.
> Does anyone please please know what I should do to get this working.
> Thanks
> K
>
>
>

Reporting Services Add In

I am attempting to set up SharePoint and Reporting Services to run in
Integrated mode. I have successfully in stalled MOSS 2007 and Reporting
Services. I've installed SP2 for SQL Server and configured Reporting Services
to run in Integrated mode.
For some reason I cannot get the Reportnig Services add on for Sharepoint to
install. It gets to the very end of the installation and then performs a
rollback. I'm logged on as an Administrator. My user is also a farm
administrator. All the prereq's are installed.
The ONLY error I find in Even Viewer is the generic 11708 "failed to
install" error.
Has anyone else encountered this? If so, how did you resolve the issue?
Thanks!Check the log file at:
C:\Documents and Settings\[username]]\Local Settings\Temp\RS_SP_0.log
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:2F9CDC1A-35C4-47D6-A81B-40594AA9A9F6@.microsoft.com...
>I am attempting to set up SharePoint and Reporting Services to run in
> Integrated mode. I have successfully in stalled MOSS 2007 and Reporting
> Services. I've installed SP2 for SQL Server and configured Reporting
> Services
> to run in Integrated mode.
> For some reason I cannot get the Reportnig Services add on for Sharepoint
> to
> install. It gets to the very end of the installation and then performs a
> rollback. I'm logged on as an Administrator. My user is also a farm
> administrator. All the prereq's are installed.
> The ONLY error I find in Even Viewer is the generic 11708 "failed to
> install" error.
> Has anyone else encountered this? If so, how did you resolve the issue?
> Thanks!
>

Reporting Services 2005 WMI documentation inccorect?

I have SQL Server 2005 + Reporting Services 2005 installed on a machine. I've
implemented Custom Forms Security Extension. When I run the code below I get
a WMI Exception "Not Found"? I think maybe I have the namespace or class name
wrong... The documentation has both "MSReportManager_ConfigurationSetting"
and "MSReportServerReportManager_ConfigurationSetting" but neither seem to
work. The only WMI class that works properly is "MSReportServer_Instance".
What am I doing wrong?
________________________________________________________
Nuno Pereira
ManagementScope scope = new
ManagementScope(@."\\localhost\root\Microsoft\SqlServer\ReportServer\v9");
scope.Connect();
ManagementPath path = new
ManagementPath("MSReportServerReportManager_ConfigurationSetting");
ObjectGetOptions options = new ObjectGetOptions();
ManagementClass serverClass = new ManagementClass(scope, path, options);
serverClass.Get(); // throws a "Not Found" error...
________________________________________________________________I am now encountering a similar problem as you. I am looking into the sample
provided by MS on Using Forms Authentication in Reporting Services (this is
in SQL2K) and trying to replicate it using SQL 2005 and VS2005.
But I have problems accessing the PathName property of
MSReportServer_ConfigurationSetting, which i need to point to the installed
path for the Report Server. Can any one help to explain more on this area?
Thanks for any assistance.
"Nuno" wrote:
> I have SQL Server 2005 + Reporting Services 2005 installed on a machine. I've
> implemented Custom Forms Security Extension. When I run the code below I get
> a WMI Exception "Not Found"? I think maybe I have the namespace or class name
> wrong... The documentation has both "MSReportManager_ConfigurationSetting"
> and "MSReportServerReportManager_ConfigurationSetting" but neither seem to
> work. The only WMI class that works properly is "MSReportServer_Instance".
> What am I doing wrong?
> ________________________________________________________
> Nuno Pereira
> ManagementScope scope = new
> ManagementScope(@."\\localhost\root\Microsoft\SqlServer\ReportServer\v9");
> scope.Connect();
> ManagementPath path = new
> ManagementPath("MSReportServerReportManager_ConfigurationSetting");
> ObjectGetOptions options = new ObjectGetOptions();
> ManagementClass serverClass = new ManagementClass(scope, path, options);
> serverClass.Get(); // throws a "Not Found" error...
> ________________________________________________________________
>
>|||I am also encountering similar problems trying to retrieve the RS Web Service
from the following constants in Utils.cs provided in the RS 2000 Forms
Authentication example:
// WMI constants
const string WmiNamespace =@."\\localhost\root\Microsoft\SqlServer\ReportingServices\v8";
const string WmiRSClass = @."MSReportServerReportManager_ConfigurationSetting";
I've had to hardcode the Web Service URL (server.Url = "https://<server
name>/reportserver/reportservice.asmx";) in my UILogon.aspx.cs file to make
it work but this obviously isn't a solution since we have multiple test and
production enviornments.
"ice" wrote:
> I am now encountering a similar problem as you. I am looking into the sample
> provided by MS on Using Forms Authentication in Reporting Services (this is
> in SQL2K) and trying to replicate it using SQL 2005 and VS2005.
> But I have problems accessing the PathName property of
> MSReportServer_ConfigurationSetting, which i need to point to the installed
> path for the Report Server. Can any one help to explain more on this area?
> Thanks for any assistance.
> "Nuno" wrote:
> > I have SQL Server 2005 + Reporting Services 2005 installed on a machine. I've
> > implemented Custom Forms Security Extension. When I run the code below I get
> > a WMI Exception "Not Found"? I think maybe I have the namespace or class name
> > wrong... The documentation has both "MSReportManager_ConfigurationSetting"
> > and "MSReportServerReportManager_ConfigurationSetting" but neither seem to
> > work. The only WMI class that works properly is "MSReportServer_Instance".
> >
> > What am I doing wrong?
> >
> > ________________________________________________________
> >
> > Nuno Pereira
> > ManagementScope scope = new
> > ManagementScope(@."\\localhost\root\Microsoft\SqlServer\ReportServer\v9");
> >
> > scope.Connect();
> >
> > ManagementPath path = new
> > ManagementPath("MSReportServerReportManager_ConfigurationSetting");
> > ObjectGetOptions options = new ObjectGetOptions();
> > ManagementClass serverClass = new ManagementClass(scope, path, options);
> >
> > serverClass.Get(); // throws a "Not Found" error...
> > ________________________________________________________________
> >
> >
> >|||Is anybody from Microsoft listening? What's the answer to this question? What
are the equivalent constants for RS 2005 that were documented in the RS 2000
security extension example?
// RS 2000 Code
// WMI constants
const string WmiNamespace = @."\\localhost\root\Microsoft\SqlServer\ReportingServices\v8";
const string WmiRSClass = @."MSReportServerReportManager_ConfigurationSetting";
"JSI" wrote:
> I am also encountering similar problems trying to retrieve the RS Web Service
> from the following constants in Utils.cs provided in the RS 2000 Forms
> Authentication example:
> // WMI constants
> const string WmiNamespace => @."\\localhost\root\Microsoft\SqlServer\ReportingServices\v8";
> const string WmiRSClass = @."MSReportServerReportManager_ConfigurationSetting";
> I've had to hardcode the Web Service URL (server.Url = "https://<server
> name>/reportserver/reportservice.asmx";) in my UILogon.aspx.cs file to make
> it work but this obviously isn't a solution since we have multiple test and
> production enviornments.
> "ice" wrote:
> > I am now encountering a similar problem as you. I am looking into the sample
> > provided by MS on Using Forms Authentication in Reporting Services (this is
> > in SQL2K) and trying to replicate it using SQL 2005 and VS2005.
> >
> > But I have problems accessing the PathName property of
> > MSReportServer_ConfigurationSetting, which i need to point to the installed
> > path for the Report Server. Can any one help to explain more on this area?
> >
> > Thanks for any assistance.
> >
> > "Nuno" wrote:
> >
> > > I have SQL Server 2005 + Reporting Services 2005 installed on a machine. I've
> > > implemented Custom Forms Security Extension. When I run the code below I get
> > > a WMI Exception "Not Found"? I think maybe I have the namespace or class name
> > > wrong... The documentation has both "MSReportManager_ConfigurationSetting"
> > > and "MSReportServerReportManager_ConfigurationSetting" but neither seem to
> > > work. The only WMI class that works properly is "MSReportServer_Instance".
> > >
> > > What am I doing wrong?
> > >
> > > ________________________________________________________
> > >
> > > Nuno Pereira
> > > ManagementScope scope = new
> > > ManagementScope(@."\\localhost\root\Microsoft\SqlServer\ReportServer\v9");
> > >
> > > scope.Connect();
> > >
> > > ManagementPath path = new
> > > ManagementPath("MSReportServerReportManager_ConfigurationSetting");
> > > ObjectGetOptions options = new ObjectGetOptions();
> > > ManagementClass serverClass = new ManagementClass(scope, path, options);
> > >
> > > serverClass.Get(); // throws a "Not Found" error...
> > > ________________________________________________________________
> > >
> > >
> > >|||For those of you who are still looking for the answer. The namespace in sql server 2005 is: "\\localhost\root\Microsoft\SqlServer\ReportServer\v9\Admin" while the class for report manager instance is: "MSReportManager_ConfigurationSetting". For more details please refer to: http://msdn2.microsoft.com/en-us/library/ms153571.aspx
From http://www.developmentnow.com/g/115_2006_2_0_0_693931/Reporting-Services-2005-WMI-documentation-inccorect.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

Reporting Services 2005 semantic query error

I recently changed my datasource in our Reporting Service Model. We changed to a quicker SQL server with disk capacity. I am able to run new reports but when I try to run old ones that were on the old datasource, I get an error. The datasource has been changed to point to the new server (datasource).

The error is: An error has occurred during report processing. (rsProcessingAborted)
Semantic query compilation failed: e EmptySemanticQuery The SemanticQuery does not contain any Groupings or MeasureGroups. SemanticQuery must contain at least one of these elements. (SemanticQuery ''). (rsSemanticQueryEngineError)

I will appreciate any help.

Nick

Is your data source an SSAS 2005 cube? Or did you misconfigure the model data source? Use the Report Manager to verify/reset the model data source.

|||My datasource is one of our SQL server databases. I know that the datasource is not misconfigured in the model because I am able to run new reports.sql

Reporting Services 2005 Access Issue

Hi Everyone,

I've set all SQL services to run using a domain account which is a local administrator, I am able to view reports on the server but when someone from client machine connects to the server it prompts for user id and password and doesn't like unless I give a local server user who is also a local admin on box.

The app pools are also running using a domain a/c which is local admin but even we can't access reports even if I enter this ID from client machine. It is asking for a server user who is also an admin. I went ahead and added all domain users to the browser group but still no success.

The other problem I've is the link to report sent by Subscriptions service is broken, when I click on it I get a page not found error. How can we fix this?

Any help would be good.

Regards,

Sumit

Hey Sumit,

You might want to make sure you have 'Integrated Windows authentication' checked in Reports and ReportServer within IIS. Or you could have 'Enable anonymous access' checked.

Hope this helps.

Jarret

Reporting Services 2000, VS2005 and SQL Server 2000

I am running MS SQL 2000 and VS .Net 2005.

Is it possible to run Microsoft Reporting Services 2000 with VS2005 or am I required to upgrade the SQL server and reporting services to 2005 just to produce a few WEB reports ? This would be too costly both in money and time.

I have just finished developing a solution using the version of Crystal Reports which is built into VS 2005. However I now believe that I require costly licenses to deploy the solution on the live WEB server. Can anyone confirm this assumption (in simple terms).

There really does to be a lack of a descent, reasonable cost, solution for generating WEB reports against an MS SQL 2000 database whilst using the VS 2005 development environment. Maybe I am wrong ?

Any suggestions would be appreciated.

Cheers

George

Please help ...

In simple terms, I have VS2005 and am limited to MSSQL 2000 and want to generate some WEB reports. Can I use Reporting Services 2000 services to do this ?

Upgrading to SQL 2005 is NOT an option and I have a large WEB project built using VS2005 (VB).

This now all seems to be so difficult. Any help would be appreciated.

Cheers

George

Reporting Services $$$

It is licensed the same as SQL Server so if you run it on the same box (web
service + db) as SQL then there is no additional cost above the SQL license
cost. If you install the web service on a separate server then you need a
SQL license for it in addition to the one on the server hosting the report
server databases.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Eleanor, MCSE/MCT" <EleanorMCSEMCT@.discussions.microsoft.com> wrote in
message news:8B63E947-80D1-490B-ACFA-29D6AD429E3B@.microsoft.com...
> Is there a cost associated with SQL 2000 Reporting Services, i.e., does
> one
> have to purchase it or does it come as a tool with SQL?
> Thanks!
> EleanorThanks!
"Jasper Smith" wrote:

> It is licensed the same as SQL Server so if you run it on the same box (we
b
> service + db) as SQL then there is no additional cost above the SQL licens
e
> cost. If you install the web service on a separate server then you need a
> SQL license for it in addition to the one on the server hosting the report
> server databases.
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Eleanor, MCSE/MCT" <EleanorMCSEMCT@.discussions.microsoft.com> wrote in
> message news:8B63E947-80D1-490B-ACFA-29D6AD429E3B@.microsoft.com...
>
>|||Where does one find the Reporting Tools? Are they on the SQL CD? Also, I
want to pull reports from another server (SharePoint). Does the Reporting
Tools have to reside on the same server?
Sorry, I am a newbie, as you can tell.
-Eleanor
"Jasper Smith" wrote:

> It is licensed the same as SQL Server so if you run it on the same box (we
b
> service + db) as SQL then there is no additional cost above the SQL licens
e
> cost. If you install the web service on a separate server then you need a
> SQL license for it in addition to the one on the server hosting the report
> server databases.
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Eleanor, MCSE/MCT" <EleanorMCSEMCT@.discussions.microsoft.com> wrote in
> message news:8B63E947-80D1-490B-ACFA-29D6AD429E3B@.microsoft.com...
>
>|||Is there a cost associated with SQL 2000 Reporting Services, i.e., does one
have to purchase it or does it come as a tool with SQL?
Thanks!
Eleanor|||It is licensed the same as SQL Server so if you run it on the same box (web
service + db) as SQL then there is no additional cost above the SQL license
cost. If you install the web service on a separate server then you need a
SQL license for it in addition to the one on the server hosting the report
server databases.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Eleanor, MCSE/MCT" <EleanorMCSEMCT@.discussions.microsoft.com> wrote in
message news:8B63E947-80D1-490B-ACFA-29D6AD429E3B@.microsoft.com...
> Is there a cost associated with SQL 2000 Reporting Services, i.e., does
> one
> have to purchase it or does it come as a tool with SQL?
> Thanks!
> Eleanor|||Thanks!
"Jasper Smith" wrote:

> It is licensed the same as SQL Server so if you run it on the same box (we
b
> service + db) as SQL then there is no additional cost above the SQL licens
e
> cost. If you install the web service on a separate server then you need a
> SQL license for it in addition to the one on the server hosting the report
> server databases.
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Eleanor, MCSE/MCT" <EleanorMCSEMCT@.discussions.microsoft.com> wrote in
> message news:8B63E947-80D1-490B-ACFA-29D6AD429E3B@.microsoft.com...
>
>|||Where does one find the Reporting Tools? Are they on the SQL CD? Also, I
want to pull reports from another server (SharePoint). Does the Reporting
Tools have to reside on the same server?
Sorry, I am a newbie, as you can tell.
-Eleanor
"Jasper Smith" wrote:

> It is licensed the same as SQL Server so if you run it on the same box (we
b
> service + db) as SQL then there is no additional cost above the SQL licens
e
> cost. If you install the web service on a separate server then you need a
> SQL license for it in addition to the one on the server hosting the report
> server databases.
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Eleanor, MCSE/MCT" <EleanorMCSEMCT@.discussions.microsoft.com> wrote in
> message news:8B63E947-80D1-490B-ACFA-29D6AD429E3B@.microsoft.com...
>
>|||Reporting Services is a separate CD.
[url]http://www.microsoft.com/sql/prodinfo/previousversions/rs/retailfulfillment.mspx[/
url]
Reporting Services can pull from almost any data source, local or remote.
It can even combine data from multiple sources.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Eleanor, MCSE/MCT" <EleanorMCSEMCT@.discussions.microsoft.com> wrote in
message news:7FBB43BF-C798-4617-94C8-63BCA3CBB5AC@.microsoft.com...[vbcol=seagreen]
> Where does one find the Reporting Tools? Are they on the SQL CD? Also, I
> want to pull reports from another server (SharePoint). Does the Reporting
> Tools have to reside on the same server?
> Sorry, I am a newbie, as you can tell.
> -Eleanor
> "Jasper Smith" wrote:
>|||Reporting Services is a separate CD.
[url]http://www.microsoft.com/sql/prodinfo/previousversions/rs/retailfulfillment.mspx[/
url]
Reporting Services can pull from almost any data source, local or remote.
It can even combine data from multiple sources.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Eleanor, MCSE/MCT" <EleanorMCSEMCT@.discussions.microsoft.com> wrote in
message news:7FBB43BF-C798-4617-94C8-63BCA3CBB5AC@.microsoft.com...[vbcol=seagreen]
> Where does one find the Reporting Tools? Are they on the SQL CD? Also, I
> want to pull reports from another server (SharePoint). Does the Reporting
> Tools have to reside on the same server?
> Sorry, I am a newbie, as you can tell.
> -Eleanor
> "Jasper Smith" wrote:
>|||Thank you for the response. I need this yesterday, as is the case with
everything in IT :-) My company has a volumn software license and MSDE. Is
this tool contained within any of these sources?
Thanks!
-Eleanor
"Geoff N. Hiten" wrote:

> Reporting Services is a separate CD.
> http://www.microsoft.com/sql/prodin...nt.mspx

> Reporting Services can pull from almost any data source, local or remote.
> It can even combine data from multiple sources.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Eleanor, MCSE/MCT" <EleanorMCSEMCT@.discussions.microsoft.com> wrote in
> message news:7FBB43BF-C798-4617-94C8-63BCA3CBB5AC@.microsoft.com...
>
>sql

Reporting Services - MS SQL 2005 beta

Hello,
I'm trying to run reporting services that comes with MS SQL 2005 beta. I've
just installed it and I know I have to activate eg. using rsactivate... Here
comes the problem, rsactivate gives me the following message:
"File or assembly name 'ReportingServicesNativeClient, ....', or one of its
dependencies, was not found."
It seems for me that there is ReportingServicesNativeClient assembly in my
system, because I installed it (using autorun setup) and there are dlls in
few directories. What's wrong then'
Marek Aleksander DabekYou need to run rsactivate from the \Report Manager\bin directory. It is in
the wrong path in Beta 2.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mad One" <madonehc@.NO-SPAM-PLEASE.poczta.onet.pl> wrote in message
news:ucv6smfqEHA.1960@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm trying to run reporting services that comes with MS SQL 2005 beta.
I've
> just installed it and I know I have to activate eg. using rsactivate...
Here
> comes the problem, rsactivate gives me the following message:
> "File or assembly name 'ReportingServicesNativeClient, ....', or one of
its
> dependencies, was not found."
> It seems for me that there is ReportingServicesNativeClient assembly in my
> system, because I installed it (using autorun setup) and there are dlls in
> few directories. What's wrong then'
> Marek Aleksander Dabek
>|||> You need to run rsactivate from the \Report Manager\bin directory. It is
in
> the wrong path in Beta 2.
It worked perfectly. Thank you.

Reporting Services

Dear sir
When i try to subscribe the report, it gives error "Credentials used to run
this report are not stored".
thanks and regards
arvind
asharma@.techaxes.com
--
asharmahttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/rswork/htm/rms_datasources_v1_87e9.asp
Please go through the above URL. I hope this helps.
Reetesh B. Chhatpar
ShawMan Software Enterprises
www.shawmansoftware.com
Diamond is just another piece of coal that did well under pressure.
"asharma2004" <asharma@.harbingertechaxes.com> wrote in message
news:8A4C67B4-F0FD-4647-AA6D-988E5F7B5F39@.microsoft.com...
> Dear sir
> When i try to subscribe the report, it gives error "Credentials used to
run
> this report are not stored".
> thanks and regards
> arvind
> asharma@.techaxes.com
> --
> asharma|||this works for me
(where "reports" is my web reference to reporting services)
Dim Credentials As reports.DataSourceCredentials()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.render.....blah blah blah
"asharma2004" wrote:
> Dear sir
> When i try to subscribe the report, it gives error "Credentials used to run
> this report are not stored".
> thanks and regards
> arvind
> asharma@.techaxes.com
> --
> asharma

reporting service performance issue

Hi,
I construct a sql statement as a dataset. But when I click Run or Refresh,
the VS.NET runs so slow and no response sometimes. Do you know why, and how
to
deal with this issue?
below is the sql statement I used for:
DECLARE
@.sql NVARCHAR(4000)
select @.resp=ltrim(rtrim(@.resp)),
@.country =ltrim(rtrim(@.country )),
@.entity=ltrim(rtrim(@.entity)),
@.loc=ltrim(rtrim(@.loc)),
--@.office=ltrim(rtrim(@.office)),
@.local_oc=ltrim(rtrim(@.local_oc)),
@.for_agent=ltrim(rtrim(@.for_agent))
select @.sql =
'SELECT Country_Application.country_code, Country_Application.type,
Country_Application.appl_no,
Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
Action_Due.item, Action_Due.action_due_resp_atty,
Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
Action_Due.due_date,
Action_Due.remark, Action_Due.resp_admin
from Country_Application INNER JOIN Action_Due on
Country_Application.pdno = Action_Due.pdno and Country_Application.sub_case
= Action_Due.sub_case
inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type) <>
''ANNUITY''' + --hard code
' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' + @.DateEnd +
''''
if @.resp not in ('ALL', '')
begin
set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
end
if @.country not in ('ALL', '')
begin
if @.country = '<> US'
begin
set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
end
else
begin
set @.sql = @.sql + ' and Country_Application.country_code in ('+ @.country
+')'
end
end
if @.entity not in ('ALL', '')
begin
set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
Invention_Data.loc = '''+@.loc+''''
end
if @.local_oc <> ''
begin
set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
end
if @.for_agent <> ''
begin
set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
end
exec sp_executesql @.sqlThe best way to deal with this is to pull the query out and run it in SQL
SErver Management Studio - get the query plan and find out what is
happening...
You are using some <>, and functions in where clauses which prevents the
optimizer from using index statistics to choose indexes..so you might not the
best plan...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"David Zhu" wrote:
> Hi,
> I construct a sql statement as a dataset. But when I click Run or Refresh,
> the VS.NET runs so slow and no response sometimes. Do you know why, and how
> to
> deal with this issue?
> below is the sql statement I used for:
> DECLARE
> @.sql NVARCHAR(4000)
> select @.resp=ltrim(rtrim(@.resp)),
> @.country =ltrim(rtrim(@.country )),
> @.entity=ltrim(rtrim(@.entity)),
> @.loc=ltrim(rtrim(@.loc)),
> --@.office=ltrim(rtrim(@.office)),
> @.local_oc=ltrim(rtrim(@.local_oc)),
> @.for_agent=ltrim(rtrim(@.for_agent))
> select @.sql => 'SELECT Country_Application.country_code, Country_Application.type,
> Country_Application.appl_no,
> Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
> Action_Due.item, Action_Due.action_due_resp_atty,
> Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
> Action_Due.due_date,
> Action_Due.remark, Action_Due.resp_admin
> from Country_Application INNER JOIN Action_Due on
> Country_Application.pdno = Action_Due.pdno and Country_Application.sub_case
> = Action_Due.sub_case
> inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
>
> where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type) <>
> ''ANNUITY''' + --hard code
> ' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' + @.DateEnd +
> ''''
> if @.resp not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
> end
> if @.country not in ('ALL', '')
> begin
> if @.country = '<> US'
> begin
> set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
> end
> else
> begin
> set @.sql = @.sql + ' and Country_Application.country_code in ('+ @.country
> +')'
> end
> end
> if @.entity not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
> Invention_Data.loc = '''+@.loc+''''
> end
> if @.local_oc <> ''
> begin
> set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
> end
> if @.for_agent <> ''
> begin
> set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
> end
> exec sp_executesql @.sql
>
>
>
>|||Hi Snyder,
Thanks a lot.
I also did some investigation. Unfortunately, the sql statement run fast
enough in
Sql query analyser. And I found the report run fast on the RS2000 without
SP2 machine, but slow on RS2000 with SP2.
Could you please give me some suggestion?
"Wayne Snyder" wrote:
> The best way to deal with this is to pull the query out and run it in SQL
> SErver Management Studio - get the query plan and find out what is
> happening...
> You are using some <>, and functions in where clauses which prevents the
> optimizer from using index statistics to choose indexes..so you might not the
> best plan...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "David Zhu" wrote:
> > Hi,
> >
> > I construct a sql statement as a dataset. But when I click Run or Refresh,
> > the VS.NET runs so slow and no response sometimes. Do you know why, and how
> > to
> > deal with this issue?
> >
> > below is the sql statement I used for:
> >
> > DECLARE
> >
> > @.sql NVARCHAR(4000)
> >
> > select @.resp=ltrim(rtrim(@.resp)),
> > @.country =ltrim(rtrim(@.country )),
> > @.entity=ltrim(rtrim(@.entity)),
> > @.loc=ltrim(rtrim(@.loc)),
> > --@.office=ltrim(rtrim(@.office)),
> > @.local_oc=ltrim(rtrim(@.local_oc)),
> > @.for_agent=ltrim(rtrim(@.for_agent))
> >
> > select @.sql => >
> > 'SELECT Country_Application.country_code, Country_Application.type,
> > Country_Application.appl_no,
> > Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
> > Action_Due.item, Action_Due.action_due_resp_atty,
> > Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
> > Action_Due.due_date,
> > Action_Due.remark, Action_Due.resp_admin
> >
> > from Country_Application INNER JOIN Action_Due on
> > Country_Application.pdno = Action_Due.pdno and Country_Application.sub_case
> > = Action_Due.sub_case
> > inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
> >
> >
> > where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type) <>
> > ''ANNUITY''' + --hard code
> >
> > ' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' + @.DateEnd +
> > ''''
> >
> > if @.resp not in ('ALL', '')
> > begin
> > set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
> > end
> >
> > if @.country not in ('ALL', '')
> > begin
> > if @.country = '<> US'
> > begin
> > set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
> > end
> > else
> > begin
> > set @.sql = @.sql + ' and Country_Application.country_code in ('+ @.country
> > +')'
> > end
> > end
> >
> > if @.entity not in ('ALL', '')
> > begin
> > set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
> > Invention_Data.loc = '''+@.loc+''''
> > end
> >
> > if @.local_oc <> ''
> > begin
> > set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
> > end
> >
> > if @.for_agent <> ''
> > begin
> > set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
> > end
> >
> > exec sp_executesql @.sql
> >
> >
> >
> >
> >
> >
> >|||My suggestion is to put this in a stored procedure and call that instead and
see what that does for performance.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David Zhu" <DavidZhu@.discussions.microsoft.com> wrote in message
news:C32A3B2C-5392-4C4B-B909-13D6B1B26F9B@.microsoft.com...
> Hi Snyder,
> Thanks a lot.
> I also did some investigation. Unfortunately, the sql statement run fast
> enough in
> Sql query analyser. And I found the report run fast on the RS2000 without
> SP2 machine, but slow on RS2000 with SP2.
> Could you please give me some suggestion?
> "Wayne Snyder" wrote:
>> The best way to deal with this is to pull the query out and run it in SQL
>> SErver Management Studio - get the query plan and find out what is
>> happening...
>> You are using some <>, and functions in where clauses which prevents the
>> optimizer from using index statistics to choose indexes..so you might not
>> the
>> best plan...
>> --
>> Wayne Snyder MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> I support the Professional Association for SQL Server ( PASS) and it''s
>> community of SQL Professionals.
>>
>> "David Zhu" wrote:
>> > Hi,
>> >
>> > I construct a sql statement as a dataset. But when I click Run or
>> > Refresh,
>> > the VS.NET runs so slow and no response sometimes. Do you know why, and
>> > how
>> > to
>> > deal with this issue?
>> >
>> > below is the sql statement I used for:
>> >
>> > DECLARE
>> >
>> > @.sql NVARCHAR(4000)
>> >
>> > select @.resp=ltrim(rtrim(@.resp)),
>> > @.country =ltrim(rtrim(@.country )),
>> > @.entity=ltrim(rtrim(@.entity)),
>> > @.loc=ltrim(rtrim(@.loc)),
>> > --@.office=ltrim(rtrim(@.office)),
>> > @.local_oc=ltrim(rtrim(@.local_oc)),
>> > @.for_agent=ltrim(rtrim(@.for_agent))
>> >
>> > select @.sql =>> >
>> > 'SELECT Country_Application.country_code, Country_Application.type,
>> > Country_Application.appl_no,
>> > Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
>> > Action_Due.item, Action_Due.action_due_resp_atty,
>> > Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
>> > Action_Due.due_date,
>> > Action_Due.remark, Action_Due.resp_admin
>> >
>> > from Country_Application INNER JOIN Action_Due on
>> > Country_Application.pdno = Action_Due.pdno and
>> > Country_Application.sub_case
>> > = Action_Due.sub_case
>> > inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
>> >
>> >
>> > where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type)
>> > <>
>> > ''ANNUITY''' + --hard code
>> >
>> > ' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' +
>> > @.DateEnd +
>> > ''''
>> >
>> > if @.resp not in ('ALL', '')
>> > begin
>> > set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
>> > end
>> >
>> > if @.country not in ('ALL', '')
>> > begin
>> > if @.country = '<> US'
>> > begin
>> > set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
>> > end
>> > else
>> > begin
>> > set @.sql = @.sql + ' and Country_Application.country_code in ('+
>> > @.country
>> > +')'
>> > end
>> > end
>> >
>> > if @.entity not in ('ALL', '')
>> > begin
>> > set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
>> > Invention_Data.loc = '''+@.loc+''''
>> > end
>> >
>> > if @.local_oc <> ''
>> > begin
>> > set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
>> > end
>> >
>> > if @.for_agent <> ''
>> > begin
>> > set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
>> > end
>> >
>> > exec sp_executesql @.sql
>> >
>> >
>> >
>> >
>> >
>> >
>> >|||I have the same problem..
Unfortunately, things did improve when I wrote it into a stored procedure.
The bad part about this is that we are planning to allow our customers
create reports using our views. They will not like extremely slow reports. I
have tracked this down in the log files to the fact that the report is being
add to a job list and then not being run from 1 to 3 minutes.
I haven't figured out how to make the report run without going to this list.
"David Zhu" <DavidZhu@.discussions.microsoft.com> wrote in message
news:44673263-E25F-44C1-8C33-E5D9DF419422@.microsoft.com...
> Hi,
> I construct a sql statement as a dataset. But when I click Run or Refresh,
> the VS.NET runs so slow and no response sometimes. Do you know why, and
> how
> to
> deal with this issue?
> below is the sql statement I used for:
> DECLARE
> @.sql NVARCHAR(4000)
> select @.resp=ltrim(rtrim(@.resp)),
> @.country =ltrim(rtrim(@.country )),
> @.entity=ltrim(rtrim(@.entity)),
> @.loc=ltrim(rtrim(@.loc)),
> --@.office=ltrim(rtrim(@.office)),
> @.local_oc=ltrim(rtrim(@.local_oc)),
> @.for_agent=ltrim(rtrim(@.for_agent))
> select @.sql => 'SELECT Country_Application.country_code, Country_Application.type,
> Country_Application.appl_no,
> Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
> Action_Due.item, Action_Due.action_due_resp_atty,
> Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
> Action_Due.due_date,
> Action_Due.remark, Action_Due.resp_admin
> from Country_Application INNER JOIN Action_Due on
> Country_Application.pdno = Action_Due.pdno and
> Country_Application.sub_case
> = Action_Due.sub_case
> inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
>
> where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type) <>
> ''ANNUITY''' + --hard code
> ' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' + @.DateEnd
> +
> ''''
> if @.resp not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
> end
> if @.country not in ('ALL', '')
> begin
> if @.country = '<> US'
> begin
> set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
> end
> else
> begin
> set @.sql = @.sql + ' and Country_Application.country_code in ('+ @.country
> +')'
> end
> end
> if @.entity not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
> Invention_Data.loc = '''+@.loc+''''
> end
> if @.local_oc <> ''
> begin
> set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
> end
> if @.for_agent <> ''
> begin
> set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
> end
> exec sp_executesql @.sql
>
>
>
>|||David.
You may experience a great performance improvment if you write the
statement as a stored procedure AND avoid using dynamic execution
(sp_executesql).
In fact, you can rewrite your SQL query in a simple statement.
Try to replace code like the following:
if @.resp not in ('ALL', '')
begin
set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
end
by a condition in the clause WHERE:
WHERE
...
AND (@.resp not in ('ALL', '') or Action_Due.hpj_resp_atty in (select
[str] from fList(@.resp)))
where fList is as UDF that converts a comma-separated list of values to
a table:
ALTER FUNCTION fList(@.list ntext)
RETURNS @.tbl TABLE
( listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000) COLLATE
SQL_Latin1_General_CP1_CI_AS,
nstr nvarchar(2000) COLLATE
SQL_Latin1_General_CP1_CI_AS
)
AS
BEGIN
if @.list is null return
DECLARE @.pos int, @.textpos int, @.tam smallint, @.tmpstr
nvarchar(4000), @.resto nvarchar(4000), @.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.resto = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.tam = 4000 - datalength(@.resto) / 2
SET @.tmpstr = @.resto + substring(@.list, @.textpos, @.tam)
SET @.textpos = @.textpos + @.tam
SET @.pos = charindex(',', @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(',', @.tmpstr)
END
SET @.resto = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.resto)),
ltrim(rtrim(@.resto)))
RETURN
END
David Zhu escreveu:
> Hi,
> I construct a sql statement as a dataset. But when I click Run or Refresh,
> the VS.NET runs so slow and no response sometimes. Do you know why, and how
> to
> deal with this issue?
> below is the sql statement I used for:
> DECLARE
> @.sql NVARCHAR(4000)
> select @.resp=ltrim(rtrim(@.resp)),
> @.country =ltrim(rtrim(@.country )),
> @.entity=ltrim(rtrim(@.entity)),
> @.loc=ltrim(rtrim(@.loc)),
> --@.office=ltrim(rtrim(@.office)),
> @.local_oc=ltrim(rtrim(@.local_oc)),
> @.for_agent=ltrim(rtrim(@.for_agent))
> select @.sql => 'SELECT Country_Application.country_code, Country_Application.type,
> Country_Application.appl_no,
> Action_Due.hpj_resp_atty, Action_Due.pdno, Action_Due.sub_case,
> Action_Due.item, Action_Due.action_due_resp_atty,
> Action_Due.action_type, Action_Due.action_due, Action_Due.indicator,
> Action_Due.due_date,
> Action_Due.remark, Action_Due.resp_admin
> from Country_Application INNER JOIN Action_Due on
> Country_Application.pdno = Action_Due.pdno and Country_Application.sub_case
> = Action_Due.sub_case
> inner join Invention_Data on Action_Due.pdno=Invention_Data.pdno
>
> where upper(Action_Due.done) = ''NO'' and upper(Action_Due.action_type) <>
> ''ANNUITY''' + --hard code
> ' and Action_Due.due_date between '''+ @.DateBegin + ''' and ''' + @.DateEnd +
> ''''
> if @.resp not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
> end
> if @.country not in ('ALL', '')
> begin
> if @.country = '<> US'
> begin
> set @.sql = @.sql + ' and Country_Application.country_code <> ''US'''
> end
> else
> begin
> set @.sql = @.sql + ' and Country_Application.country_code in ('+ @.country
> +')'
> end
> end
> if @.entity not in ('ALL', '')
> begin
> set @.sql = @.sql + ' and Invention_Data.entity = '''+@.entity+''' and
> Invention_Data.loc = '''+@.loc+''''
> end
> if @.local_oc <> ''
> begin
> set @.sql = @.sql +' and Action_Due.local_oc = '''+@.local_oc+''''
> end
> if @.for_agent <> ''
> begin
> set @.sql = @.sql +' and Action_Due.for_agent = '''+@.for_agent+''''
> end
> exec sp_executesql @.sql|||David.
You may experience a great performance improvment if you write the
statement as a stored procedure AND avoid using dynamic execution
(sp_executesql).
In fact, you can rewrite your SQL query in a simple statement.
Try to replace code like the following:
if @.resp not in ('ALL', '')
begin
set @.sql = @.sql + ' and Action_Due.hpj_resp_atty in ('+ @.resp + ')'
end
by a condition in the clause WHERE:
WHERE
...
AND (@.resp not in ('ALL', '') or Action_Due.hpj_resp_atty in (select
[str] from fList(@.resp)))
where fList is as UDF that converts a comma-separated list of values to
a table:
ALTER FUNCTION fList(@.list ntext)
RETURNS @.tbl TABLE
( listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000) COLLATE
SQL_Latin1_General_CP1_CI_AS,
nstr nvarchar(2000) COLLATE
SQL_Latin1_General_CP1_CI_AS
)
AS
BEGIN
if @.list is null return
DECLARE @.pos int, @.textpos int, @.tam smallint, @.tmpstr
nvarchar(4000), @.resto nvarchar(4000), @.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.resto = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.tam = 4000 - datalength(@.resto) / 2
SET @.tmpstr = @.resto + substring(@.list, @.textpos, @.tam)
SET @.textpos = @.textpos + @.tam
SET @.pos = charindex(',', @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(',', @.tmpstr)
END
SET @.resto = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.resto)),
ltrim(rtrim(@.resto)))
RETURN
END

reporting service error

When I try to run the web page which host the report, I am not able to reach the home page and following error show up.

" The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content and then restart the service. Check the documentation for more information. (rsReportServerDisabled) Get Online Help
Bad Data."

I then went to Reporting Services Configuration Manager -> Encryption Keys

then I clicked the Change button

after all these I can get back to the reporting service home page. Any one can tell me the reason of this error? thanks

when I tried to open the reporting service web site. The following error pop up

The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content and then restart the service. Check the documentation for more information. (rsReportServerDisabled) Get Online Help
Bad Data.

Then I go to Reporting Service Manager-> Encryption keys. After I changed the encryption keys; the reporting service works again. Could anyone explain the reason behind this? ans is that the right way to reslove the error?

Thanks

|||Reporting Services data is encrypted. Did you restore the database or change the service account not through the Condifguration manager ? After changing / deleting the key 8and the encrypted content) the database was empty, you had a new key and you could start all over.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Reporting Server -- Transaction Log Backups won't restore

I am trying to use my Standby Server as a Reporting Server so that our
resource intensive reporting queries can be run against it instead of
production.
I've been successful at automating the backups and restores of all the
databases as well as transaction logs from production to standby. However,
if someone is trying to do a select query at the time, or even just have
their reporting application connected to it, the restore of the transaction
log fails "due to not being able to gain exclusive access". If one of the
transaction log restores fail, they all fail after that because the LSNs are
off.
This is, of course, an urgent problem. Management has required that this
server be as current as possible for reporting purposes and this entire
process must be automated.
Servers are W2K Server sp4, SQL2K Standard Edition sp3a.
Below is an example of my syntax for the transaction log restores. Works
like a charm unless something is connected to the database. I had to do it
this way because I wanted to save all the transaction log files taken
throughout the day in case of "point in time" disaster recovery. I ended up
opening a ticket with Microsoft and this is the best solution they could
suggest. They said that normally with log shipping, the TLog backups
overwrite the others in the device. I found it difficult to believe that it
was expected that every process would work correctly every time and you
would have no need to ever access a transaction log backup that was
previously taken. If it has been overwritten, and you try to restore one
that is later, SQL send the error about LSNs not being right.
Is there something in here that needs to be changed to accomplish my task?
CREATE proc RestoreApplicationLogs_TLog
as
declare @.position int
declare @.mediasetid int
set @.mediasetid = (select media_set_id
from NCNSV1015.msdb.dbo.backupmediafamily
where logical_device_name = 'ApplicationLogs_TLog')
set @.position= (select position from NCNSV1015.msdb.dbo.backupset
where Backup_set_id = (select max(backup_set_id) from
NCNSV1015.msdb.dbo.backupset where media_set_id = @.mediasetid))
Restore Log ApplicationLogs
from disk = 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak'
with file = @.position,
dbo_only,
standby = 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf'
waitfor delay '00:00:10'
GO
Please help.
Thank you in advance,
DeborahLog shipping is not the best way to create a reporting
server, especially with frequent transaction log loads.
To apply a transaction log, SQL Server requires that no
connections are in the database; it needs exclusive access.
You'll have to manually kick out the users for the restore
if you want to use it as a reporting server.
My recommendation is to always use another method for
reporting.
That said, the built in feature of SQL 2K EE log shipping
will apply your tran logs in order, even if it is in
standby; you will just create more latency if people are
in it. I've coded a version that does the same thing.
>--Original Message--
>I am trying to use my Standby Server as a Reporting
Server so that our
>resource intensive reporting queries can be run against
it instead of
>production.
>I've been successful at automating the backups and
restores of all the
>databases as well as transaction logs from production to
standby. However,
>if someone is trying to do a select query at the time, or
even just have
>their reporting application connected to it, the restore
of the transaction
>log fails "due to not being able to gain exclusive
access". If one of the
>transaction log restores fail, they all fail after that
because the LSNs are
>off.
>This is, of course, an urgent problem. Management has
required that this
>server be as current as possible for reporting purposes
and this entire
>process must be automated.
>Servers are W2K Server sp4, SQL2K Standard Edition sp3a.
>Below is an example of my syntax for the transaction log
restores. Works
>like a charm unless something is connected to the
database. I had to do it
>this way because I wanted to save all the transaction log
files taken
>throughout the day in case of "point in time" disaster
recovery. I ended up
>opening a ticket with Microsoft and this is the best
solution they could
>suggest. They said that normally with log shipping, the
TLog backups
>overwrite the others in the device. I found it difficult
to believe that it
>was expected that every process would work correctly
every time and you
>would have no need to ever access a transaction log
backup that was
>previously taken. If it has been overwritten, and you
try to restore one
>that is later, SQL send the error about LSNs not being
right.
> Is there something in here that needs to be changed to
accomplish my task?
>CREATE proc RestoreApplicationLogs_TLog
>as
>declare @.position int
>declare @.mediasetid int
>set @.mediasetid = (select media_set_id
>from NCNSV1015.msdb.dbo.backupmediafamily
>where logical_device_name = 'ApplicationLogs_TLog')
>set @.position= (select position from
NCNSV1015.msdb.dbo.backupset
>where Backup_set_id = (select max(backup_set_id) from
>NCNSV1015.msdb.dbo.backupset where media_set_id =@.mediasetid))
>Restore Log ApplicationLogs
>from disk
= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
'
>with file = @.position,
>dbo_only,
>standby
= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
'
>waitfor delay '00:00:10'
>GO
>
>Please help.
>Thank you in advance,
>Deborah
>
>.
>|||You need to be using something like replication to meet
your stated requirements. In order for a db to be restored
nobody can be accessing it. Log shipping has an option to
allow users into the db while its not being restored and
it will kick everony out when the restore is starting, but
this isnt usually a good idea for a reporting server.
>--Original Message--
>I am trying to use my Standby Server as a Reporting
Server so that our
>resource intensive reporting queries can be run against
it instead of
>production.
>I've been successful at automating the backups and
restores of all the
>databases as well as transaction logs from production to
standby. However,
>if someone is trying to do a select query at the time, or
even just have
>their reporting application connected to it, the restore
of the transaction
>log fails "due to not being able to gain exclusive
access". If one of the
>transaction log restores fail, they all fail after that
because the LSNs are
>off.
>This is, of course, an urgent problem. Management has
required that this
>server be as current as possible for reporting purposes
and this entire
>process must be automated.
>Servers are W2K Server sp4, SQL2K Standard Edition sp3a.
>Below is an example of my syntax for the transaction log
restores. Works
>like a charm unless something is connected to the
database. I had to do it
>this way because I wanted to save all the transaction log
files taken
>throughout the day in case of "point in time" disaster
recovery. I ended up
>opening a ticket with Microsoft and this is the best
solution they could
>suggest. They said that normally with log shipping, the
TLog backups
>overwrite the others in the device. I found it difficult
to believe that it
>was expected that every process would work correctly
every time and you
>would have no need to ever access a transaction log
backup that was
>previously taken. If it has been overwritten, and you
try to restore one
>that is later, SQL send the error about LSNs not being
right.
> Is there something in here that needs to be changed to
accomplish my task?
>CREATE proc RestoreApplicationLogs_TLog
>as
>declare @.position int
>declare @.mediasetid int
>set @.mediasetid = (select media_set_id
>from NCNSV1015.msdb.dbo.backupmediafamily
>where logical_device_name = 'ApplicationLogs_TLog')
>set @.position= (select position from
NCNSV1015.msdb.dbo.backupset
>where Backup_set_id = (select max(backup_set_id) from
>NCNSV1015.msdb.dbo.backupset where media_set_id =@.mediasetid))
>Restore Log ApplicationLogs
>from disk
= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
'
>with file = @.position,
>dbo_only,
>standby
= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
'
>waitfor delay '00:00:10'
>GO
>
>Please help.
>Thank you in advance,
>Deborah
>
>.
>|||I'm sure that the log shipping tool in the Enterprise Edition would work
great, but I do not have Enterprise Edition, nor will we be upgrading to it.
Our environment simply cannot warrant that.
Since the log restores literally take less than a couple of seconds, is
there a way to kick the users out and automatically reconnect them? So that
from their perspective the query just took a couple more seconds to run?
I have not tried to implement replication in SQL2K, and I am concerned about
possible maintenance mistakes. I've been reading up on it in BOL, but I'm
now in a time crunch and don't want to generate any new problems.
Is transactional replication a straight-forward thing to implement? Are
there any issues with adding that to the plan that I need to be aware of?
If I were to implement transactional replication, how would that change the
backup/restore strategy, other than I wouldn't have to restore the
transaction logs every 10 minutes? Can the process be completely automated?
Sorry to be asking so many questions. I have been reading BOL, but reading
the BOL, while usually helpful, is no substitute for talking to someone with
real experience.
Thanks in advance,
Deborah
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
> You need to be using something like replication to meet
> your stated requirements. In order for a db to be restored
> nobody can be accessing it. Log shipping has an option to
> allow users into the db while its not being restored and
> it will kick everony out when the restore is starting, but
> this isnt usually a good idea for a reporting server.
>
> >--Original Message--
> >I am trying to use my Standby Server as a Reporting
> Server so that our
> >resource intensive reporting queries can be run against
> it instead of
> >production.
> >
> >I've been successful at automating the backups and
> restores of all the
> >databases as well as transaction logs from production to
> standby. However,
> >if someone is trying to do a select query at the time, or
> even just have
> >their reporting application connected to it, the restore
> of the transaction
> >log fails "due to not being able to gain exclusive
> access". If one of the
> >transaction log restores fail, they all fail after that
> because the LSNs are
> >off.
> >
> >This is, of course, an urgent problem. Management has
> required that this
> >server be as current as possible for reporting purposes
> and this entire
> >process must be automated.
> >
> >Servers are W2K Server sp4, SQL2K Standard Edition sp3a.
> >
> >Below is an example of my syntax for the transaction log
> restores. Works
> >like a charm unless something is connected to the
> database. I had to do it
> >this way because I wanted to save all the transaction log
> files taken
> >throughout the day in case of "point in time" disaster
> recovery. I ended up
> >opening a ticket with Microsoft and this is the best
> solution they could
> >suggest. They said that normally with log shipping, the
> TLog backups
> >overwrite the others in the device. I found it difficult
> to believe that it
> >was expected that every process would work correctly
> every time and you
> >would have no need to ever access a transaction log
> backup that was
> >previously taken. If it has been overwritten, and you
> try to restore one
> >that is later, SQL send the error about LSNs not being
> right.
> >
> > Is there something in here that needs to be changed to
> accomplish my task?
> >
> >CREATE proc RestoreApplicationLogs_TLog
> >as
> >declare @.position int
> >declare @.mediasetid int
> >
> >set @.mediasetid = (select media_set_id
> >from NCNSV1015.msdb.dbo.backupmediafamily
> >where logical_device_name = 'ApplicationLogs_TLog')
> >
> >set @.position= (select position from
> NCNSV1015.msdb.dbo.backupset
> >where Backup_set_id = (select max(backup_set_id) from
> >NCNSV1015.msdb.dbo.backupset where media_set_id => @.mediasetid))
> >
> >Restore Log ApplicationLogs
> >from disk
> = 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
> '
> >with file = @.position,
> >dbo_only,
> >standby
> = 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
> '
> >
> >waitfor delay '00:00:10'
> >GO
> >
> >
> >Please help.
> >
> >Thank you in advance,
> >Deborah
> >
> >
> >.
> >|||You might want to check out the log shipping scripts that
come with the book SQL 2K High Availability; they are
pretty close to EE-type functionality (i.e. you will still
get them applied in the right order) and you can customize
them to terminate the users with the ALTER DATABASE dbname
SET SINGLE_USER before applying the tran log and ALTER
DATABASE dbname SET MULTI_USER after. Check BOL for the
exact syntax.
CHapter 7 is dedicated to log shipping.
>--Original Message--
>I'm sure that the log shipping tool in the Enterprise
Edition would work
>great, but I do not have Enterprise Edition, nor will we
be upgrading to it.
>Our environment simply cannot warrant that.
>Since the log restores literally take less than a couple
of seconds, is
>there a way to kick the users out and automatically
reconnect them? So that
>from their perspective the query just took a couple more
seconds to run?
>I have not tried to implement replication in SQL2K, and I
am concerned about
>possible maintenance mistakes. I've been reading up on
it in BOL, but I'm
>now in a time crunch and don't want to generate any new
problems.
>Is transactional replication a straight-forward thing to
implement? Are
>there any issues with adding that to the plan that I need
to be aware of?
>If I were to implement transactional replication, how
would that change the
>backup/restore strategy, other than I wouldn't have to
restore the
>transaction logs every 10 minutes? Can the process be
completely automated?
>Sorry to be asking so many questions. I have been
reading BOL, but reading
>the BOL, while usually helpful, is no substitute for
talking to someone with
>real experience.
>Thanks in advance,
>Deborah
>
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
>> You need to be using something like replication to meet
>> your stated requirements. In order for a db to be
restored
>> nobody can be accessing it. Log shipping has an option
to
>> allow users into the db while its not being restored and
>> it will kick everony out when the restore is starting,
but
>> this isnt usually a good idea for a reporting server.
>>
>> >--Original Message--
>> >I am trying to use my Standby Server as a Reporting
>> Server so that our
>> >resource intensive reporting queries can be run against
>> it instead of
>> >production.
>> >
>> >I've been successful at automating the backups and
>> restores of all the
>> >databases as well as transaction logs from production
to
>> standby. However,
>> >if someone is trying to do a select query at the time,
or
>> even just have
>> >their reporting application connected to it, the
restore
>> of the transaction
>> >log fails "due to not being able to gain exclusive
>> access". If one of the
>> >transaction log restores fail, they all fail after that
>> because the LSNs are
>> >off.
>> >
>> >This is, of course, an urgent problem. Management has
>> required that this
>> >server be as current as possible for reporting purposes
>> and this entire
>> >process must be automated.
>> >
>> >Servers are W2K Server sp4, SQL2K Standard Edition
sp3a.
>> >
>> >Below is an example of my syntax for the transaction
log
>> restores. Works
>> >like a charm unless something is connected to the
>> database. I had to do it
>> >this way because I wanted to save all the transaction
log
>> files taken
>> >throughout the day in case of "point in time" disaster
>> recovery. I ended up
>> >opening a ticket with Microsoft and this is the best
>> solution they could
>> >suggest. They said that normally with log shipping,
the
>> TLog backups
>> >overwrite the others in the device. I found it
difficult
>> to believe that it
>> >was expected that every process would work correctly
>> every time and you
>> >would have no need to ever access a transaction log
>> backup that was
>> >previously taken. If it has been overwritten, and you
>> try to restore one
>> >that is later, SQL send the error about LSNs not being
>> right.
>> >
>> > Is there something in here that needs to be changed to
>> accomplish my task?
>> >
>> >CREATE proc RestoreApplicationLogs_TLog
>> >as
>> >declare @.position int
>> >declare @.mediasetid int
>> >
>> >set @.mediasetid = (select media_set_id
>> >from NCNSV1015.msdb.dbo.backupmediafamily
>> >where logical_device_name = 'ApplicationLogs_TLog')
>> >
>> >set @.position= (select position from
>> NCNSV1015.msdb.dbo.backupset
>> >where Backup_set_id = (select max(backup_set_id) from
>> >NCNSV1015.msdb.dbo.backupset where media_set_id =>> @.mediasetid))
>> >
>> >Restore Log ApplicationLogs
>> >from disk
= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
>> '
>> >with file = @.position,
>> >dbo_only,
>> >standby
= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
>> '
>> >
>> >waitfor delay '00:00:10'
>> >GO
>> >
>> >
>> >Please help.
>> >
>> >Thank you in advance,
>> >Deborah
>> >
>> >
>> >.
>> >
>
>.
>|||>Since the log restores literally take less than a couple
of seconds, is
>there a way to kick the users out and automatically
reconnect them? So that
>from their perspective the query just took a couple more
seconds to run?
Not that I know of. Someone else?
>but I'm
>now in a time crunch and don't want to generate any new
problems.
>Is transactional replication a straight-forward thing to
implement? Are
>there any issues with adding that to the plan that I need
to be aware of?
>If I were to implement transactional replication, how
would that change the
>backup/restore strategy, other than I wouldn't have to
restore the
>transaction logs every 10 minutes? Can the process be
completely automated?
Replication from my experience had somewhat of a steep
learning curve. It really depends on your circumstances
though. Security/ firewalls/ authentication/ disater
recovery scenarios/ size of initial snapshot/ transferring
of logins and jobs/ alloted maintenance time/ the
infamous "schema changes to a published table"/ and lets
not forget the all time favorite "identity colmuns in
replication"/ ect. If you are on that much of a time
crunch this may not be your best approach.
Out of curiosity, how did you suddenly wind up in this
truely bogus situation?
>--Original Message--
>I'm sure that the log shipping tool in the Enterprise
Edition would work
>great, but I do not have Enterprise Edition, nor will we
be upgrading to it.
>Our environment simply cannot warrant that.
>Since the log restores literally take less than a couple
of seconds, is
>there a way to kick the users out and automatically
reconnect them? So that
>from their perspective the query just took a couple more
seconds to run?
>I have not tried to implement replication in SQL2K, and I
am concerned about
>possible maintenance mistakes. I've been reading up on
it in BOL, but I'm
>now in a time crunch and don't want to generate any new
problems.
>Is transactional replication a straight-forward thing to
implement? Are
>there any issues with adding that to the plan that I need
to be aware of?
>If I were to implement transactional replication, how
would that change the
>backup/restore strategy, other than I wouldn't have to
restore the
>transaction logs every 10 minutes? Can the process be
completely automated?
>Sorry to be asking so many questions. I have been
reading BOL, but reading
>the BOL, while usually helpful, is no substitute for
talking to someone with
>real experience.
>Thanks in advance,
>Deborah
>
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
>> You need to be using something like replication to meet
>> your stated requirements. In order for a db to be
restored
>> nobody can be accessing it. Log shipping has an option
to
>> allow users into the db while its not being restored and
>> it will kick everony out when the restore is starting,
but
>> this isnt usually a good idea for a reporting server.
>>
>> >--Original Message--
>> >I am trying to use my Standby Server as a Reporting
>> Server so that our
>> >resource intensive reporting queries can be run against
>> it instead of
>> >production.
>> >
>> >I've been successful at automating the backups and
>> restores of all the
>> >databases as well as transaction logs from production
to
>> standby. However,
>> >if someone is trying to do a select query at the time,
or
>> even just have
>> >their reporting application connected to it, the
restore
>> of the transaction
>> >log fails "due to not being able to gain exclusive
>> access". If one of the
>> >transaction log restores fail, they all fail after that
>> because the LSNs are
>> >off.
>> >
>> >This is, of course, an urgent problem. Management has
>> required that this
>> >server be as current as possible for reporting purposes
>> and this entire
>> >process must be automated.
>> >
>> >Servers are W2K Server sp4, SQL2K Standard Edition
sp3a.
>> >
>> >Below is an example of my syntax for the transaction
log
>> restores. Works
>> >like a charm unless something is connected to the
>> database. I had to do it
>> >this way because I wanted to save all the transaction
log
>> files taken
>> >throughout the day in case of "point in time" disaster
>> recovery. I ended up
>> >opening a ticket with Microsoft and this is the best
>> solution they could
>> >suggest. They said that normally with log shipping,
the
>> TLog backups
>> >overwrite the others in the device. I found it
difficult
>> to believe that it
>> >was expected that every process would work correctly
>> every time and you
>> >would have no need to ever access a transaction log
>> backup that was
>> >previously taken. If it has been overwritten, and you
>> try to restore one
>> >that is later, SQL send the error about LSNs not being
>> right.
>> >
>> > Is there something in here that needs to be changed to
>> accomplish my task?
>> >
>> >CREATE proc RestoreApplicationLogs_TLog
>> >as
>> >declare @.position int
>> >declare @.mediasetid int
>> >
>> >set @.mediasetid = (select media_set_id
>> >from NCNSV1015.msdb.dbo.backupmediafamily
>> >where logical_device_name = 'ApplicationLogs_TLog')
>> >
>> >set @.position= (select position from
>> NCNSV1015.msdb.dbo.backupset
>> >where Backup_set_id = (select max(backup_set_id) from
>> >NCNSV1015.msdb.dbo.backupset where media_set_id =>> @.mediasetid))
>> >
>> >Restore Log ApplicationLogs
>> >from disk
= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
>> '
>> >with file = @.position,
>> >dbo_only,
>> >standby
= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
>> '
>> >
>> >waitfor delay '00:00:10'
>> >GO
>> >
>> >
>> >Please help.
>> >
>> >Thank you in advance,
>> >Deborah
>> >
>> >
>> >.
>> >
>
>.
>|||Well, clearly I have run into this bogus situation due to not considering
the fact the I couldn't do log restores while someone is connected to the
database. I mean, I knew that database restores required exclusive access,
but somehow I thought that a read-only database would allow transaction log
restores while others are connected to the database. This would be a major
oops on my part. I had originally suggested to management that the data be
synchronized only once or twice a day, but they firmly want a solution that
allows them to off-load some of the decision support queries/reports that
they use to be retrieved from this "standby" server, in order to reduce the
resource load on production. This has always been touted as an advantage to
having a standby server.
It has taken weeks to iron out the bugs that have popped up in automating
the backup and restore of 36 databases. Everything from permissions issues
to invalid syntax to just plain old timing problems. Now that has been
running successfully all week and they are ready to point these apps to that
server. I was testing that and began to see my restore log jobs failing, of
course.
So, at this point, I don't know quite what I'm going to have to do in order
to get the desired results. Disappointing is not quite the word I would use
to describe the situation if I have to tell them that I now have to take
some considerable time to create and test a replication scenario. But, if
it is the ONLY way, then I guess that's just what I'll have to do.
I am mentally exhausted and in desperate need of a workable solution. Its
been at least 2 years since I've used any type of replication in production,
and I am more than a bit fuzzy on what will have to be done for successful
implementation.
Ok, guess I've over-explained :-) Thanks for listening.
Deborah
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
> >Since the log restores literally take less than a couple
> of seconds, is
> >there a way to kick the users out and automatically
> reconnect them? So that
> >from their perspective the query just took a couple more
> seconds to run?
> Not that I know of. Someone else?
> >but I'm
> >now in a time crunch and don't want to generate any new
> problems.
> >
> >Is transactional replication a straight-forward thing to
> implement? Are
> >there any issues with adding that to the plan that I need
> to be aware of?
> >If I were to implement transactional replication, how
> would that change the
> >backup/restore strategy, other than I wouldn't have to
> restore the
> >transaction logs every 10 minutes? Can the process be
> completely automated?
>
> Replication from my experience had somewhat of a steep
> learning curve. It really depends on your circumstances
> though. Security/ firewalls/ authentication/ disater
> recovery scenarios/ size of initial snapshot/ transferring
> of logins and jobs/ alloted maintenance time/ the
> infamous "schema changes to a published table"/ and lets
> not forget the all time favorite "identity colmuns in
> replication"/ ect. If you are on that much of a time
> crunch this may not be your best approach.
>
> Out of curiosity, how did you suddenly wind up in this
> truely bogus situation?
>
> >--Original Message--
> >I'm sure that the log shipping tool in the Enterprise
> Edition would work
> >great, but I do not have Enterprise Edition, nor will we
> be upgrading to it.
> >Our environment simply cannot warrant that.
> >
> >Since the log restores literally take less than a couple
> of seconds, is
> >there a way to kick the users out and automatically
> reconnect them? So that
> >from their perspective the query just took a couple more
> seconds to run?
> >
> >I have not tried to implement replication in SQL2K, and I
> am concerned about
> >possible maintenance mistakes. I've been reading up on
> it in BOL, but I'm
> >now in a time crunch and don't want to generate any new
> problems.
> >
> >Is transactional replication a straight-forward thing to
> implement? Are
> >there any issues with adding that to the plan that I need
> to be aware of?
> >If I were to implement transactional replication, how
> would that change the
> >backup/restore strategy, other than I wouldn't have to
> restore the
> >transaction logs every 10 minutes? Can the process be
> completely automated?
> >
> >Sorry to be asking so many questions. I have been
> reading BOL, but reading
> >the BOL, while usually helpful, is no substitute for
> talking to someone with
> >real experience.
> >
> >Thanks in advance,
> >Deborah
> >
> >
> >
> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
> >> You need to be using something like replication to meet
> >> your stated requirements. In order for a db to be
> restored
> >> nobody can be accessing it. Log shipping has an option
> to
> >> allow users into the db while its not being restored and
> >> it will kick everony out when the restore is starting,
> but
> >> this isnt usually a good idea for a reporting server.
> >>
> >>
> >> >--Original Message--
> >> >I am trying to use my Standby Server as a Reporting
> >> Server so that our
> >> >resource intensive reporting queries can be run against
> >> it instead of
> >> >production.
> >> >
> >> >I've been successful at automating the backups and
> >> restores of all the
> >> >databases as well as transaction logs from production
> to
> >> standby. However,
> >> >if someone is trying to do a select query at the time,
> or
> >> even just have
> >> >their reporting application connected to it, the
> restore
> >> of the transaction
> >> >log fails "due to not being able to gain exclusive
> >> access". If one of the
> >> >transaction log restores fail, they all fail after that
> >> because the LSNs are
> >> >off.
> >> >
> >> >This is, of course, an urgent problem. Management has
> >> required that this
> >> >server be as current as possible for reporting purposes
> >> and this entire
> >> >process must be automated.
> >> >
> >> >Servers are W2K Server sp4, SQL2K Standard Edition
> sp3a.
> >> >
> >> >Below is an example of my syntax for the transaction
> log
> >> restores. Works
> >> >like a charm unless something is connected to the
> >> database. I had to do it
> >> >this way because I wanted to save all the transaction
> log
> >> files taken
> >> >throughout the day in case of "point in time" disaster
> >> recovery. I ended up
> >> >opening a ticket with Microsoft and this is the best
> >> solution they could
> >> >suggest. They said that normally with log shipping,
> the
> >> TLog backups
> >> >overwrite the others in the device. I found it
> difficult
> >> to believe that it
> >> >was expected that every process would work correctly
> >> every time and you
> >> >would have no need to ever access a transaction log
> >> backup that was
> >> >previously taken. If it has been overwritten, and you
> >> try to restore one
> >> >that is later, SQL send the error about LSNs not being
> >> right.
> >> >
> >> > Is there something in here that needs to be changed to
> >> accomplish my task?
> >> >
> >> >CREATE proc RestoreApplicationLogs_TLog
> >> >as
> >> >declare @.position int
> >> >declare @.mediasetid int
> >> >
> >> >set @.mediasetid = (select media_set_id
> >> >from NCNSV1015.msdb.dbo.backupmediafamily
> >> >where logical_device_name = 'ApplicationLogs_TLog')
> >> >
> >> >set @.position= (select position from
> >> NCNSV1015.msdb.dbo.backupset
> >> >where Backup_set_id = (select max(backup_set_id) from
> >> >NCNSV1015.msdb.dbo.backupset where media_set_id => >> @.mediasetid))
> >> >
> >> >Restore Log ApplicationLogs
> >> >from disk
> >>
> = 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
> >> '
> >> >with file = @.position,
> >> >dbo_only,
> >> >standby
> >>
> = 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
> >> '
> >> >
> >> >waitfor delay '00:00:10'
> >> >GO
> >> >
> >> >
> >> >Please help.
> >> >
> >> >Thank you in advance,
> >> >Deborah
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||You can kick users out of a database... Tibor Karaszi has some utility
procedures on his web site... www.sqlmaint.com..
One of those will kill all connections to a given database... You can not
however re-connect automatically.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Deborah Bohannon" <dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
in message news:#Qz7b3BwDHA.2260@.TK2MSFTNGP09.phx.gbl...
> Well, clearly I have run into this bogus situation due to not considering
> the fact the I couldn't do log restores while someone is connected to the
> database. I mean, I knew that database restores required exclusive
access,
> but somehow I thought that a read-only database would allow transaction
log
> restores while others are connected to the database. This would be a
major
> oops on my part. I had originally suggested to management that the data
be
> synchronized only once or twice a day, but they firmly want a solution
that
> allows them to off-load some of the decision support queries/reports that
> they use to be retrieved from this "standby" server, in order to reduce
the
> resource load on production. This has always been touted as an advantage
to
> having a standby server.
> It has taken weeks to iron out the bugs that have popped up in automating
> the backup and restore of 36 databases. Everything from permissions
issues
> to invalid syntax to just plain old timing problems. Now that has been
> running successfully all week and they are ready to point these apps to
that
> server. I was testing that and began to see my restore log jobs failing,
of
> course.
> So, at this point, I don't know quite what I'm going to have to do in
order
> to get the desired results. Disappointing is not quite the word I would
use
> to describe the situation if I have to tell them that I now have to take
> some considerable time to create and test a replication scenario. But, if
> it is the ONLY way, then I guess that's just what I'll have to do.
> I am mentally exhausted and in desperate need of a workable solution. Its
> been at least 2 years since I've used any type of replication in
production,
> and I am more than a bit fuzzy on what will have to be done for successful
> implementation.
> Ok, guess I've over-explained :-) Thanks for listening.
> Deborah
>
> "chris" <anonymous@.discussions.microsoft.com> wrote in message
> news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
> > >Since the log restores literally take less than a couple
> > of seconds, is
> > >there a way to kick the users out and automatically
> > reconnect them? So that
> > >from their perspective the query just took a couple more
> > seconds to run?
> >
> > Not that I know of. Someone else?
> >
> > >but I'm
> > >now in a time crunch and don't want to generate any new
> > problems.
> > >
> > >Is transactional replication a straight-forward thing to
> > implement? Are
> > >there any issues with adding that to the plan that I need
> > to be aware of?
> > >If I were to implement transactional replication, how
> > would that change the
> > >backup/restore strategy, other than I wouldn't have to
> > restore the
> > >transaction logs every 10 minutes? Can the process be
> > completely automated?
> >
> >
> > Replication from my experience had somewhat of a steep
> > learning curve. It really depends on your circumstances
> > though. Security/ firewalls/ authentication/ disater
> > recovery scenarios/ size of initial snapshot/ transferring
> > of logins and jobs/ alloted maintenance time/ the
> > infamous "schema changes to a published table"/ and lets
> > not forget the all time favorite "identity colmuns in
> > replication"/ ect. If you are on that much of a time
> > crunch this may not be your best approach.
> >
> >
> > Out of curiosity, how did you suddenly wind up in this
> > truely bogus situation?
> >
> >
> > >--Original Message--
> > >I'm sure that the log shipping tool in the Enterprise
> > Edition would work
> > >great, but I do not have Enterprise Edition, nor will we
> > be upgrading to it.
> > >Our environment simply cannot warrant that.
> > >
> > >Since the log restores literally take less than a couple
> > of seconds, is
> > >there a way to kick the users out and automatically
> > reconnect them? So that
> > >from their perspective the query just took a couple more
> > seconds to run?
> > >
> > >I have not tried to implement replication in SQL2K, and I
> > am concerned about
> > >possible maintenance mistakes. I've been reading up on
> > it in BOL, but I'm
> > >now in a time crunch and don't want to generate any new
> > problems.
> > >
> > >Is transactional replication a straight-forward thing to
> > implement? Are
> > >there any issues with adding that to the plan that I need
> > to be aware of?
> > >If I were to implement transactional replication, how
> > would that change the
> > >backup/restore strategy, other than I wouldn't have to
> > restore the
> > >transaction logs every 10 minutes? Can the process be
> > completely automated?
> > >
> > >Sorry to be asking so many questions. I have been
> > reading BOL, but reading
> > >the BOL, while usually helpful, is no substitute for
> > talking to someone with
> > >real experience.
> > >
> > >Thanks in advance,
> > >Deborah
> > >
> > >
> > >
> > >"chris" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
> > >> You need to be using something like replication to meet
> > >> your stated requirements. In order for a db to be
> > restored
> > >> nobody can be accessing it. Log shipping has an option
> > to
> > >> allow users into the db while its not being restored and
> > >> it will kick everony out when the restore is starting,
> > but
> > >> this isnt usually a good idea for a reporting server.
> > >>
> > >>
> > >> >--Original Message--
> > >> >I am trying to use my Standby Server as a Reporting
> > >> Server so that our
> > >> >resource intensive reporting queries can be run against
> > >> it instead of
> > >> >production.
> > >> >
> > >> >I've been successful at automating the backups and
> > >> restores of all the
> > >> >databases as well as transaction logs from production
> > to
> > >> standby. However,
> > >> >if someone is trying to do a select query at the time,
> > or
> > >> even just have
> > >> >their reporting application connected to it, the
> > restore
> > >> of the transaction
> > >> >log fails "due to not being able to gain exclusive
> > >> access". If one of the
> > >> >transaction log restores fail, they all fail after that
> > >> because the LSNs are
> > >> >off.
> > >> >
> > >> >This is, of course, an urgent problem. Management has
> > >> required that this
> > >> >server be as current as possible for reporting purposes
> > >> and this entire
> > >> >process must be automated.
> > >> >
> > >> >Servers are W2K Server sp4, SQL2K Standard Edition
> > sp3a.
> > >> >
> > >> >Below is an example of my syntax for the transaction
> > log
> > >> restores. Works
> > >> >like a charm unless something is connected to the
> > >> database. I had to do it
> > >> >this way because I wanted to save all the transaction
> > log
> > >> files taken
> > >> >throughout the day in case of "point in time" disaster
> > >> recovery. I ended up
> > >> >opening a ticket with Microsoft and this is the best
> > >> solution they could
> > >> >suggest. They said that normally with log shipping,
> > the
> > >> TLog backups
> > >> >overwrite the others in the device. I found it
> > difficult
> > >> to believe that it
> > >> >was expected that every process would work correctly
> > >> every time and you
> > >> >would have no need to ever access a transaction log
> > >> backup that was
> > >> >previously taken. If it has been overwritten, and you
> > >> try to restore one
> > >> >that is later, SQL send the error about LSNs not being
> > >> right.
> > >> >
> > >> > Is there something in here that needs to be changed to
> > >> accomplish my task?
> > >> >
> > >> >CREATE proc RestoreApplicationLogs_TLog
> > >> >as
> > >> >declare @.position int
> > >> >declare @.mediasetid int
> > >> >
> > >> >set @.mediasetid = (select media_set_id
> > >> >from NCNSV1015.msdb.dbo.backupmediafamily
> > >> >where logical_device_name = 'ApplicationLogs_TLog')
> > >> >
> > >> >set @.position= (select position from
> > >> NCNSV1015.msdb.dbo.backupset
> > >> >where Backup_set_id = (select max(backup_set_id) from
> > >> >NCNSV1015.msdb.dbo.backupset where media_set_id => > >> @.mediasetid))
> > >> >
> > >> >Restore Log ApplicationLogs
> > >> >from disk
> > >>
> > = 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
> > >> '
> > >> >with file = @.position,
> > >> >dbo_only,
> > >> >standby
> > >>
> > = 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
> > >> '
> > >> >
> > >> >waitfor delay '00:00:10'
> > >> >GO
> > >> >
> > >> >
> > >> >Please help.
> > >> >
> > >> >Thank you in advance,
> > >> >Deborah
> > >> >
> > >> >
> > >> >.
> > >> >
> > >
> > >
> > >.
> > >
>|||I posted the syntax above ... it's just switches of the
ALTER DATABASE command. True, you have the app
reconnection problem, but hey, it keeps you in business.
This is the way backup/restore has worked historically in
SQL Server. It makes sense, as you are making sure
nothing can possibly interfere with the restore process.
I understand your dilemma, and I've had this hard
discussion with many of my clients in the past. It's a
common misunderstanding that if you put your db in
STANDBY, you always have read-only access. It's true if
you never apply a tran log.
Now, if you could possibly convince them that the
reporting server will be a delta of time out (say a few
hours) and apply a bunch of TLs all at once at known
times, it may help here.
>--Original Message--
>You can kick users out of a database... Tibor Karaszi has
some utility
>procedures on his web site... www.sqlmaint.com..
>One of those will kill all connections to a given
database... You can not
>however re-connect automatically.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Deborah Bohannon"
<dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
>in message news:#Qz7b3BwDHA.2260@.TK2MSFTNGP09.phx.gbl...
>> Well, clearly I have run into this bogus situation due
to not considering
>> the fact the I couldn't do log restores while someone
is connected to the
>> database. I mean, I knew that database restores
required exclusive
>access,
>> but somehow I thought that a read-only database would
allow transaction
>log
>> restores while others are connected to the database.
This would be a
>major
>> oops on my part. I had originally suggested to
management that the data
>be
>> synchronized only once or twice a day, but they firmly
want a solution
>that
>> allows them to off-load some of the decision support
queries/reports that
>> they use to be retrieved from this "standby" server, in
order to reduce
>the
>> resource load on production. This has always been
touted as an advantage
>to
>> having a standby server.
>> It has taken weeks to iron out the bugs that have
popped up in automating
>> the backup and restore of 36 databases. Everything
from permissions
>issues
>> to invalid syntax to just plain old timing problems.
Now that has been
>> running successfully all week and they are ready to
point these apps to
>that
>> server. I was testing that and began to see my restore
log jobs failing,
>of
>> course.
>> So, at this point, I don't know quite what I'm going to
have to do in
>order
>> to get the desired results. Disappointing is not quite
the word I would
>use
>> to describe the situation if I have to tell them that I
now have to take
>> some considerable time to create and test a replication
scenario. But, if
>> it is the ONLY way, then I guess that's just what I'll
have to do.
>> I am mentally exhausted and in desperate need of a
workable solution. Its
>> been at least 2 years since I've used any type of
replication in
>production,
>> and I am more than a bit fuzzy on what will have to be
done for successful
>> implementation.
>> Ok, guess I've over-explained :-) Thanks for listening.
>> Deborah
>>
>> "chris" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
>> > >Since the log restores literally take less than a
couple
>> > of seconds, is
>> > >there a way to kick the users out and automatically
>> > reconnect them? So that
>> > >from their perspective the query just took a couple
more
>> > seconds to run?
>> >
>> > Not that I know of. Someone else?
>> >
>> > >but I'm
>> > >now in a time crunch and don't want to generate any
new
>> > problems.
>> > >
>> > >Is transactional replication a straight-forward
thing to
>> > implement? Are
>> > >there any issues with adding that to the plan that I
need
>> > to be aware of?
>> > >If I were to implement transactional replication, how
>> > would that change the
>> > >backup/restore strategy, other than I wouldn't have
to
>> > restore the
>> > >transaction logs every 10 minutes? Can the process
be
>> > completely automated?
>> >
>> >
>> > Replication from my experience had somewhat of a steep
>> > learning curve. It really depends on your
circumstances
>> > though. Security/ firewalls/ authentication/ disater
>> > recovery scenarios/ size of initial snapshot/
transferring
>> > of logins and jobs/ alloted maintenance time/ the
>> > infamous "schema changes to a published table"/ and
lets
>> > not forget the all time favorite "identity colmuns in
>> > replication"/ ect. If you are on that much of a time
>> > crunch this may not be your best approach.
>> >
>> >
>> > Out of curiosity, how did you suddenly wind up in this
>> > truely bogus situation?
>> >
>> >
>> > >--Original Message--
>> > >I'm sure that the log shipping tool in the Enterprise
>> > Edition would work
>> > >great, but I do not have Enterprise Edition, nor
will we
>> > be upgrading to it.
>> > >Our environment simply cannot warrant that.
>> > >
>> > >Since the log restores literally take less than a
couple
>> > of seconds, is
>> > >there a way to kick the users out and automatically
>> > reconnect them? So that
>> > >from their perspective the query just took a couple
more
>> > seconds to run?
>> > >
>> > >I have not tried to implement replication in SQL2K,
and I
>> > am concerned about
>> > >possible maintenance mistakes. I've been reading up
on
>> > it in BOL, but I'm
>> > >now in a time crunch and don't want to generate any
new
>> > problems.
>> > >
>> > >Is transactional replication a straight-forward
thing to
>> > implement? Are
>> > >there any issues with adding that to the plan that I
need
>> > to be aware of?
>> > >If I were to implement transactional replication, how
>> > would that change the
>> > >backup/restore strategy, other than I wouldn't have
to
>> > restore the
>> > >transaction logs every 10 minutes? Can the process
be
>> > completely automated?
>> > >
>> > >Sorry to be asking so many questions. I have been
>> > reading BOL, but reading
>> > >the BOL, while usually helpful, is no substitute for
>> > talking to someone with
>> > >real experience.
>> > >
>> > >Thanks in advance,
>> > >Deborah
>> > >
>> > >
>> > >
>> > >"chris" <anonymous@.discussions.microsoft.com> wrote
in
>> > message
>> > >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
>> > >> You need to be using something like replication to
meet
>> > >> your stated requirements. In order for a db to be
>> > restored
>> > >> nobody can be accessing it. Log shipping has an
option
>> > to
>> > >> allow users into the db while its not being
restored and
>> > >> it will kick everony out when the restore is
starting,
>> > but
>> > >> this isnt usually a good idea for a reporting
server.
>> > >>
>> > >>
>> > >> >--Original Message--
>> > >> >I am trying to use my Standby Server as a
Reporting
>> > >> Server so that our
>> > >> >resource intensive reporting queries can be run
against
>> > >> it instead of
>> > >> >production.
>> > >> >
>> > >> >I've been successful at automating the backups and
>> > >> restores of all the
>> > >> >databases as well as transaction logs from
production
>> > to
>> > >> standby. However,
>> > >> >if someone is trying to do a select query at the
time,
>> > or
>> > >> even just have
>> > >> >their reporting application connected to it, the
>> > restore
>> > >> of the transaction
>> > >> >log fails "due to not being able to gain exclusive
>> > >> access". If one of the
>> > >> >transaction log restores fail, they all fail
after that
>> > >> because the LSNs are
>> > >> >off.
>> > >> >
>> > >> >This is, of course, an urgent problem.
Management has
>> > >> required that this
>> > >> >server be as current as possible for reporting
purposes
>> > >> and this entire
>> > >> >process must be automated.
>> > >> >
>> > >> >Servers are W2K Server sp4, SQL2K Standard Edition
>> > sp3a.
>> > >> >
>> > >> >Below is an example of my syntax for the
transaction
>> > log
>> > >> restores. Works
>> > >> >like a charm unless something is connected to the
>> > >> database. I had to do it
>> > >> >this way because I wanted to save all the
transaction
>> > log
>> > >> files taken
>> > >> >throughout the day in case of "point in time"
disaster
>> > >> recovery. I ended up
>> > >> >opening a ticket with Microsoft and this is the
best
>> > >> solution they could
>> > >> >suggest. They said that normally with log
shipping,
>> > the
>> > >> TLog backups
>> > >> >overwrite the others in the device. I found it
>> > difficult
>> > >> to believe that it
>> > >> >was expected that every process would work
correctly
>> > >> every time and you
>> > >> >would have no need to ever access a transaction
log
>> > >> backup that was
>> > >> >previously taken. If it has been overwritten,
and you
>> > >> try to restore one
>> > >> >that is later, SQL send the error about LSNs not
being
>> > >> right.
>> > >> >
>> > >> > Is there something in here that needs to be
changed to
>> > >> accomplish my task?
>> > >> >
>> > >> >CREATE proc RestoreApplicationLogs_TLog
>> > >> >as
>> > >> >declare @.position int
>> > >> >declare @.mediasetid int
>> > >> >
>> > >> >set @.mediasetid = (select media_set_id
>> > >> >from NCNSV1015.msdb.dbo.backupmediafamily
>> > >> >where logical_device_name
= 'ApplicationLogs_TLog')
>> > >> >
>> > >> >set @.position= (select position from
>> > >> NCNSV1015.msdb.dbo.backupset
>> > >> >where Backup_set_id = (select max(backup_set_id)
from
>> > >> >NCNSV1015.msdb.dbo.backupset where media_set_id =>> > >> @.mediasetid))
>> > >> >
>> > >> >Restore Log ApplicationLogs
>> > >> >from disk
>> > >>
>> >
= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
>> > >> '
>> > >> >with file = @.position,
>> > >> >dbo_only,
>> > >> >standby
>> > >>
>> >
= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
>> > >> '
>> > >> >
>> > >> >waitfor delay '00:00:10'
>> > >> >GO
>> > >> >
>> > >> >
>> > >> >Please help.
>> > >> >
>> > >> >Thank you in advance,
>> > >> >Deborah
>> > >> >
>> > >> >
>> > >> >.
>> > >> >
>> > >
>> > >
>> > >.
>> > >
>>
>
>.
>|||Perhaps I can somehow have it written into the connection of their app to
the database that it reconnects whenever they run a "query" and disconnects
after. Then the transaction log restore process can "retry" a few times
until it is successful.
I'm not sure if that is possible or what kind of performance issues that
would bring up (as pertains to their reporting), but it might be another way
to address this problem.
It seems I have led them astray with the idea that using the warm standby
server could actually be used as a reporting server. At this point, I will
need to adjust their expectations. My head is getting bruised from all the
hitting it against the wall I've done on what was supposed to be a fairly
simple project :-)
We simply cannot afford to have a 3rd server that is available for use as a
reporting server. It was a stretch to convince them we needed a standby
server at all. It was only after the production server went down and 50
people were standing around for almost a day, not being productive, that it
became a priority. The real sell for them was the idea of off-loading some
of the reporting while also having a warm backup.
Thank you all for all your help.
Deborah
"Allan Hirt" <allanh@.NOSPAMavanade.com> wrote in message
news:02a301c3c02d$833cd850$a501280a@.phx.gbl...
> I posted the syntax above ... it's just switches of the
> ALTER DATABASE command. True, you have the app
> reconnection problem, but hey, it keeps you in business.
> This is the way backup/restore has worked historically in
> SQL Server. It makes sense, as you are making sure
> nothing can possibly interfere with the restore process.
> I understand your dilemma, and I've had this hard
> discussion with many of my clients in the past. It's a
> common misunderstanding that if you put your db in
> STANDBY, you always have read-only access. It's true if
> you never apply a tran log.
> Now, if you could possibly convince them that the
> reporting server will be a delta of time out (say a few
> hours) and apply a bunch of TLs all at once at known
> times, it may help here.
> >--Original Message--
> >You can kick users out of a database... Tibor Karaszi has
> some utility
> >procedures on his web site... www.sqlmaint.com..
> >
> >One of those will kill all connections to a given
> database... You can not
> >however re-connect automatically.
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >"Deborah Bohannon"
> <dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
> >in message news:#Qz7b3BwDHA.2260@.TK2MSFTNGP09.phx.gbl...
> >> Well, clearly I have run into this bogus situation due
> to not considering
> >> the fact the I couldn't do log restores while someone
> is connected to the
> >> database. I mean, I knew that database restores
> required exclusive
> >access,
> >> but somehow I thought that a read-only database would
> allow transaction
> >log
> >> restores while others are connected to the database.
> This would be a
> >major
> >> oops on my part. I had originally suggested to
> management that the data
> >be
> >> synchronized only once or twice a day, but they firmly
> want a solution
> >that
> >> allows them to off-load some of the decision support
> queries/reports that
> >> they use to be retrieved from this "standby" server, in
> order to reduce
> >the
> >> resource load on production. This has always been
> touted as an advantage
> >to
> >> having a standby server.
> >>
> >> It has taken weeks to iron out the bugs that have
> popped up in automating
> >> the backup and restore of 36 databases. Everything
> from permissions
> >issues
> >> to invalid syntax to just plain old timing problems.
> Now that has been
> >> running successfully all week and they are ready to
> point these apps to
> >that
> >> server. I was testing that and began to see my restore
> log jobs failing,
> >of
> >> course.
> >>
> >> So, at this point, I don't know quite what I'm going to
> have to do in
> >order
> >> to get the desired results. Disappointing is not quite
> the word I would
> >use
> >> to describe the situation if I have to tell them that I
> now have to take
> >> some considerable time to create and test a replication
> scenario. But, if
> >> it is the ONLY way, then I guess that's just what I'll
> have to do.
> >>
> >> I am mentally exhausted and in desperate need of a
> workable solution. Its
> >> been at least 2 years since I've used any type of
> replication in
> >production,
> >> and I am more than a bit fuzzy on what will have to be
> done for successful
> >> implementation.
> >>
> >> Ok, guess I've over-explained :-) Thanks for listening.
> >> Deborah
> >>
> >>
> >> "chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >> news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
> >> > >Since the log restores literally take less than a
> couple
> >> > of seconds, is
> >> > >there a way to kick the users out and automatically
> >> > reconnect them? So that
> >> > >from their perspective the query just took a couple
> more
> >> > seconds to run?
> >> >
> >> > Not that I know of. Someone else?
> >> >
> >> > >but I'm
> >> > >now in a time crunch and don't want to generate any
> new
> >> > problems.
> >> > >
> >> > >Is transactional replication a straight-forward
> thing to
> >> > implement? Are
> >> > >there any issues with adding that to the plan that I
> need
> >> > to be aware of?
> >> > >If I were to implement transactional replication, how
> >> > would that change the
> >> > >backup/restore strategy, other than I wouldn't have
> to
> >> > restore the
> >> > >transaction logs every 10 minutes? Can the process
> be
> >> > completely automated?
> >> >
> >> >
> >> > Replication from my experience had somewhat of a steep
> >> > learning curve. It really depends on your
> circumstances
> >> > though. Security/ firewalls/ authentication/ disater
> >> > recovery scenarios/ size of initial snapshot/
> transferring
> >> > of logins and jobs/ alloted maintenance time/ the
> >> > infamous "schema changes to a published table"/ and
> lets
> >> > not forget the all time favorite "identity colmuns in
> >> > replication"/ ect. If you are on that much of a time
> >> > crunch this may not be your best approach.
> >> >
> >> >
> >> > Out of curiosity, how did you suddenly wind up in this
> >> > truely bogus situation?
> >> >
> >> >
> >> > >--Original Message--
> >> > >I'm sure that the log shipping tool in the Enterprise
> >> > Edition would work
> >> > >great, but I do not have Enterprise Edition, nor
> will we
> >> > be upgrading to it.
> >> > >Our environment simply cannot warrant that.
> >> > >
> >> > >Since the log restores literally take less than a
> couple
> >> > of seconds, is
> >> > >there a way to kick the users out and automatically
> >> > reconnect them? So that
> >> > >from their perspective the query just took a couple
> more
> >> > seconds to run?
> >> > >
> >> > >I have not tried to implement replication in SQL2K,
> and I
> >> > am concerned about
> >> > >possible maintenance mistakes. I've been reading up
> on
> >> > it in BOL, but I'm
> >> > >now in a time crunch and don't want to generate any
> new
> >> > problems.
> >> > >
> >> > >Is transactional replication a straight-forward
> thing to
> >> > implement? Are
> >> > >there any issues with adding that to the plan that I
> need
> >> > to be aware of?
> >> > >If I were to implement transactional replication, how
> >> > would that change the
> >> > >backup/restore strategy, other than I wouldn't have
> to
> >> > restore the
> >> > >transaction logs every 10 minutes? Can the process
> be
> >> > completely automated?
> >> > >
> >> > >Sorry to be asking so many questions. I have been
> >> > reading BOL, but reading
> >> > >the BOL, while usually helpful, is no substitute for
> >> > talking to someone with
> >> > >real experience.
> >> > >
> >> > >Thanks in advance,
> >> > >Deborah
> >> > >
> >> > >
> >> > >
> >> > >"chris" <anonymous@.discussions.microsoft.com> wrote
> in
> >> > message
> >> > >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
> >> > >> You need to be using something like replication to
> meet
> >> > >> your stated requirements. In order for a db to be
> >> > restored
> >> > >> nobody can be accessing it. Log shipping has an
> option
> >> > to
> >> > >> allow users into the db while its not being
> restored and
> >> > >> it will kick everony out when the restore is
> starting,
> >> > but
> >> > >> this isnt usually a good idea for a reporting
> server.
> >> > >>
> >> > >>
> >> > >> >--Original Message--
> >> > >> >I am trying to use my Standby Server as a
> Reporting
> >> > >> Server so that our
> >> > >> >resource intensive reporting queries can be run
> against
> >> > >> it instead of
> >> > >> >production.
> >> > >> >
> >> > >> >I've been successful at automating the backups and
> >> > >> restores of all the
> >> > >> >databases as well as transaction logs from
> production
> >> > to
> >> > >> standby. However,
> >> > >> >if someone is trying to do a select query at the
> time,
> >> > or
> >> > >> even just have
> >> > >> >their reporting application connected to it, the
> >> > restore
> >> > >> of the transaction
> >> > >> >log fails "due to not being able to gain exclusive
> >> > >> access". If one of the
> >> > >> >transaction log restores fail, they all fail
> after that
> >> > >> because the LSNs are
> >> > >> >off.
> >> > >> >
> >> > >> >This is, of course, an urgent problem.
> Management has
> >> > >> required that this
> >> > >> >server be as current as possible for reporting
> purposes
> >> > >> and this entire
> >> > >> >process must be automated.
> >> > >> >
> >> > >> >Servers are W2K Server sp4, SQL2K Standard Edition
> >> > sp3a.
> >> > >> >
> >> > >> >Below is an example of my syntax for the
> transaction
> >> > log
> >> > >> restores. Works
> >> > >> >like a charm unless something is connected to the
> >> > >> database. I had to do it
> >> > >> >this way because I wanted to save all the
> transaction
> >> > log
> >> > >> files taken
> >> > >> >throughout the day in case of "point in time"
> disaster
> >> > >> recovery. I ended up
> >> > >> >opening a ticket with Microsoft and this is the
> best
> >> > >> solution they could
> >> > >> >suggest. They said that normally with log
> shipping,
> >> > the
> >> > >> TLog backups
> >> > >> >overwrite the others in the device. I found it
> >> > difficult
> >> > >> to believe that it
> >> > >> >was expected that every process would work
> correctly
> >> > >> every time and you
> >> > >> >would have no need to ever access a transaction
> log
> >> > >> backup that was
> >> > >> >previously taken. If it has been overwritten,
> and you
> >> > >> try to restore one
> >> > >> >that is later, SQL send the error about LSNs not
> being
> >> > >> right.
> >> > >> >
> >> > >> > Is there something in here that needs to be
> changed to
> >> > >> accomplish my task?
> >> > >> >
> >> > >> >CREATE proc RestoreApplicationLogs_TLog
> >> > >> >as
> >> > >> >declare @.position int
> >> > >> >declare @.mediasetid int
> >> > >> >
> >> > >> >set @.mediasetid = (select media_set_id
> >> > >> >from NCNSV1015.msdb.dbo.backupmediafamily
> >> > >> >where logical_device_name
> = 'ApplicationLogs_TLog')
> >> > >> >
> >> > >> >set @.position= (select position from
> >> > >> NCNSV1015.msdb.dbo.backupset
> >> > >> >where Backup_set_id = (select max(backup_set_id)
> from
> >> > >> >NCNSV1015.msdb.dbo.backupset where media_set_id => >> > >> @.mediasetid))
> >> > >> >
> >> > >> >Restore Log ApplicationLogs
> >> > >> >from disk
> >> > >>
> >> >
> = 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
> >> > >> '
> >> > >> >with file = @.position,
> >> > >> >dbo_only,
> >> > >> >standby
> >> > >>
> >> >
> = 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
> >> > >> '
> >> > >> >
> >> > >> >waitfor delay '00:00:10'
> >> > >> >GO
> >> > >> >
> >> > >> >
> >> > >> >Please help.
> >> > >> >
> >> > >> >Thank you in advance,
> >> > >> >Deborah
> >> > >> >
> >> > >> >
> >> > >> >.
> >> > >> >
> >> > >
> >> > >
> >> > >.
> >> > >
> >>
> >>
> >
> >
> >.
> >|||While you can't get around kicking users out in order to
do the restore, here's a simple way to do it. Of course
they'd have to rerun their queries or reports...
alter database dbname set SINGLE_USER with rollback
immediate
restore log dbname from disk = 't-log file'
alter database dbname set MULTI_USER
This works great for our DR site to do log restores during
the day. Replication would have been a nightmare to
implement. If anyone has any better solutions for a DR
site, feel free to share with me. I do the restores to DR
each hr from hourly t-log backups from production. I know
management would like it to be real-time, but replication
would just be too much trouble to keep maintained. I
could however do the t-log solution every 15 min instead
of every hr....
>--Original Message--
>You can kick users out of a database... Tibor Karaszi has
some utility
>procedures on his web site... www.sqlmaint.com..
>One of those will kill all connections to a given
database... You can not
>however re-connect automatically.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Deborah Bohannon"
<dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
>in message news:#Qz7b3BwDHA.2260@.TK2MSFTNGP09.phx.gbl...
>> Well, clearly I have run into this bogus situation due
to not considering
>> the fact the I couldn't do log restores while someone
is connected to the
>> database. I mean, I knew that database restores
required exclusive
>access,
>> but somehow I thought that a read-only database would
allow transaction
>log
>> restores while others are connected to the database.
This would be a
>major
>> oops on my part. I had originally suggested to
management that the data
>be
>> synchronized only once or twice a day, but they firmly
want a solution
>that
>> allows them to off-load some of the decision support
queries/reports that
>> they use to be retrieved from this "standby" server, in
order to reduce
>the
>> resource load on production. This has always been
touted as an advantage
>to
>> having a standby server.
>> It has taken weeks to iron out the bugs that have
popped up in automating
>> the backup and restore of 36 databases. Everything
from permissions
>issues
>> to invalid syntax to just plain old timing problems.
Now that has been
>> running successfully all week and they are ready to
point these apps to
>that
>> server. I was testing that and began to see my restore
log jobs failing,
>of
>> course.
>> So, at this point, I don't know quite what I'm going to
have to do in
>order
>> to get the desired results. Disappointing is not quite
the word I would
>use
>> to describe the situation if I have to tell them that I
now have to take
>> some considerable time to create and test a replication
scenario. But, if
>> it is the ONLY way, then I guess that's just what I'll
have to do.
>> I am mentally exhausted and in desperate need of a
workable solution. Its
>> been at least 2 years since I've used any type of
replication in
>production,
>> and I am more than a bit fuzzy on what will have to be
done for successful
>> implementation.
>> Ok, guess I've over-explained :-) Thanks for listening.
>> Deborah
>>
>> "chris" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
>> > >Since the log restores literally take less than a
couple
>> > of seconds, is
>> > >there a way to kick the users out and automatically
>> > reconnect them? So that
>> > >from their perspective the query just took a couple
more
>> > seconds to run?
>> >
>> > Not that I know of. Someone else?
>> >
>> > >but I'm
>> > >now in a time crunch and don't want to generate any
new
>> > problems.
>> > >
>> > >Is transactional replication a straight-forward
thing to
>> > implement? Are
>> > >there any issues with adding that to the plan that I
need
>> > to be aware of?
>> > >If I were to implement transactional replication, how
>> > would that change the
>> > >backup/restore strategy, other than I wouldn't have
to
>> > restore the
>> > >transaction logs every 10 minutes? Can the process
be
>> > completely automated?
>> >
>> >
>> > Replication from my experience had somewhat of a steep
>> > learning curve. It really depends on your
circumstances
>> > though. Security/ firewalls/ authentication/ disater
>> > recovery scenarios/ size of initial snapshot/
transferring
>> > of logins and jobs/ alloted maintenance time/ the
>> > infamous "schema changes to a published table"/ and
lets
>> > not forget the all time favorite "identity colmuns in
>> > replication"/ ect. If you are on that much of a time
>> > crunch this may not be your best approach.
>> >
>> >
>> > Out of curiosity, how did you suddenly wind up in this
>> > truely bogus situation?
>> >
>> >
>> > >--Original Message--
>> > >I'm sure that the log shipping tool in the Enterprise
>> > Edition would work
>> > >great, but I do not have Enterprise Edition, nor
will we
>> > be upgrading to it.
>> > >Our environment simply cannot warrant that.
>> > >
>> > >Since the log restores literally take less than a
couple
>> > of seconds, is
>> > >there a way to kick the users out and automatically
>> > reconnect them? So that
>> > >from their perspective the query just took a couple
more
>> > seconds to run?
>> > >
>> > >I have not tried to implement replication in SQL2K,
and I
>> > am concerned about
>> > >possible maintenance mistakes. I've been reading up
on
>> > it in BOL, but I'm
>> > >now in a time crunch and don't want to generate any
new
>> > problems.
>> > >
>> > >Is transactional replication a straight-forward
thing to
>> > implement? Are
>> > >there any issues with adding that to the plan that I
need
>> > to be aware of?
>> > >If I were to implement transactional replication, how
>> > would that change the
>> > >backup/restore strategy, other than I wouldn't have
to
>> > restore the
>> > >transaction logs every 10 minutes? Can the process
be
>> > completely automated?
>> > >
>> > >Sorry to be asking so many questions. I have been
>> > reading BOL, but reading
>> > >the BOL, while usually helpful, is no substitute for
>> > talking to someone with
>> > >real experience.
>> > >
>> > >Thanks in advance,
>> > >Deborah
>> > >
>> > >
>> > >
>> > >"chris" <anonymous@.discussions.microsoft.com> wrote
in
>> > message
>> > >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
>> > >> You need to be using something like replication to
meet
>> > >> your stated requirements. In order for a db to be
>> > restored
>> > >> nobody can be accessing it. Log shipping has an
option
>> > to
>> > >> allow users into the db while its not being
restored and
>> > >> it will kick everony out when the restore is
starting,
>> > but
>> > >> this isnt usually a good idea for a reporting
server.
>> > >>
>> > >>
>> > >> >--Original Message--
>> > >> >I am trying to use my Standby Server as a
Reporting
>> > >> Server so that our
>> > >> >resource intensive reporting queries can be run
against
>> > >> it instead of
>> > >> >production.
>> > >> >
>> > >> >I've been successful at automating the backups and
>> > >> restores of all the
>> > >> >databases as well as transaction logs from
production
>> > to
>> > >> standby. However,
>> > >> >if someone is trying to do a select query at the
time,
>> > or
>> > >> even just have
>> > >> >their reporting application connected to it, the
>> > restore
>> > >> of the transaction
>> > >> >log fails "due to not being able to gain exclusive
>> > >> access". If one of the
>> > >> >transaction log restores fail, they all fail
after that
>> > >> because the LSNs are
>> > >> >off.
>> > >> >
>> > >> >This is, of course, an urgent problem.
Management has
>> > >> required that this
>> > >> >server be as current as possible for reporting
purposes
>> > >> and this entire
>> > >> >process must be automated.
>> > >> >
>> > >> >Servers are W2K Server sp4, SQL2K Standard Edition
>> > sp3a.
>> > >> >
>> > >> >Below is an example of my syntax for the
transaction
>> > log
>> > >> restores. Works
>> > >> >like a charm unless something is connected to the
>> > >> database. I had to do it
>> > >> >this way because I wanted to save all the
transaction
>> > log
>> > >> files taken
>> > >> >throughout the day in case of "point in time"
disaster
>> > >> recovery. I ended up
>> > >> >opening a ticket with Microsoft and this is the
best
>> > >> solution they could
>> > >> >suggest. They said that normally with log
shipping,
>> > the
>> > >> TLog backups
>> > >> >overwrite the others in the device. I found it
>> > difficult
>> > >> to believe that it
>> > >> >was expected that every process would work
correctly
>> > >> every time and you
>> > >> >would have no need to ever access a transaction
log
>> > >> backup that was
>> > >> >previously taken. If it has been overwritten,
and you
>> > >> try to restore one
>> > >> >that is later, SQL send the error about LSNs not
being
>> > >> right.
>> > >> >
>> > >> > Is there something in here that needs to be
changed to
>> > >> accomplish my task?
>> > >> >
>> > >> >CREATE proc RestoreApplicationLogs_TLog
>> > >> >as
>> > >> >declare @.position int
>> > >> >declare @.mediasetid int
>> > >> >
>> > >> >set @.mediasetid = (select media_set_id
>> > >> >from NCNSV1015.msdb.dbo.backupmediafamily
>> > >> >where logical_device_name
= 'ApplicationLogs_TLog')
>> > >> >
>> > >> >set @.position= (select position from
>> > >> NCNSV1015.msdb.dbo.backupset
>> > >> >where Backup_set_id = (select max(backup_set_id)
from
>> > >> >NCNSV1015.msdb.dbo.backupset where media_set_id =>> > >> @.mediasetid))
>> > >> >
>> > >> >Restore Log ApplicationLogs
>> > >> >from disk
>> > >>
>> >
= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
>> > >> '
>> > >> >with file = @.position,
>> > >> >dbo_only,
>> > >> >standby
>> > >>
>> >
= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
>> > >> '
>> > >> >
>> > >> >waitfor delay '00:00:10'
>> > >> >GO
>> > >> >
>> > >> >
>> > >> >Please help.
>> > >> >
>> > >> >Thank you in advance,
>> > >> >Deborah
>> > >> >
>> > >> >
>> > >> >.
>> > >> >
>> > >
>> > >
>> > >.
>> > >
>>
>
>.
>|||Do the users reports somehow automatically re-connect
after the restore? I ask only because she has stated that
to be one of her goals.
>--Original Message--
>While you can't get around kicking users out in order to
>do the restore, here's a simple way to do it. Of course
>they'd have to rerun their queries or reports...
>alter database dbname set SINGLE_USER with rollback
>immediate
>restore log dbname from disk = 't-log file'
>alter database dbname set MULTI_USER
>This works great for our DR site to do log restores
during
>the day. Replication would have been a nightmare to
>implement. If anyone has any better solutions for a DR
>site, feel free to share with me. I do the restores to
DR
>each hr from hourly t-log backups from production. I
know
>management would like it to be real-time, but replication
>would just be too much trouble to keep maintained. I
>could however do the t-log solution every 15 min instead
>of every hr....
>>--Original Message--
>>You can kick users out of a database... Tibor Karaszi
has
>some utility
>>procedures on his web site... www.sqlmaint.com..
>>One of those will kill all connections to a given
>database... You can not
>>however re-connect automatically.
>>--
>>Wayne Snyder, MCDBA, SQL Server MVP
>>Computer Education Services Corporation (CESC),
>Charlotte, NC
>>www.computeredservices.com
>>(Please respond only to the newsgroups.)
>>I support the Professional Association of SQL Server
>(PASS) and it's
>>community of SQL Server professionals.
>>www.sqlpass.org
>>"Deborah Bohannon"
><dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
>>in message news:#Qz7b3BwDHA.2260@.TK2MSFTNGP09.phx.gbl...
>> Well, clearly I have run into this bogus situation due
>to not considering
>> the fact the I couldn't do log restores while someone
>is connected to the
>> database. I mean, I knew that database restores
>required exclusive
>>access,
>> but somehow I thought that a read-only database would
>allow transaction
>>log
>> restores while others are connected to the database.
>This would be a
>>major
>> oops on my part. I had originally suggested to
>management that the data
>>be
>> synchronized only once or twice a day, but they firmly
>want a solution
>>that
>> allows them to off-load some of the decision support
>queries/reports that
>> they use to be retrieved from this "standby" server,
in
>order to reduce
>>the
>> resource load on production. This has always been
>touted as an advantage
>>to
>> having a standby server.
>> It has taken weeks to iron out the bugs that have
>popped up in automating
>> the backup and restore of 36 databases. Everything
>from permissions
>>issues
>> to invalid syntax to just plain old timing problems.
>Now that has been
>> running successfully all week and they are ready to
>point these apps to
>>that
>> server. I was testing that and began to see my
restore
>log jobs failing,
>>of
>> course.
>> So, at this point, I don't know quite what I'm going
to
>have to do in
>>order
>> to get the desired results. Disappointing is not
quite
>the word I would
>>use
>> to describe the situation if I have to tell them that
I
>now have to take
>> some considerable time to create and test a
replication
>scenario. But, if
>> it is the ONLY way, then I guess that's just what I'll
>have to do.
>> I am mentally exhausted and in desperate need of a
>workable solution. Its
>> been at least 2 years since I've used any type of
>replication in
>>production,
>> and I am more than a bit fuzzy on what will have to be
>done for successful
>> implementation.
>> Ok, guess I've over-explained :-) Thanks for
listening.
>> Deborah
>>
>> "chris" <anonymous@.discussions.microsoft.com> wrote in
>message
>> news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
>> > >Since the log restores literally take less than a
>couple
>> > of seconds, is
>> > >there a way to kick the users out and automatically
>> > reconnect them? So that
>> > >from their perspective the query just took a couple
>more
>> > seconds to run?
>> >
>> > Not that I know of. Someone else?
>> >
>> > >but I'm
>> > >now in a time crunch and don't want to generate any
>new
>> > problems.
>> > >
>> > >Is transactional replication a straight-forward
>thing to
>> > implement? Are
>> > >there any issues with adding that to the plan that
I
>need
>> > to be aware of?
>> > >If I were to implement transactional replication,
how
>> > would that change the
>> > >backup/restore strategy, other than I wouldn't have
>to
>> > restore the
>> > >transaction logs every 10 minutes? Can the process
>be
>> > completely automated?
>> >
>> >
>> > Replication from my experience had somewhat of a
steep
>> > learning curve. It really depends on your
>circumstances
>> > though. Security/ firewalls/ authentication/ disater
>> > recovery scenarios/ size of initial snapshot/
>transferring
>> > of logins and jobs/ alloted maintenance time/ the
>> > infamous "schema changes to a published table"/ and
>lets
>> > not forget the all time favorite "identity colmuns in
>> > replication"/ ect. If you are on that much of a time
>> > crunch this may not be your best approach.
>> >
>> >
>> > Out of curiosity, how did you suddenly wind up in
this
>> > truely bogus situation?
>> >
>> >
>> > >--Original Message--
>> > >I'm sure that the log shipping tool in the
Enterprise
>> > Edition would work
>> > >great, but I do not have Enterprise Edition, nor
>will we
>> > be upgrading to it.
>> > >Our environment simply cannot warrant that.
>> > >
>> > >Since the log restores literally take less than a
>couple
>> > of seconds, is
>> > >there a way to kick the users out and automatically
>> > reconnect them? So that
>> > >from their perspective the query just took a couple
>more
>> > seconds to run?
>> > >
>> > >I have not tried to implement replication in SQL2K,
>and I
>> > am concerned about
>> > >possible maintenance mistakes. I've been reading
up
>on
>> > it in BOL, but I'm
>> > >now in a time crunch and don't want to generate any
>new
>> > problems.
>> > >
>> > >Is transactional replication a straight-forward
>thing to
>> > implement? Are
>> > >there any issues with adding that to the plan that
I
>need
>> > to be aware of?
>> > >If I were to implement transactional replication,
how
>> > would that change the
>> > >backup/restore strategy, other than I wouldn't have
>to
>> > restore the
>> > >transaction logs every 10 minutes? Can the process
>be
>> > completely automated?
>> > >
>> > >Sorry to be asking so many questions. I have been
>> > reading BOL, but reading
>> > >the BOL, while usually helpful, is no substitute for
>> > talking to someone with
>> > >real experience.
>> > >
>> > >Thanks in advance,
>> > >Deborah
>> > >
>> > >
>> > >
>> > >"chris" <anonymous@.discussions.microsoft.com> wrote
>in
>> > message
>> > >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
>> > >> You need to be using something like replication
to
>meet
>> > >> your stated requirements. In order for a db to be
>> > restored
>> > >> nobody can be accessing it. Log shipping has an
>option
>> > to
>> > >> allow users into the db while its not being
>restored and
>> > >> it will kick everony out when the restore is
>starting,
>> > but
>> > >> this isnt usually a good idea for a reporting
>server.
>> > >>
>> > >>
>> > >> >--Original Message--
>> > >> >I am trying to use my Standby Server as a
>Reporting
>> > >> Server so that our
>> > >> >resource intensive reporting queries can be run
>against
>> > >> it instead of
>> > >> >production.
>> > >> >
>> > >> >I've been successful at automating the backups
and
>> > >> restores of all the
>> > >> >databases as well as transaction logs from
>production
>> > to
>> > >> standby. However,
>> > >> >if someone is trying to do a select query at the
>time,
>> > or
>> > >> even just have
>> > >> >their reporting application connected to it, the
>> > restore
>> > >> of the transaction
>> > >> >log fails "due to not being able to gain
exclusive
>> > >> access". If one of the
>> > >> >transaction log restores fail, they all fail
>after that
>> > >> because the LSNs are
>> > >> >off.
>> > >> >
>> > >> >This is, of course, an urgent problem.
>Management has
>> > >> required that this
>> > >> >server be as current as possible for reporting
>purposes
>> > >> and this entire
>> > >> >process must be automated.
>> > >> >
>> > >> >Servers are W2K Server sp4, SQL2K Standard
Edition
>> > sp3a.
>> > >> >
>> > >> >Below is an example of my syntax for the
>transaction
>> > log
>> > >> restores. Works
>> > >> >like a charm unless something is connected to the
>> > >> database. I had to do it
>> > >> >this way because I wanted to save all the
>transaction
>> > log
>> > >> files taken
>> > >> >throughout the day in case of "point in time"
>disaster
>> > >> recovery. I ended up
>> > >> >opening a ticket with Microsoft and this is the
>best
>> > >> solution they could
>> > >> >suggest. They said that normally with log
>shipping,
>> > the
>> > >> TLog backups
>> > >> >overwrite the others in the device. I found it
>> > difficult
>> > >> to believe that it
>> > >> >was expected that every process would work
>correctly
>> > >> every time and you
>> > >> >would have no need to ever access a transaction
>log
>> > >> backup that was
>> > >> >previously taken. If it has been overwritten,
>and you
>> > >> try to restore one
>> > >> >that is later, SQL send the error about LSNs not
>being
>> > >> right.
>> > >> >
>> > >> > Is there something in here that needs to be
>changed to
>> > >> accomplish my task?
>> > >> >
>> > >> >CREATE proc RestoreApplicationLogs_TLog
>> > >> >as
>> > >> >declare @.position int
>> > >> >declare @.mediasetid int
>> > >> >
>> > >> >set @.mediasetid = (select media_set_id
>> > >> >from NCNSV1015.msdb.dbo.backupmediafamily
>> > >> >where logical_device_name
>= 'ApplicationLogs_TLog')
>> > >> >
>> > >> >set @.position= (select position from
>> > >> NCNSV1015.msdb.dbo.backupset
>> > >> >where Backup_set_id = (select max(backup_set_id)
>from
>> > >> >NCNSV1015.msdb.dbo.backupset where media_set_id =>> > >> @.mediasetid))
>> > >> >
>> > >> >Restore Log ApplicationLogs
>> > >> >from disk
>> > >>
>> >
>= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.ba
k
>> > >> '
>> > >> >with file = @.position,
>> > >> >dbo_only,
>> > >> >standby
>> > >>
>> >
>= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ld
f
>> > >> '
>> > >> >
>> > >> >waitfor delay '00:00:10'
>> > >> >GO
>> > >> >
>> > >> >
>> > >> >Please help.
>> > >> >
>> > >> >Thank you in advance,
>> > >> >Deborah
>> > >> >
>> > >> >
>> > >> >.
>> > >> >
>> > >
>> > >
>> > >.
>> > >
>>
>>
>>.
>.
>|||Van,
Thanks for your reply.
I'm going to try the alter database suggestion and see how much that would
impact a particular database app I've been testing with. If the affect is
minimal, meaning not too aggravating for those running queries/reports, then
I think that is the best way for us to go. I am the only DBA here, and
while I don't feel that it would be impossible to set up the transactional
replication scenario, I am unsure as to how much "administration" time from
me it would require. I am also the EDI Implemenation Specialist/BizTalk
guru and those duties are the "revenue" producing ones for our business.
Same old story, not enough hours in the day etc...
If anyone has any better ideas, I am certainly game to hear them.
Deborah
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:0d7101c3c02f$f3bfb910$a001280a@.phx.gbl...
> While you can't get around kicking users out in order to
> do the restore, here's a simple way to do it. Of course
> they'd have to rerun their queries or reports...
> alter database dbname set SINGLE_USER with rollback
> immediate
> restore log dbname from disk = 't-log file'
> alter database dbname set MULTI_USER
> This works great for our DR site to do log restores during
> the day. Replication would have been a nightmare to
> implement. If anyone has any better solutions for a DR
> site, feel free to share with me. I do the restores to DR
> each hr from hourly t-log backups from production. I know
> management would like it to be real-time, but replication
> would just be too much trouble to keep maintained. I
> could however do the t-log solution every 15 min instead
> of every hr....
> >--Original Message--
> >You can kick users out of a database... Tibor Karaszi has
> some utility
> >procedures on his web site... www.sqlmaint.com..
> >
> >One of those will kill all connections to a given
> database... You can not
> >however re-connect automatically.
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >"Deborah Bohannon"
> <dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
> >in message news:#Qz7b3BwDHA.2260@.TK2MSFTNGP09.phx.gbl...
> >> Well, clearly I have run into this bogus situation due
> to not considering
> >> the fact the I couldn't do log restores while someone
> is connected to the
> >> database. I mean, I knew that database restores
> required exclusive
> >access,
> >> but somehow I thought that a read-only database would
> allow transaction
> >log
> >> restores while others are connected to the database.
> This would be a
> >major
> >> oops on my part. I had originally suggested to
> management that the data
> >be
> >> synchronized only once or twice a day, but they firmly
> want a solution
> >that
> >> allows them to off-load some of the decision support
> queries/reports that
> >> they use to be retrieved from this "standby" server, in
> order to reduce
> >the
> >> resource load on production. This has always been
> touted as an advantage
> >to
> >> having a standby server.
> >>
> >> It has taken weeks to iron out the bugs that have
> popped up in automating
> >> the backup and restore of 36 databases. Everything
> from permissions
> >issues
> >> to invalid syntax to just plain old timing problems.
> Now that has been
> >> running successfully all week and they are ready to
> point these apps to
> >that
> >> server. I was testing that and began to see my restore
> log jobs failing,
> >of
> >> course.
> >>
> >> So, at this point, I don't know quite what I'm going to
> have to do in
> >order
> >> to get the desired results. Disappointing is not quite
> the word I would
> >use
> >> to describe the situation if I have to tell them that I
> now have to take
> >> some considerable time to create and test a replication
> scenario. But, if
> >> it is the ONLY way, then I guess that's just what I'll
> have to do.
> >>
> >> I am mentally exhausted and in desperate need of a
> workable solution. Its
> >> been at least 2 years since I've used any type of
> replication in
> >production,
> >> and I am more than a bit fuzzy on what will have to be
> done for successful
> >> implementation.
> >>
> >> Ok, guess I've over-explained :-) Thanks for listening.
> >> Deborah
> >>
> >>
> >> "chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >> news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
> >> > >Since the log restores literally take less than a
> couple
> >> > of seconds, is
> >> > >there a way to kick the users out and automatically
> >> > reconnect them? So that
> >> > >from their perspective the query just took a couple
> more
> >> > seconds to run?
> >> >
> >> > Not that I know of. Someone else?
> >> >
> >> > >but I'm
> >> > >now in a time crunch and don't want to generate any
> new
> >> > problems.
> >> > >
> >> > >Is transactional replication a straight-forward
> thing to
> >> > implement? Are
> >> > >there any issues with adding that to the plan that I
> need
> >> > to be aware of?
> >> > >If I were to implement transactional replication, how
> >> > would that change the
> >> > >backup/restore strategy, other than I wouldn't have
> to
> >> > restore the
> >> > >transaction logs every 10 minutes? Can the process
> be
> >> > completely automated?
> >> >
> >> >
> >> > Replication from my experience had somewhat of a steep
> >> > learning curve. It really depends on your
> circumstances
> >> > though. Security/ firewalls/ authentication/ disater
> >> > recovery scenarios/ size of initial snapshot/
> transferring
> >> > of logins and jobs/ alloted maintenance time/ the
> >> > infamous "schema changes to a published table"/ and
> lets
> >> > not forget the all time favorite "identity colmuns in
> >> > replication"/ ect. If you are on that much of a time
> >> > crunch this may not be your best approach.
> >> >
> >> >
> >> > Out of curiosity, how did you suddenly wind up in this
> >> > truely bogus situation?
> >> >
> >> >
> >> > >--Original Message--
> >> > >I'm sure that the log shipping tool in the Enterprise
> >> > Edition would work
> >> > >great, but I do not have Enterprise Edition, nor
> will we
> >> > be upgrading to it.
> >> > >Our environment simply cannot warrant that.
> >> > >
> >> > >Since the log restores literally take less than a
> couple
> >> > of seconds, is
> >> > >there a way to kick the users out and automatically
> >> > reconnect them? So that
> >> > >from their perspective the query just took a couple
> more
> >> > seconds to run?
> >> > >
> >> > >I have not tried to implement replication in SQL2K,
> and I
> >> > am concerned about
> >> > >possible maintenance mistakes. I've been reading up
> on
> >> > it in BOL, but I'm
> >> > >now in a time crunch and don't want to generate any
> new
> >> > problems.
> >> > >
> >> > >Is transactional replication a straight-forward
> thing to
> >> > implement? Are
> >> > >there any issues with adding that to the plan that I
> need
> >> > to be aware of?
> >> > >If I were to implement transactional replication, how
> >> > would that change the
> >> > >backup/restore strategy, other than I wouldn't have
> to
> >> > restore the
> >> > >transaction logs every 10 minutes? Can the process
> be
> >> > completely automated?
> >> > >
> >> > >Sorry to be asking so many questions. I have been
> >> > reading BOL, but reading
> >> > >the BOL, while usually helpful, is no substitute for
> >> > talking to someone with
> >> > >real experience.
> >> > >
> >> > >Thanks in advance,
> >> > >Deborah
> >> > >
> >> > >
> >> > >
> >> > >"chris" <anonymous@.discussions.microsoft.com> wrote
> in
> >> > message
> >> > >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
> >> > >> You need to be using something like replication to
> meet
> >> > >> your stated requirements. In order for a db to be
> >> > restored
> >> > >> nobody can be accessing it. Log shipping has an
> option
> >> > to
> >> > >> allow users into the db while its not being
> restored and
> >> > >> it will kick everony out when the restore is
> starting,
> >> > but
> >> > >> this isnt usually a good idea for a reporting
> server.
> >> > >>
> >> > >>
> >> > >> >--Original Message--
> >> > >> >I am trying to use my Standby Server as a
> Reporting
> >> > >> Server so that our
> >> > >> >resource intensive reporting queries can be run
> against
> >> > >> it instead of
> >> > >> >production.
> >> > >> >
> >> > >> >I've been successful at automating the backups and
> >> > >> restores of all the
> >> > >> >databases as well as transaction logs from
> production
> >> > to
> >> > >> standby. However,
> >> > >> >if someone is trying to do a select query at the
> time,
> >> > or
> >> > >> even just have
> >> > >> >their reporting application connected to it, the
> >> > restore
> >> > >> of the transaction
> >> > >> >log fails "due to not being able to gain exclusive
> >> > >> access". If one of the
> >> > >> >transaction log restores fail, they all fail
> after that
> >> > >> because the LSNs are
> >> > >> >off.
> >> > >> >
> >> > >> >This is, of course, an urgent problem.
> Management has
> >> > >> required that this
> >> > >> >server be as current as possible for reporting
> purposes
> >> > >> and this entire
> >> > >> >process must be automated.
> >> > >> >
> >> > >> >Servers are W2K Server sp4, SQL2K Standard Edition
> >> > sp3a.
> >> > >> >
> >> > >> >Below is an example of my syntax for the
> transaction
> >> > log
> >> > >> restores. Works
> >> > >> >like a charm unless something is connected to the
> >> > >> database. I had to do it
> >> > >> >this way because I wanted to save all the
> transaction
> >> > log
> >> > >> files taken
> >> > >> >throughout the day in case of "point in time"
> disaster
> >> > >> recovery. I ended up
> >> > >> >opening a ticket with Microsoft and this is the
> best
> >> > >> solution they could
> >> > >> >suggest. They said that normally with log
> shipping,
> >> > the
> >> > >> TLog backups
> >> > >> >overwrite the others in the device. I found it
> >> > difficult
> >> > >> to believe that it
> >> > >> >was expected that every process would work
> correctly
> >> > >> every time and you
> >> > >> >would have no need to ever access a transaction
> log
> >> > >> backup that was
> >> > >> >previously taken. If it has been overwritten,
> and you
> >> > >> try to restore one
> >> > >> >that is later, SQL send the error about LSNs not
> being
> >> > >> right.
> >> > >> >
> >> > >> > Is there something in here that needs to be
> changed to
> >> > >> accomplish my task?
> >> > >> >
> >> > >> >CREATE proc RestoreApplicationLogs_TLog
> >> > >> >as
> >> > >> >declare @.position int
> >> > >> >declare @.mediasetid int
> >> > >> >
> >> > >> >set @.mediasetid = (select media_set_id
> >> > >> >from NCNSV1015.msdb.dbo.backupmediafamily
> >> > >> >where logical_device_name
> = 'ApplicationLogs_TLog')
> >> > >> >
> >> > >> >set @.position= (select position from
> >> > >> NCNSV1015.msdb.dbo.backupset
> >> > >> >where Backup_set_id = (select max(backup_set_id)
> from
> >> > >> >NCNSV1015.msdb.dbo.backupset where media_set_id => >> > >> @.mediasetid))
> >> > >> >
> >> > >> >Restore Log ApplicationLogs
> >> > >> >from disk
> >> > >>
> >> >
> = 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
> >> > >> '
> >> > >> >with file = @.position,
> >> > >> >dbo_only,
> >> > >> >standby
> >> > >>
> >> >
> = 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
> >> > >> '
> >> > >> >
> >> > >> >waitfor delay '00:00:10'
> >> > >> >GO
> >> > >> >
> >> > >> >
> >> > >> >Please help.
> >> > >> >
> >> > >> >Thank you in advance,
> >> > >> >Deborah
> >> > >> >
> >> > >> >
> >> > >> >.
> >> > >> >
> >> > >
> >> > >
> >> > >.
> >> > >
> >>
> >>
> >
> >
> >.
> >|||Dont know about that last one. If you should need to end
up using replication, start posting to the replication
section of this NG. A few of us reply to questions asked
there regularly in an attempt to save others the growing
pains of using replication. Keep in mind that its like log
shipping in that once you have the bugs ironed out its
pretty easy to maintain.
Good luck.
>--Original Message--
>Perhaps I can somehow have it written into the connection
of their app to
>the database that it reconnects whenever they run
a "query" and disconnects
>after. Then the transaction log restore process
can "retry" a few times
>until it is successful.
>I'm not sure if that is possible or what kind of
performance issues that
>would bring up (as pertains to their reporting), but it
might be another way
>to address this problem.
>It seems I have led them astray with the idea that using
the warm standby
>server could actually be used as a reporting server. At
this point, I will
>need to adjust their expectations. My head is getting
bruised from all the
>hitting it against the wall I've done on what was
supposed to be a fairly
>simple project :-)
>We simply cannot afford to have a 3rd server that is
available for use as a
>reporting server. It was a stretch to convince them we
needed a standby
>server at all. It was only after the production server
went down and 50
>people were standing around for almost a day, not being
productive, that it
>became a priority. The real sell for them was the idea
of off-loading some
>of the reporting while also having a warm backup.
>Thank you all for all your help.
>Deborah
>"Allan Hirt" <allanh@.NOSPAMavanade.com> wrote in message
>news:02a301c3c02d$833cd850$a501280a@.phx.gbl...
>> I posted the syntax above ... it's just switches of the
>> ALTER DATABASE command. True, you have the app
>> reconnection problem, but hey, it keeps you in business.
>> This is the way backup/restore has worked historically
in
>> SQL Server. It makes sense, as you are making sure
>> nothing can possibly interfere with the restore process.
>> I understand your dilemma, and I've had this hard
>> discussion with many of my clients in the past. It's a
>> common misunderstanding that if you put your db in
>> STANDBY, you always have read-only access. It's true if
>> you never apply a tran log.
>> Now, if you could possibly convince them that the
>> reporting server will be a delta of time out (say a few
>> hours) and apply a bunch of TLs all at once at known
>> times, it may help here.
>> >--Original Message--
>> >You can kick users out of a database... Tibor Karaszi
has
>> some utility
>> >procedures on his web site... www.sqlmaint.com..
>> >
>> >One of those will kill all connections to a given
>> database... You can not
>> >however re-connect automatically.
>> >
>> >--
>> >Wayne Snyder, MCDBA, SQL Server MVP
>> >Computer Education Services Corporation (CESC),
>> Charlotte, NC
>> >www.computeredservices.com
>> >(Please respond only to the newsgroups.)
>> >
>> >I support the Professional Association of SQL Server
>> (PASS) and it's
>> >community of SQL Server professionals.
>> >www.sqlpass.org
>> >
>> >"Deborah Bohannon"
>> <dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
>> >in message
news:#Qz7b3BwDHA.2260@.TK2MSFTNGP09.phx.gbl...
>> >> Well, clearly I have run into this bogus situation
due
>> to not considering
>> >> the fact the I couldn't do log restores while someone
>> is connected to the
>> >> database. I mean, I knew that database restores
>> required exclusive
>> >access,
>> >> but somehow I thought that a read-only database would
>> allow transaction
>> >log
>> >> restores while others are connected to the database.
>> This would be a
>> >major
>> >> oops on my part. I had originally suggested to
>> management that the data
>> >be
>> >> synchronized only once or twice a day, but they
firmly
>> want a solution
>> >that
>> >> allows them to off-load some of the decision support
>> queries/reports that
>> >> they use to be retrieved from this "standby" server,
in
>> order to reduce
>> >the
>> >> resource load on production. This has always been
>> touted as an advantage
>> >to
>> >> having a standby server.
>> >>
>> >> It has taken weeks to iron out the bugs that have
>> popped up in automating
>> >> the backup and restore of 36 databases. Everything
>> from permissions
>> >issues
>> >> to invalid syntax to just plain old timing problems.
>> Now that has been
>> >> running successfully all week and they are ready to
>> point these apps to
>> >that
>> >> server. I was testing that and began to see my
restore
>> log jobs failing,
>> >of
>> >> course.
>> >>
>> >> So, at this point, I don't know quite what I'm going
to
>> have to do in
>> >order
>> >> to get the desired results. Disappointing is not
quite
>> the word I would
>> >use
>> >> to describe the situation if I have to tell them
that I
>> now have to take
>> >> some considerable time to create and test a
replication
>> scenario. But, if
>> >> it is the ONLY way, then I guess that's just what
I'll
>> have to do.
>> >>
>> >> I am mentally exhausted and in desperate need of a
>> workable solution. Its
>> >> been at least 2 years since I've used any type of
>> replication in
>> >production,
>> >> and I am more than a bit fuzzy on what will have to
be
>> done for successful
>> >> implementation.
>> >>
>> >> Ok, guess I've over-explained :-) Thanks for
listening.
>> >> Deborah
>> >>
>> >>
>> >> "chris" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >> news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
>> >> > >Since the log restores literally take less than a
>> couple
>> >> > of seconds, is
>> >> > >there a way to kick the users out and
automatically
>> >> > reconnect them? So that
>> >> > >from their perspective the query just took a
couple
>> more
>> >> > seconds to run?
>> >> >
>> >> > Not that I know of. Someone else?
>> >> >
>> >> > >but I'm
>> >> > >now in a time crunch and don't want to generate
any
>> new
>> >> > problems.
>> >> > >
>> >> > >Is transactional replication a straight-forward
>> thing to
>> >> > implement? Are
>> >> > >there any issues with adding that to the plan
that I
>> need
>> >> > to be aware of?
>> >> > >If I were to implement transactional replication,
how
>> >> > would that change the
>> >> > >backup/restore strategy, other than I wouldn't
have
>> to
>> >> > restore the
>> >> > >transaction logs every 10 minutes? Can the
process
>> be
>> >> > completely automated?
>> >> >
>> >> >
>> >> > Replication from my experience had somewhat of a
steep
>> >> > learning curve. It really depends on your
>> circumstances
>> >> > though. Security/ firewalls/ authentication/
disater
>> >> > recovery scenarios/ size of initial snapshot/
>> transferring
>> >> > of logins and jobs/ alloted maintenance time/ the
>> >> > infamous "schema changes to a published table"/ and
>> lets
>> >> > not forget the all time favorite "identity colmuns
in
>> >> > replication"/ ect. If you are on that much of a
time
>> >> > crunch this may not be your best approach.
>> >> >
>> >> >
>> >> > Out of curiosity, how did you suddenly wind up in
this
>> >> > truely bogus situation?
>> >> >
>> >> >
>> >> > >--Original Message--
>> >> > >I'm sure that the log shipping tool in the
Enterprise
>> >> > Edition would work
>> >> > >great, but I do not have Enterprise Edition, nor
>> will we
>> >> > be upgrading to it.
>> >> > >Our environment simply cannot warrant that.
>> >> > >
>> >> > >Since the log restores literally take less than a
>> couple
>> >> > of seconds, is
>> >> > >there a way to kick the users out and
automatically
>> >> > reconnect them? So that
>> >> > >from their perspective the query just took a
couple
>> more
>> >> > seconds to run?
>> >> > >
>> >> > >I have not tried to implement replication in
SQL2K,
>> and I
>> >> > am concerned about
>> >> > >possible maintenance mistakes. I've been reading
up
>> on
>> >> > it in BOL, but I'm
>> >> > >now in a time crunch and don't want to generate
any
>> new
>> >> > problems.
>> >> > >
>> >> > >Is transactional replication a straight-forward
>> thing to
>> >> > implement? Are
>> >> > >there any issues with adding that to the plan
that I
>> need
>> >> > to be aware of?
>> >> > >If I were to implement transactional replication,
how
>> >> > would that change the
>> >> > >backup/restore strategy, other than I wouldn't
have
>> to
>> >> > restore the
>> >> > >transaction logs every 10 minutes? Can the
process
>> be
>> >> > completely automated?
>> >> > >
>> >> > >Sorry to be asking so many questions. I have been
>> >> > reading BOL, but reading
>> >> > >the BOL, while usually helpful, is no substitute
for
>> >> > talking to someone with
>> >> > >real experience.
>> >> > >
>> >> > >Thanks in advance,
>> >> > >Deborah
>> >> > >
>> >> > >
>> >> > >
>> >> > >"chris" <anonymous@.discussions.microsoft.com>
wrote
>> in
>> >> > message
>> >> > >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
>> >> > >> You need to be using something like replication
to
>> meet
>> >> > >> your stated requirements. In order for a db to
be
>> >> > restored
>> >> > >> nobody can be accessing it. Log shipping has an
>> option
>> >> > to
>> >> > >> allow users into the db while its not being
>> restored and
>> >> > >> it will kick everony out when the restore is
>> starting,
>> >> > but
>> >> > >> this isnt usually a good idea for a reporting
>> server.
>> >> > >>
>> >> > >>
>> >> > >> >--Original Message--
>> >> > >> >I am trying to use my Standby Server as a
>> Reporting
>> >> > >> Server so that our
>> >> > >> >resource intensive reporting queries can be run
>> against
>> >> > >> it instead of
>> >> > >> >production.
>> >> > >> >
>> >> > >> >I've been successful at automating the backups
and
>> >> > >> restores of all the
>> >> > >> >databases as well as transaction logs from
>> production
>> >> > to
>> >> > >> standby. However,
>> >> > >> >if someone is trying to do a select query at
the
>> time,
>> >> > or
>> >> > >> even just have
>> >> > >> >their reporting application connected to it,
the
>> >> > restore
>> >> > >> of the transaction
>> >> > >> >log fails "due to not being able to gain
exclusive
>> >> > >> access". If one of the
>> >> > >> >transaction log restores fail, they all fail
>> after that
>> >> > >> because the LSNs are
>> >> > >> >off.
>> >> > >> >
>> >> > >> >This is, of course, an urgent problem.
>> Management has
>> >> > >> required that this
>> >> > >> >server be as current as possible for reporting
>> purposes
>> >> > >> and this entire
>> >> > >> >process must be automated.
>> >> > >> >
>> >> > >> >Servers are W2K Server sp4, SQL2K Standard
Edition
>> >> > sp3a.
>> >> > >> >
>> >> > >> >Below is an example of my syntax for the
>> transaction
>> >> > log
>> >> > >> restores. Works
>> >> > >> >like a charm unless something is connected to
the
>> >> > >> database. I had to do it
>> >> > >> >this way because I wanted to save all the
>> transaction
>> >> > log
>> >> > >> files taken
>> >> > >> >throughout the day in case of "point in time"
>> disaster
>> >> > >> recovery. I ended up
>> >> > >> >opening a ticket with Microsoft and this is the
>> best
>> >> > >> solution they could
>> >> > >> >suggest. They said that normally with log
>> shipping,
>> >> > the
>> >> > >> TLog backups
>> >> > >> >overwrite the others in the device. I found it
>> >> > difficult
>> >> > >> to believe that it
>> >> > >> >was expected that every process would work
>> correctly
>> >> > >> every time and you
>> >> > >> >would have no need to ever access a
transaction
>> log
>> >> > >> backup that was
>> >> > >> >previously taken. If it has been overwritten,
>> and you
>> >> > >> try to restore one
>> >> > >> >that is later, SQL send the error about LSNs
not
>> being
>> >> > >> right.
>> >> > >> >
>> >> > >> > Is there something in here that needs to be
>> changed to
>> >> > >> accomplish my task?
>> >> > >> >
>> >> > >> >CREATE proc RestoreApplicationLogs_TLog
>> >> > >> >as
>> >> > >> >declare @.position int
>> >> > >> >declare @.mediasetid int
>> >> > >> >
>> >> > >> >set @.mediasetid = (select media_set_id
>> >> > >> >from NCNSV1015.msdb.dbo.backupmediafamily
>> >> > >> >where logical_device_name
>> = 'ApplicationLogs_TLog')
>> >> > >> >
>> >> > >> >set @.position= (select position from
>> >> > >> NCNSV1015.msdb.dbo.backupset
>> >> > >> >where Backup_set_id = (select max
(backup_set_id)
>> from
>> >> > >> >NCNSV1015.msdb.dbo.backupset where
media_set_id =>> >> > >> @.mediasetid))
>> >> > >> >
>> >> > >> >Restore Log ApplicationLogs
>> >> > >> >from disk
>> >> > >>
>> >> >
= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
>> >> > >> '
>> >> > >> >with file = @.position,
>> >> > >> >dbo_only,
>> >> > >> >standby
>> >> > >>
>> >> >
= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
>> >> > >> '
>> >> > >> >
>> >> > >> >waitfor delay '00:00:10'
>> >> > >> >GO
>> >> > >> >
>> >> > >> >
>> >> > >> >Please help.
>> >> > >> >
>> >> > >> >Thank you in advance,
>> >> > >> >Deborah
>> >> > >> >
>> >> > >> >
>> >> > >> >.
>> >> > >> >
>> >> > >
>> >> > >
>> >> > >.
>> >> > >
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||Yea, the With Rollback Immediate will kick the users out
and that may be a bit of a problem, but with tlog
restores, that's about the best you can do. You have to
ask management what's more important...a reporting server,
or having the standby db up to date with production in
case of an outage with the production server. I believe
the latter is more important. Most of their reports
shouldn't take too long to run (since it's not used too
much), so kicking them out for a few seconds and having
them rerun hopefully won't bother them too much. At least
you can tell them that the data will be more current when
they rerun.
The problem I have with replication is that it doesn't
replicate structure changes to your db and tables. You
have to do that seperatly by basically reshipping a
current version of the db or table to the standby server
with the structure changes. At least that's the way I
understand it. So to me it seems like there is a great
deal of overhead with managing and keeping replication in
line. And also, it seems like you really don't know if
it's working without extensive testing each time you do a
structure change and apply it to your standby. With log
shipping, you know that all your changes (data and
structure) are being applied to the standby server.
That's the way I understand replication to work.
Van
>--Original Message--
>Van,
>Thanks for your reply.
>I'm going to try the alter database suggestion and see
how much that would
>impact a particular database app I've been testing with.
If the affect is
>minimal, meaning not too aggravating for those running
queries/reports, then
>I think that is the best way for us to go. I am the only
DBA here, and
>while I don't feel that it would be impossible to set up
the transactional
>replication scenario, I am unsure as to how
much "administration" time from
>me it would require. I am also the EDI Implemenation
Specialist/BizTalk
>guru and those duties are the "revenue" producing ones
for our business.
>Same old story, not enough hours in the day etc...
>If anyone has any better ideas, I am certainly game to
hear them.
>Deborah
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote
in message
>news:0d7101c3c02f$f3bfb910$a001280a@.phx.gbl...
>> While you can't get around kicking users out in order to
>> do the restore, here's a simple way to do it. Of course
>> they'd have to rerun their queries or reports...
>> alter database dbname set SINGLE_USER with rollback
>> immediate
>> restore log dbname from disk = 't-log file'
>> alter database dbname set MULTI_USER
>> This works great for our DR site to do log restores
during
>> the day. Replication would have been a nightmare to
>> implement. If anyone has any better solutions for a DR
>> site, feel free to share with me. I do the restores to
DR
>> each hr from hourly t-log backups from production. I
know
>> management would like it to be real-time, but
replication
>> would just be too much trouble to keep maintained. I
>> could however do the t-log solution every 15 min instead
>> of every hr....
>> >--Original Message--
>> >You can kick users out of a database... Tibor Karaszi
has
>> some utility
>> >procedures on his web site... www.sqlmaint.com..
>> >
>> >One of those will kill all connections to a given
>> database... You can not
>> >however re-connect automatically.
>> >
>> >--
>> >Wayne Snyder, MCDBA, SQL Server MVP
>> >Computer Education Services Corporation (CESC),
>> Charlotte, NC
>> >www.computeredservices.com
>> >(Please respond only to the newsgroups.)
>> >
>> >I support the Professional Association of SQL Server
>> (PASS) and it's
>> >community of SQL Server professionals.
>> >www.sqlpass.org
>> >
>> >"Deborah Bohannon"
>> <dbohannon@.nationalDONOTSENDHEREcareanetwork.com> wrote
>> >in message
news:#Qz7b3BwDHA.2260@.TK2MSFTNGP09.phx.gbl...
>> >> Well, clearly I have run into this bogus situation
due
>> to not considering
>> >> the fact the I couldn't do log restores while someone
>> is connected to the
>> >> database. I mean, I knew that database restores
>> required exclusive
>> >access,
>> >> but somehow I thought that a read-only database would
>> allow transaction
>> >log
>> >> restores while others are connected to the database.
>> This would be a
>> >major
>> >> oops on my part. I had originally suggested to
>> management that the data
>> >be
>> >> synchronized only once or twice a day, but they
firmly
>> want a solution
>> >that
>> >> allows them to off-load some of the decision support
>> queries/reports that
>> >> they use to be retrieved from this "standby" server,
in
>> order to reduce
>> >the
>> >> resource load on production. This has always been
>> touted as an advantage
>> >to
>> >> having a standby server.
>> >>
>> >> It has taken weeks to iron out the bugs that have
>> popped up in automating
>> >> the backup and restore of 36 databases. Everything
>> from permissions
>> >issues
>> >> to invalid syntax to just plain old timing problems.
>> Now that has been
>> >> running successfully all week and they are ready to
>> point these apps to
>> >that
>> >> server. I was testing that and began to see my
restore
>> log jobs failing,
>> >of
>> >> course.
>> >>
>> >> So, at this point, I don't know quite what I'm going
to
>> have to do in
>> >order
>> >> to get the desired results. Disappointing is not
quite
>> the word I would
>> >use
>> >> to describe the situation if I have to tell them
that I
>> now have to take
>> >> some considerable time to create and test a
replication
>> scenario. But, if
>> >> it is the ONLY way, then I guess that's just what
I'll
>> have to do.
>> >>
>> >> I am mentally exhausted and in desperate need of a
>> workable solution. Its
>> >> been at least 2 years since I've used any type of
>> replication in
>> >production,
>> >> and I am more than a bit fuzzy on what will have to
be
>> done for successful
>> >> implementation.
>> >>
>> >> Ok, guess I've over-explained :-) Thanks for
listening.
>> >> Deborah
>> >>
>> >>
>> >> "chris" <anonymous@.discussions.microsoft.com> wrote
in
>> message
>> >> news:03e301c3c01a$025a2250$a101280a@.phx.gbl...
>> >> > >Since the log restores literally take less than a
>> couple
>> >> > of seconds, is
>> >> > >there a way to kick the users out and
automatically
>> >> > reconnect them? So that
>> >> > >from their perspective the query just took a
couple
>> more
>> >> > seconds to run?
>> >> >
>> >> > Not that I know of. Someone else?
>> >> >
>> >> > >but I'm
>> >> > >now in a time crunch and don't want to generate
any
>> new
>> >> > problems.
>> >> > >
>> >> > >Is transactional replication a straight-forward
>> thing to
>> >> > implement? Are
>> >> > >there any issues with adding that to the plan
that I
>> need
>> >> > to be aware of?
>> >> > >If I were to implement transactional replication,
how
>> >> > would that change the
>> >> > >backup/restore strategy, other than I wouldn't
have
>> to
>> >> > restore the
>> >> > >transaction logs every 10 minutes? Can the
process
>> be
>> >> > completely automated?
>> >> >
>> >> >
>> >> > Replication from my experience had somewhat of a
steep
>> >> > learning curve. It really depends on your
>> circumstances
>> >> > though. Security/ firewalls/ authentication/
disater
>> >> > recovery scenarios/ size of initial snapshot/
>> transferring
>> >> > of logins and jobs/ alloted maintenance time/ the
>> >> > infamous "schema changes to a published table"/ and
>> lets
>> >> > not forget the all time favorite "identity colmuns
in
>> >> > replication"/ ect. If you are on that much of a
time
>> >> > crunch this may not be your best approach.
>> >> >
>> >> >
>> >> > Out of curiosity, how did you suddenly wind up in
this
>> >> > truely bogus situation?
>> >> >
>> >> >
>> >> > >--Original Message--
>> >> > >I'm sure that the log shipping tool in the
Enterprise
>> >> > Edition would work
>> >> > >great, but I do not have Enterprise Edition, nor
>> will we
>> >> > be upgrading to it.
>> >> > >Our environment simply cannot warrant that.
>> >> > >
>> >> > >Since the log restores literally take less than a
>> couple
>> >> > of seconds, is
>> >> > >there a way to kick the users out and
automatically
>> >> > reconnect them? So that
>> >> > >from their perspective the query just took a
couple
>> more
>> >> > seconds to run?
>> >> > >
>> >> > >I have not tried to implement replication in
SQL2K,
>> and I
>> >> > am concerned about
>> >> > >possible maintenance mistakes. I've been reading
up
>> on
>> >> > it in BOL, but I'm
>> >> > >now in a time crunch and don't want to generate
any
>> new
>> >> > problems.
>> >> > >
>> >> > >Is transactional replication a straight-forward
>> thing to
>> >> > implement? Are
>> >> > >there any issues with adding that to the plan
that I
>> need
>> >> > to be aware of?
>> >> > >If I were to implement transactional replication,
how
>> >> > would that change the
>> >> > >backup/restore strategy, other than I wouldn't
have
>> to
>> >> > restore the
>> >> > >transaction logs every 10 minutes? Can the
process
>> be
>> >> > completely automated?
>> >> > >
>> >> > >Sorry to be asking so many questions. I have been
>> >> > reading BOL, but reading
>> >> > >the BOL, while usually helpful, is no substitute
for
>> >> > talking to someone with
>> >> > >real experience.
>> >> > >
>> >> > >Thanks in advance,
>> >> > >Deborah
>> >> > >
>> >> > >
>> >> > >
>> >> > >"chris" <anonymous@.discussions.microsoft.com>
wrote
>> in
>> >> > message
>> >> > >news:0aa201c3c010$9b89b990$a001280a@.phx.gbl...
>> >> > >> You need to be using something like replication
to
>> meet
>> >> > >> your stated requirements. In order for a db to
be
>> >> > restored
>> >> > >> nobody can be accessing it. Log shipping has an
>> option
>> >> > to
>> >> > >> allow users into the db while its not being
>> restored and
>> >> > >> it will kick everony out when the restore is
>> starting,
>> >> > but
>> >> > >> this isnt usually a good idea for a reporting
>> server.
>> >> > >>
>> >> > >>
>> >> > >> >--Original Message--
>> >> > >> >I am trying to use my Standby Server as a
>> Reporting
>> >> > >> Server so that our
>> >> > >> >resource intensive reporting queries can be run
>> against
>> >> > >> it instead of
>> >> > >> >production.
>> >> > >> >
>> >> > >> >I've been successful at automating the backups
and
>> >> > >> restores of all the
>> >> > >> >databases as well as transaction logs from
>> production
>> >> > to
>> >> > >> standby. However,
>> >> > >> >if someone is trying to do a select query at
the
>> time,
>> >> > or
>> >> > >> even just have
>> >> > >> >their reporting application connected to it,
the
>> >> > restore
>> >> > >> of the transaction
>> >> > >> >log fails "due to not being able to gain
exclusive
>> >> > >> access". If one of the
>> >> > >> >transaction log restores fail, they all fail
>> after that
>> >> > >> because the LSNs are
>> >> > >> >off.
>> >> > >> >
>> >> > >> >This is, of course, an urgent problem.
>> Management has
>> >> > >> required that this
>> >> > >> >server be as current as possible for reporting
>> purposes
>> >> > >> and this entire
>> >> > >> >process must be automated.
>> >> > >> >
>> >> > >> >Servers are W2K Server sp4, SQL2K Standard
Edition
>> >> > sp3a.
>> >> > >> >
>> >> > >> >Below is an example of my syntax for the
>> transaction
>> >> > log
>> >> > >> restores. Works
>> >> > >> >like a charm unless something is connected to
the
>> >> > >> database. I had to do it
>> >> > >> >this way because I wanted to save all the
>> transaction
>> >> > log
>> >> > >> files taken
>> >> > >> >throughout the day in case of "point in time"
>> disaster
>> >> > >> recovery. I ended up
>> >> > >> >opening a ticket with Microsoft and this is the
>> best
>> >> > >> solution they could
>> >> > >> >suggest. They said that normally with log
>> shipping,
>> >> > the
>> >> > >> TLog backups
>> >> > >> >overwrite the others in the device. I found it
>> >> > difficult
>> >> > >> to believe that it
>> >> > >> >was expected that every process would work
>> correctly
>> >> > >> every time and you
>> >> > >> >would have no need to ever access a
transaction
>> log
>> >> > >> backup that was
>> >> > >> >previously taken. If it has been overwritten,
>> and you
>> >> > >> try to restore one
>> >> > >> >that is later, SQL send the error about LSNs
not
>> being
>> >> > >> right.
>> >> > >> >
>> >> > >> > Is there something in here that needs to be
>> changed to
>> >> > >> accomplish my task?
>> >> > >> >
>> >> > >> >CREATE proc RestoreApplicationLogs_TLog
>> >> > >> >as
>> >> > >> >declare @.position int
>> >> > >> >declare @.mediasetid int
>> >> > >> >
>> >> > >> >set @.mediasetid = (select media_set_id
>> >> > >> >from NCNSV1015.msdb.dbo.backupmediafamily
>> >> > >> >where logical_device_name
>> = 'ApplicationLogs_TLog')
>> >> > >> >
>> >> > >> >set @.position= (select position from
>> >> > >> NCNSV1015.msdb.dbo.backupset
>> >> > >> >where Backup_set_id = (select max
(backup_set_id)
>> from
>> >> > >> >NCNSV1015.msdb.dbo.backupset where
media_set_id =>> >> > >> @.mediasetid))
>> >> > >> >
>> >> > >> >Restore Log ApplicationLogs
>> >> > >> >from disk
>> >> > >>
>> >> >
= 'd:\mssql\backup\ApplicationLogs\ApplicationLogs_TLog.bak
>> >> > >> '
>> >> > >> >with file = @.position,
>> >> > >> >dbo_only,
>> >> > >> >standby
>> >> > >>
>> >> >
= 'd:\mssql\backup\ApplicationLogs\undo_ApplicationLogs.ldf
>> >> > >> '
>> >> > >> >
>> >> > >> >waitfor delay '00:00:10'
>> >> > >> >GO
>> >> > >> >
>> >> > >> >
>> >> > >> >Please help.
>> >> > >> >
>> >> > >> >Thank you in advance,
>> >> > >> >Deborah
>> >> > >> >
>> >> > >> >
>> >> > >> >.
>> >> > >> >
>> >> > >
>> >> > >
>> >> > >.
>> >> > >
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>