Has anyone tried compressing/uncompressing transaction log files when implementing log shipping?
I saw an article on SQL Lite Speed product, which supports compression with log shipping, I was trying to do it without any third party product.
Hi guys,
I have a server in a datacenter (SQL 2005 ent) that collects large quantities of data from our visitors. I need to set up a secondary database in our office (different geographic location) that will server 2 purposes, 1, a backup of the database and 2, allow us to perform complex queries on the data.
There is no updating of the data on the secondary server so no changes need to go back to the primary server. A database in standby mode is fine and users on the secondary server can be disconnected when it's being updated.
I have transaction log shipping working well in a staging environment (LAN). My first question is is there any reason why transaction log shipping would not work over a WAN with a VPN connection?
And my second question is can I compress the trn files for transport over the WAN. If I manually compress the files with winzip they compress by 98%. That translates into a huge saving when I am leasing a line to transport these files.
Thanks in advance
Stephen
Hi
I could not able to find Forums in regards to 'Log Shipping' thats why posting this question in here. Appriciate if someone can provide me answers depends on their experience.
Can we switch database recovery model when log shipping is turned on ?
We want to switch from Full Recovery to Bulk Logged Recovery to make sure Bulk Insert operations during the after hours load process will have some performance gain.
Is there any possibility of loosing data ?
Thanks
Hello,
I have been wanting to compress my database. I am not really sure how this is done. I was looking on Enterprise Mangr. and if you right click on the db and go to all tasks, there is an option to shrink database. Is this the way you would compress your database, or are there other ways of doing this?
Thanks for all the help.
Does DTS do any data compression when transferring data from a table on Server A to another table on server B?
View 1 Replies View RelatedCan anyone tell me whether there is any data compression in SQL6.5. Have concerns with network traffic, and was wondering if data compression was a function SQL6.5, or if the data compression have to be coded into the actual database?
View 1 Replies View RelatedHi all,
My application send/retrieve large data from and to the database over the internet. Is there anyway or method that i can compress the query before submit to database server? really appreciate any advice and comments.
regards
Desmond
We have run some tests on our application. Average message is about 2.5 MB. Messages are send once every 30 minutes. This is 3.5 Gb per month for one site. Now we already have three sites that will be sending this messages. This will be VERY high load on the WAN channel, and will cost us a LOT of money .
View 2 Replies View RelatedDoes SQL Server replication impliment any kind of compression? It seems to me that this would be very helpful for congested WAN links and costly merge replication.
View 5 Replies View RelatedHi, I was looking for a column compression functionality in SQL Server Compact and it seems that it doesn't exist (maybe I'm wrong?). I wonder if the SQL Server Team plan to implement column compression and if yes, when can we expect it?Thank you for your help!
View 3 Replies View RelatedHi,
I 'm sure I am missing something obvious, hopefully someone could point it out. After a failover log shipping, I want to fail back to my inital Primary server database; however, my database is marked as loading. How can I mark it as normal?
I did the failover as follow:
I did a failover log shipping from the 2 server Sv1 (Primary) and Sv2 (Secondary) by doing the following
1) Stop the primary database by using sp_change_primary_role (Sv1)
2) Change the 2nd server to primary server by running sp_change_secondary_role (Sv2)
3) Change the monitor role by running sp-change_monitor_role (Sv2)
4) Resolve the log ins - (Sv2)
5) Now I want to fail back - I copy the TRN files to Sv1 - use SQL Ent to restore the database at point in time. The task is done; however, the database is still mark as loading. I could not use sp_dboption.
I appreciate any suggestion.
Thanks in advance
Hi All
I have a database which is 72GB, which is backed up every night as part of the maintenance plan. I have plenty of storage space, and the server that runs the database is fairly powerful (quad-processor 3.2ghz, 64bit, 48GB RAM) and is part of an active-passive cluster. The database backup is also copied to a SAN location.
My issue is with the size of the backup file. As part of the Disaster Recovery plan, I need to copy this database backup file accross the network to a remote site, so that in the event of a disaster at the site, business can continue at the remote site after restoring the database backup file. However, my database backup file is so big that I cannot copy it accross the network in time for the next morning. I have tried using WinRar and have managed to achieve a file about 20% of its original size, but it takes 2 hours to produce this file.
Is there any recommended reeading for this type of issue? Log shipping / mirroring has been investigated and will be part of the DR model but the 'powers that be' insist on having a full copy performed to the remote site.
Any suggestions? Thanks in advance guys n gals :-)
I am wanting to use backup compression on a few sql servers (2008R2 and 2012). I have never touched compression before and always just gone with the default.I use Ola Hallengren's scripts to do the backups which, if not specifically specified, will use the server default.Backups (FULL and LOG) have been happening successfully on these servers for years.SQL won't care that the previous backup in the set was uncompressed or that the hourly transaction log backups previously taken were uncompressed?Restore statements (T-SQL) will be identical?From everything I am reading it is simply a case of setting the configuration and acting like nothing changed but I just wanted to be 100% certain.1 of the servers is a SharePoint backend.All of the backup files from all servers are picked up by Commvault backup system.
View 4 Replies View RelatedI have a report being utilized for return address labels, conforming to Avery 5167. I have tried designing both as a table and as data in rectangles. Since these are return labels their is only one instance of data replicated for all textboxes, therefore the columns are of consistant length.
The report has seven columns of precise measurement, the data filled colums are set as 1.75in, 0.25in, and between the data columns are blank columns set to .3125in, 0.25in. I have also tried to fill data into the blank colums and set the font color to white. All report margins are set to 0in and the table location is 0.04167in, 0.125in. all textboxes have the properties for increase/decrease to accomadate turned off.
The biggest issue I am having is in printing from the deployed report versus printing while designing. I have adjusted for the glitch in margins for RS2005 and have printed succussfully prior to deployment to adhere to the specific measures for the label, report margins and textbox height and width and blank column spacing.
However, after deploying the report the data spacing seems to be compressed when printed. I am not getting the same measure between data fields as I had when designing the report. The entire printout seems to be slightly compressed.
When printed from design colums 1,3,5, and 7 start respectively at .3125in,2.375in,4.3756in,6.4375in from the edge of the page.
When printed fromt he deployed report columns 1,3,5 and7 start respectively at .3125in,2.3125in,4.28125in,6.28125in.
The progression of compressed measure seems to increase from left to right inthat by the time the report is printed via the deployed report the "Tab" area is a difference of .15625in which when printing for very precise template format this creates a problem. As well as a headache when one thinks the report is correct before deployment.
Does anyone know a wayto compress data between two database connections over "low bandwidth" lines in order to speedup datatransfer?
Used connections are Oracle<->SQL2000 and SQL2000<->SQL2000.
Where can I get detailed notes on "Data compression in SQL server"?It would be useful if the notes are accompanied with examples.
View 4 Replies View RelatedWe're changing Netbackup to DPM but not sure if "backup compression default" option should be ON or OFF or doesn't matter.
View 3 Replies View RelatedSuppose a database server and client are separated by a low bandwidthlink such as DSL, and the client repeatedly issues a query for, say, acurrent product list.Suppose the product list is large, but only a handful of entries havetypically changed between queries. It would be nice if only the changesfrom last query to current one could be sent, saving bandwidth.Is there any way to do this?Thanks,--"Always look on the bright side of life."To reply by email, replace no.spam with my last name.
View 3 Replies View RelatedWe have a number of large binary xml messages. I would like to compress the XML before converting to VARBINARY as much as possible. What is the recommended way to handle this if the conversions are happening in TSQL? I will also need to be able to decompress in .NET code, so I'm wondering if the best way is to just use CLR Integration for the marshaling routines, rather than building the marshaled structure in SQL. Any suggestions on reducing the XML payload size for extremely large messages?
View 1 Replies View RelatedWill the 2008 edition of SQL Server Express contain the new row level compression features? Is it available in the current CTP for us to try out?
Thanks
my snapshot is exceeding the compression limit.
is there a way to configure the server to have a higher compression limit
thanks,
joey
I have huge table, often I need to query the table on the whole; ie., data may required at any part of the table. So I think of to compress the table with the aim of reducing I/O.
1. is that really suitable for this requirement? If So which compression is good (Row or page )?
2. Or is there any other alternate to reduce the I/O (Except Indexing)?
3. Pros and cons ?
Hi, I back up SQL Server 2000 and SQL server 2005 databases to hard disk using the SQL Server Backup Wizard and maintenance plans. Then, I copy the resulting backups to tape using third party tape backup software and compression by the backup software and hardware. I do not use the SQL Server Agent available for the third party backup software. Is this acceptable, or does the compression performed by the third party backup system introduce opportunities for database corruption or other negative effects?
Thanks
I tried to disable the data compression for granular backup purposes on SQL 2014 with following query: EXEC [dbo].[prc_EnablePrefixCompression] @online = 0, @disable = 1
I received following error message: Msg 2812, Level 16, State 62, Line 1...Could not find stored procedure 'dbo.prc_EnablePrefixCompression'.
If I'm doing data compression(page level) does it rebuild indexes too? and how about stats, does it update stats too?
View 4 Replies View RelatedCurrent am using the below script it is working fine i need use ENCRYPTION script where i should add this
NOREWIND, NOUNLOAD, COMPRESSION,
ENCRYPTION(ALGORITHM = AES_256,
SERVER CERTIFICATE = [BackupCertificate]), STATS = 10
USE [master]
GO
[code]...
I print 4x1 inch labels, anywhere from a few to a few thousand. It's all done from an asp.net page with a 9.0 report viewer in local processing mode.
108 Labels = 37MB or so.... this is totally unacceptable for something that when zipped becomes 1.3mb.
I thought version 9.0 was supposed to have PDF compression... Is there some kind of setting I'm not seeing any documentation for somwhere? how do i enable it?
This better not just be a server processing mode feature like the print button again... I've already taken plenty of heat from the bosses just for that alone. Try explaining the difference between a local report and a server report to your boss and then try to come up with a real world explanation as to why he can print some reports in some programs and is forced to export and printer them from adobe in other programs... please don't let me die have mercy.
I got into confusion while working on DB Growth report for 2012 databases which has compression on.I am analyzing the DB Growth based on MSDB..dbo.backupset table which stores the backup information.
But here it gets tricky, In Previous versions we use "backup_size" column to get actual backup size and estimate the db growth based on the previous all backup file info. But now since compression is on in 2012 the "backup_size" colmuns gives a compressed file size(If i am right) so how do you know the actual backup size to estimate the db growth over a period of time??
I found it pretty interesting. I checked the size of a database, before implementing database compression across all the user tables in a database. And Post implementation of compression too I checked the size of the database.
I did not find any difference. But if I expand the table and check propetires->storage and I can see that PAGE compression is implemented across all the tables, but no compaction in the size of the db. It still remains the same.
ALTER TABLE [dbo].[TableNameExample] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE)
The table is 110 GB, so will take time to compress. But it is one table in a database with 60 tables. Why would executing this statement, cause lock timeout on Object Explorer in SQL Server Management Studio? Users cannot drill down objects in this database without getting lock timeout.
When I cancel the compression script, users of SSMS can access objects in this database again from the GUI. Why does compressing a specific table affect access to all tables metadata? I cannot find anything on the internet but I am sure this has happened to other people.
Could somebody please guide me on how best to Implement SQL Server Log Shipping. I have gone through BOL without luck. Thanks.
View 2 Replies View RelatedI need to create a RO copy of a production DB owned by an outside company. We are connectd via a WAN link, but cannot use replication. They are proposing using an initial load via tape, and sending us a text file nightly with the days changes to the DB. We will then need to load that data using BCP, DTS or some other method. Does any one have any ideas on using log shipping instead of the text file. It would only be practical to get a fresh load of the entire DB once a quarter or once a month at most. It is a 40+ GB database and we are expecting 100 to 200 MB of logs per night. For business reasons, we are limited to some type of file transfer mechanism for the data transfer, and cannot really change their backup schedule which is nightly fullbackups and tlogs every 30 minutes.
View 1 Replies View RelatedCan anyone point out me , What does Log shipping mean ?
Is it backup of Log file ? or moving backup logs to any other destination ?
Thanks
Karthik