I think I've read some conflicting advice in BOL. Maybe someone can clarify it for me.
Under "Database Mirroring and Database Snapshots" it says:
"You can take advantage of a mirror database that you are maintaining for availability purposes to offload reporting. To use a mirror database for reporting, you can create a database snapshots on the mirror database and direct client connection requests to the most recent snapshot"
To my mind, to enjoy a noteworthy performance gain, that mirror database would need to be on another server.
But then you read under "Database Snapshots":
"Multiple snapshots can exist on a source database and always reside on the same server instance as the database."
Hello, My question is simply: according to BOL, a DB snapshot taken from a Mirror is read-only. Say I wanted a temporary (disposable) read-write DB created from the snapshot. Is there a way to generate a writable temp DB (that's not tempdb) from the snapshot? Thanks,AK
Have 3 reports that runs using a shared schedule to generate historic snapshots. This has be working for months and months but out of nowhere it changed.
So now when ever the schedule runs it will generate the historic snapshot for 1 report but NOT the 2 remaining. As a strange twist the report that is successful changes from time to time...
We will soon be going to a new ERP system using MS SQL 2000. I'm looking into possible backup strategies. The database size will be about 50 Gigs.
We have a SAN, which the new ERP system will be backed up on. I know a little about a SAN, but I need to make decisions about the types of backups to make. We can have 10 active snapshots, so I could use snapshots during the day as a point in time backup. I could also use the SAN snapshot instead of using a full backup. Would it be safe to use the SAN snapshots instead of the normal SQL Server backups? I’m not sure how long it will take to do a full SQL Server backup because the server is still in testing mode. I’m not getting the big picture about when to use snapshots and when to use regular SQL Server backups.
Is it possible in Windows 2000 Server to stop the transactions and flush the buffers for a few seconds to do a snapshot? From what I understand, the database could be left in a suspect state if you use snapshots to restore a database.
HiWhat would be the quickest way to create a backup and revert programon an sql (2000) database?- Can you create a transaction on a database, regardless of theconnections and thenrollback it all via an external program- Could you monitor the changes with profiler and then reverse those?- If desperate, could you backup the db from a tool and then restoreit? (too slow to be practical?)Not sure how to do this so any offers would be appreciatedta
If multiple snapshots for the same report attempt to generate at the same second, is there something in place to prevent them from conflicting with each other?
Our SSRS 2005 application will use a console app that we are writing to change the default parameters on each report and call the CreateReportHistorySnapshot method. This application will be multi-threaded, so there is a possibility that multiple versions of the same report with different parameters will attempt to run at the same time. We need to be sure that these snapshots will not conflict and return the appropriate SnapshotHistoryID for that report run.
From looking at the History table in the ReportServer database it looks like there are uniqueIDs and other keys that would prevent duplicates, but what if the requests come in at the same time? Is SSRS 2005 smart enough to delay for a second so that there is not a duplicate? Since the way you reference these snapshots in your URL is with the Snapshot Time in UTC format, that only goes out to full seconds (2006-05-02T00:00:00).
Sorry for the length, but I could not think of how to condense this.
At the moment, we are using SSRS to report off an Oracle database, if we take a snapshot of the data on the Oracle database and put it within SQL, will we expect an increase in performace/response.
And is it a good idea to keep the snapshot in the same place where SSRS is installed?
I want to share one of my doubt about SSRS and report server, if i use snapshots or cache to my reports in report server, is it going to make increase in performance? if it is going to make increase in performance,then SQLSERVER DATABASE is going to have any burdens, means is it going to down the performance of Database? Pls give me Responce to my Questions....
I am having the huge db of 80 GB and trying to configure merge replication. The intial snapshot application is failing due to some schema issues. I have made some necessary changes to avoid the schema issues. If I try start the merge agent it is going to re-initialize the snapshot and reapplying the whole snapshot will take atleast 10-15 hrs.
Is there any way to resume the snapshot from the point of failure.. avoiding the reapplying all the data which has already got transfered to subscriber.
Does someone know if doing a reindex on a clustered or non-clustered index cause the snapshot file to grow? In other words, is the data that makes up the snapshot copied from the source to the snapshot database? If a normal reindex is done on the underlying database, will it block users from acessing the snapshot? Any help would be appreciated.
I have setup my web synchronisation with very few problems until now. Initially I set the snapshots to generated by the subscriber when they first synchronise, this worked perfectly every time until a bottleneck occured after I reinitialised all the subscribers. When more than one subscriber then initialises the snapshot agent, the snapshot jobs fail because there is already a job running for user distributor_admin. The agent then retries etc, etc.. until all retries have failed. Ultimately only one of the snapshots is successfully created only after all other jobs have failed and run out of retries, it is then left alone to carry out its job.
Following this I made the decision to run with pre-generated snapshots instead so that I can schedule the server load and avoid these bottlenecks. Using the same publication I unchecked the option "Automatically define a partition and generate..." in the publication properties and proceeded to add each of the partitions specifying the correct "Host_Name()" value etc. After this I selected one partition and clicked "Generate the selected snapshots now". The snapshot was duly created but when synchronised the snapshot is not collected and used. Weirdly the merge porcess begins enumerating changes at the publisher followed by downloading 3100 chunks of data before receiving an error concerning the format of the message from the distributor.
If I check the option to allow auto generation, the synchronisation begins absolutely perfectly but only once it has generate a snapshot (Ignoring the one I created previously).
To add further weirdness, I created a small app which would generate the dynamic partitions using RMO. I used the sample code implicitly from BOL and it duly generated the dynamic snapshot job. I can then create a new Job class passing it the JobServer and JobName, it gives me the correct job but I cannot then call the Start() method because it keeps telling me the Job object is not created even though It is clearly enabled as a job in SSMS and can be right_clicked and started from there.
Even partition jobs created from within the SSMS appear as state "Creating" when I make an instance of it using the Job class.
What am I missing? Are my pre-generated snapshots not available to the subscriber because they seem to still be in the creating state? Is this a SQL Server configuration issue or have I setup the publication incorrectly?
Private Sub btnGenerate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenerate.Click
Dim hostName As String = txtSiteID.Text.Trim ' Define the server, database, and publication names Dim publisherName As String = "WDU340" Dim publicationName As String = txtOrgID.Text.Trim Dim publicationDbName As String = publicationName Dim distributorName As String = publisherName
Dim publication As MergePublication Dim partition As MergePartition Dim snapshotAgentJob As MergeDynamicSnapshotJob = Nothing Dim schedule As ReplicationAgentSchedule
' Create a connection to the Distributor to start the Snapshot Agent. Dim distributorConn As ServerConnection = New ServerConnection(distributorName)
Try ' Connect to the Publisher. distributorConn.Connect()
' Set the required properties for the publication. publication = New MergePublication() publication.ConnectionContext = distributorConn publication.Name = publicationName publication.DatabaseName = publicationDbName
' If we can't get the properties for this merge publication, ' then throw an application exception. If (publication.LoadProperties() Or publication.SnapshotAvailable) Then ' Set a weekly schedule for the filtered data snapshot.
If RunJob(publication, snapshotAgentJob, distributorConn, hostName) = False Then
' Set the value of Hostname that defines the data partition. partition = New MergePartition() partition.DynamicFilterHostName = hostName snapshotAgentJob = New MergeDynamicSnapshotJob() snapshotAgentJob.DynamicFilterHostName = hostName
' Create the partition for the publication with the defined schedule. publication.AddMergePartition(partition) publication.AddMergeDynamicSnapshotJobForLateBoundComClients(snapshotAgentJob, schedule)
RunJob(publication, snapshotAgentJob, distributorConn, hostName) End If Else Throw New ApplicationException(String.Format( _ "Settings could not be retrieved for the publication, " + _ " or the initial snapshot has not been generated. " + _ "Ensure that the publication {0} exists on {1} and " + _ "that the Snapshot Agent has run successfully.", _ publicationName, publisherName)) End If Catch ex As Exception ' Do error handling here. MessageBox.Show(String.Format( _ "The partition for '{0}' in the {1} publication could not be created.", _ hostName, publicationName) & ": " & ex.Message) Finally If distributorConn.IsOpen Then distributorConn.Disconnect() End If End Try
End Sub
Private Function RunJob(ByVal publication As MergePublication, ByVal snapshotAgentJob As MergeDynamicSnapshotJob, ByVal distributorConn As ServerConnection, ByVal hostName As String) As Boolean Dim jobs As ArrayList Dim iJob As Integer Dim bExists As Boolean = False jobs = publication.EnumMergeDynamicSnapshotJobs() For iJob = 0 To jobs.Count - 1 snapshotAgentJob = DirectCast(jobs(iJob), MergeDynamicSnapshotJob) If snapshotAgentJob.DynamicFilterHostName = hostName Then 'Run the Job bExists = True Dim server As New Server(distributorConn) Dim job As New Job = (server.JobServer, snapshotAgentJob.Name) MessageBox.Show(String.Format("About to run the dynamic snapshot job: {0} with status: {1}", job.Name, job.State.ToString)) Try job.Start() Catch ex As Exception MessageBox.Show(ex.ToString) Throw End Try Exit For End If Next End Function End Class
I posted last year about getting and Expand All/Collapse All link working in reports, and it does work perfectly for on-demand reporting. I was able to get these working using the boolean report parameter and then the Jump to Report option going back to itself changing that parameter.
The issue that I have just discovered is that when a report with these Expand All/Collapse All links are in a report snapshot, clicking those links will cause it to re-render the report from the datasource at the time the link is clicked. This is an issue for one of the datasources we are using because the data is always changing, so when it goes back to re-render the counts and data returned are completely different then they were when the snapshot was first created. As time goes on the data will no longer even be in the datasource since it can only maintain 4 months of data.
Is there another way to get all detail groups to expand/collapse at once that does not require the report to be completely re-rendered from the datasource?
I am using SQL2005 merge replication and have pull subscribers on a low bandwidth link
I am compressing the snapshot into an alternate folder. Files are not put into the default folder
When I start a synchronization, I would expect the cab file to be copied to the subscriber and then the files to be extracted locally at the subscriber in order to apply the snapshot
However, what appears to be happening is that the files are being extracted from the cab file on the publisher (in a UNC specified directory) and then copied in their uncompressed form to the subscriber - resulting in an extremely slow snapshot application.
Any ideas what I am doing wrong? I have read about the options for using FTP to transfer snapshot files, but I am not clear whether I have to use FTP in order to transmit a compressed snapshot. I don't want to use FTP unless I need to.
Hi everybody, I'm quite new to SQL 2005 and I€™m trying to understand some key concepts regarding replicas. I need to develop an application with characteristics similar to the Sales Order Sample for Merge Replication, on the client side it should run with the express version of sql server and also the synchronization should only work via web. I try to run the sample but I got an exception in the CreateSubscription method on invoking publisherConn.Connect();
I am trying to create daily automatic snapshots by using the code below...
Code Block declare @MyDay varchar(20) declare @query varchar(1000) declare @DatabaseName varchar(128) declare @snapshotName varchar(128) declare @snapDataName varchar(128) declare @snapFileName varchar(128) declare @snapFilePath varchar(128) set @Myday = (Select datename(weekday,getdate())) print 'It is ' + @MyDay Set @DatabaseName ='Cerritos_Net' Set @SnapDataName='Cerritos_Net_Data' Set @SnapshotName ='Cerritos_Net_Snapshot'+'_'+@MyDay Set @SnapFilename ='E:ShareINCOMINGSnapshotsDailyCerritos_Net_Data'+'_'+@MyDay+'.ss' Print 'Snapshot name is ' +@SnapshotName select * from sys.databases where source_database_id =db_id(@databasename) and name = @SnapshotName if @@rowcount <>0 begin set @query = 'Drop database '+ @SnapshotName print @query exec(@query) end set @query ='Create database '+ @SnapshotName + ' on (Name = '''+@snapDataName +', FileName="' +@SnapFilename +'") AS SNAPSHOT of '+ @databasename+';' print @query exec(@query)
But I keep getting this error
Code Block It is Tuesday Snapshot name is Cerritos_Net_Snapshot_Tuesday (0 row(s) affected) Create database Cerritos_Net_Snapshot_Tuesday on (Name = 'Cerritos_Net_Data, FileName="E:ShareINCOMINGSnapshotsDailyCerritos_Net_Data_Tuesday.ss") AS SNAPSHOT of Cerritos_Net; Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark after the character string 'Cerritos_Net_Data, FileName="E:ShareINCOMINGSnapshotsDailyCerritos_Net_Data_Tuesday.ss") AS SNAPSHOT of Cerritos_Net;'. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'Cerritos_Net_Data, FileName="E:ShareINCOMINGSnapshotsDailyCerritos_Net_Data_Tuesday.ss") AS SNAPSHOT of Cerritos_Net;'.
We are running a Mirrored instance of SQL Server 2005 SP2 (in High Performance mode) and using snapshots on the Mirror to provide a reporting solution.
The problem is that quite frequently the Database Snapshot starts going through a Recovery Process. This is causing large delays in the reporting process.
The sequence of events in the SQL logs is 1. An Event for "Starting up Database Snapshotname" 2 A series of events "Analysis of Database Snapshotname is X% complete" This can take up to 2 minutes 3. A series of events "Recovery of Database Snapshotname is X% complete" This recovery process can take up to 1hr 45 minutes.
The database is running on new servers and there are no apparent disk problems.
Can anybody advise on why a snapshot should start a recovery process and how to prevent this Regards
I have a database which contains more than a hundred publications and the clients need a snapshot for each one. Is there a way of automating this process of creating a snapshot? Like a job or something.
I would like to know what is the difference between a snapshot and a cached instance in SSRS?
Which one has the best performances and which one is the best for multiple users and reports containing parameters (the parameters are then passed in the where clause of the sql code; ex: WHERE IN(@param1))?
I am programatically adding and creating snapshots and subscriptions in report services from my application I created.
1) Is there a way to create a snapshot with user defined parameters? If so how would you do this programatically with the Web service?
2) How do you create a subscription with user defined parameters? Do you do this with a data driven subscription? or do you use another webservice method to do this?
Hi. I'm evaluating performance features between Enterprise Edition and Standard Edition. I've heard that Database Snapshots are only available in Enterprise Edition, but cannot find anything that proves/disproves this.
Does anyone currently use Database Snapshots in Standard Edition SQL Server 2005? (Not talking about the Replication Database Snapshots.)
Quick script to try on a Standard Edition SQL Server:
CREATE DATABASE AdventureWorks_dbss1800 ON ( NAME = AdventureWorks_Data, FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_data_1800.ss' ) AS SNAPSHOT OF AdventureWorks;
I've been running web Synchronisation for over a month. Just today some subscribers received a message warning of an "OS Error" could not retrieve file "dynsnapshotvalidation.tok". Sometimes they also got an error regarding low or no virtual memory. After much investigation it seems that the Web Server hosting the web sync IIS had literally no space left on the system c: drive. It also turns out that the \WindowsTEMP folder contained over 9GB of snapshot files.
It seems that when the Web server collects snapshot files from Data Server to deliver to the subscriber it creates a Temp copy on it's own local system. Unfortunately this temp data is not cleaned up and over time has jammed up the system and caused this failure.
Is there an answer to this or does it involve manually checking the server from time to time to clean-up the TEMP folder?
We have a statutory requirement to keep 5 years of history, so I've checked the option in the Site Settings page in the Report Manager to "Keep an unlimited number of snapshots in report history". The drives are partitioned as follows:
C: -- OS; 20GB
D: -- Apps; 20GB
E: --Data: 160GB
I want to force the report history snapshots onto the data (E:) drive, but I can't figure out what setting will force them there. Can anyone tell me how to set the path to the reports snapshots folder?
I have merge replication set up between sql mobile and sql 2005 (just recently upgraded from sql 2000). My publication has around 40 published tables, about half of them are filtered. The other day I noticed that the initial replication to get the entire database was downloading the same tables multiple times. The last test was each table was downloaded 10 times. So I did some research which brought me to the following fix, which was to implement dynamic snapshots.
However, when I try to replicate, it generates a dynamic snapshot, but it fails with the following error€¦
An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199428)
I haven€™t changed the snapshot locations, and if I don€™t use dynamic snapshots it works fine. I€™ve tweaked the permissions (in development) to the point where everyone has full permission on the IIS share, and snapshot folder with no luck.
Using SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....
I need to set up asyncronous data replication across two clustered instances of SQL 2012 across 2 Datacenters. Both the datacenters have a common domain however the vlans are different. There are only 3 small databases on the primary instance.
any issue in setting up mirroring in this case as vlans are different.
We have a distributed system running on IP-VPN Network. Every site in the network has one SQL Server 2000 standard server database running on Windows 20003 server.
I am setting up Merge replication on every site where the database is updates are consolidated in the main database.
I have no problem in setting up merge replication, and system is working ok as of now.
But some of remote sites are located in different geographical locations and I want to transfer the Initial snapshots using a removable device, which I want to manually restore at those remote sites, and then enable the merge replication.
I have already read thru many manuals, websites etc to find out how to restore the initial snapshots on the subscriber, but can not find any info.
This is PUSH replication where the distributor is pushing the updates to the different remote subscribers.
My deadline for implementation is approaching and I will very much appreciate any help in this matter.
Our last CheckDB failed on the 02/09. We were having problems with our SAN then and that got sorted out. The databases that it failed still has the snapshot showing in SQL Management Studio, screenshot exemple below (server name blanked out)
I reran CheckDB on the 04/09 on all the databases and the same databases that failed on the 02/09 failed again. I noticed in the log file, it tried to use the same snapshot file created on  02/09 to run CheckBD
I ran CheckDB manually on the databases that failed "DBCC CheckDB (WSS_Content_ce) with all_errormsgs"Â and did not get any errors back.Â
How do I remove the snapshot databases? We had a problem on 02/09 so the snapshot copy obvisouly has problems. I'd like to remove these snapshots and rerun a CheckDB on all the databases to ensure all is ok.
Is there a way to take a snapshot of a table at precisely the same time on multiple servers and write the table snapshot to another 'work' database? I'm asking in this forum hoping that perhaps SSIS has a built in tool to help with this. I'm wanting to audit table(s) accross seven different servers. In order to do this I want to make sure that I have the table at the same moment in time on all the servers. I plan on using the tablediff utility to then compare each table on each server against one another. If somebody knows a better method to do this please let me know.
I have a report that runs monthly and they want to see the previous 2 months of data. This data is stored no place other than the snapshot of the same report for the previous 2 months. Is there any way I can display the last 2 snapshots/history withing my report? Thanks.
We have a SSRS report that runs on snapshots. The report contains a textbox that displays the report name using the Global!ReportName built-in field.Â
Recently, we changed the RDL name to something else but this seems to have affected the snapshots too. The older snapshots (generated before the name change) are showing the updated name.
Just wondering if there is some way to say that the snapshot will show the report name based on the time it was generated (same as the ExecutionTime built-in field). Why is there a difference?
