Compressed Snapshot With Push Subscribers
Oct 16, 2007
Hi,
I have a server in our central location which is a compressed snapshot publisher. I have 2 push subscribers in remote locations on very slow WAN links. I would like the snapshot cabinet file to be uncompressed at the subscribers location rather than the publisher location. Is this possible with push subscribers? I want to manage the pushing of data to the remote subscribers from the publisher location.
I understand the default with push subscriptions is to uncompress the cabinet file at the publisher location.
Thanks,
Mark.
View 3 Replies
ADVERTISEMENT
Jul 19, 2007
Noticed weird activity today with pull merge.
When your snapshot is set to be delivered via ftp and compressed in a cab file. If you add a new article to your publication and re-run the snapshot the agent will be unable to pull the snapshot down as it for some reason doesnt think its compressed. It is failing to find the scripts it needs inside the cab file despite the cab file existing in the correct location.
Here is the error.
2007-07-19 09:57:29.855 Snapshot files will be downloaded via ftp
2007-07-19 09:57:29.886 Connecting to ftp site 'SQL3'
2007-07-19 09:57:29.933 The schema script 'empActive_127.sch' could not be propagated to the subscriber.
2007-07-19 09:57:29.933 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'empActive_127.sch' could not be propagated to the subscriber.
2007-07-19 09:57:29.933 Category:AGENT
Source: SQL2SQL2005
Number: 20033
Message: The process could not retrieve file 'SQL3_CCUSA_ATLAS_SYSTEM TABLES/20070719055712/empActive_127.sch' from the FTP site 'SQL3'.
2007-07-19 09:57:29.949 CategoryS
Source:
Number: 12003
Message: 200 Type set to I.
200 PORT command successful.
550 SQL3_CCUSA_ATLAS_SYSTEM TABLES/20070719055712/empActive_127.sch: The system cannot find the file specified.
550 SQL3_CCUSA_ATLAS_SYSTEM TABLES/20070719055712/empActive_127.sch: The system cannot find
View 1 Replies
View Related
Oct 2, 2007
I need a way to push create index statements drop to the subscribers. If I can use replication to distribute index creation that would be great. We don't allow our clients to make any schema changes directly. We are using transaction and merge replication.
View 4 Replies
View Related
Jan 7, 2008
Hi all,
I've been looking for the right stored procedure to create a push subscription for publication but i haven't had much luck so far. So I was wondering if you guys know which SP is appropriate, that'd be super great!! thanks guys!
note. the sp should run on the subscriber database.
thanks again
View 2 Replies
View Related
Mar 10, 2006
Hi,
I'm converting a replication script from SQL 2000 to SQL 2005.
I am getting an error with push merge with no way to figure out what is wrong.
I've configured replication on a single XP server in SQL 2005 RTM version.
I have a push merge set up between A and B and between B and C. All 3 databases are 9.0 compatibility.
The snapshot and merge jobs for the A to B run fine with no errors, and merge replicates ok.
The snapshot for B to C fails with this message:
Message
2006-03-09 17:30:35.94 ---------------------------------------------
2006-03-09 17:30:35.94 -BcpBatchSize 100000
2006-03-09 17:30:35.94 -HistoryVerboseLevel 2
2006-03-09 17:30:35.94 -LoginTimeout 15
2006-03-09 17:30:35.94 -QueryTimeout 1800
2006-03-09 17:30:35.94 ---------------------------------------------
2006-03-09 17:30:35.95 Connecting to Publisher 'MyInstance'
2006-03-09 17:30:35.97 Publisher database compatibility level is set to 90.
2006-03-09 17:30:35.97 Retrieving publication and article information from the publisher database 'MyInstance.MyDB'
2006-03-09 17:30:36.22 [0%] The replication agent had encountered an exception.
2006-03-09 17:30:36.22 Source: Replication
2006-03-09 17:30:36.22 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2006-03-09 17:30:36.22 Exception Message: Data is Null. This method or property cannot be called on Null values.
2006-03-09 17:30:36.22 Message Code: 52006
2006-03-09 17:30:36.22
Love that exception message: "Data is Null" - very helpful to someone who is clairvoyant perhaps.
I checked the snapshot bcp files. The tables being merged all have data.
A sample add article command is:
exec sp_addmergearticle @publication = N'MyMerge', @article = N'Phone', @processing_order = 4, @source_owner = N'dbo', @source_object = N'Phone', @type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script = null, @schema_option = 0x000000004C42CDDF, @article_resolver = null, @subset_filterclause = null, @vertical_partition = N'false', @destination_owner = N'dbo', @verify_resolver_signature = 0, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @identityrangemanagementoption = N'none' ,@force_invalidate_snapshot = 1,@force_reinit_subscription = 1
If you have any ideas on how to fix this, I'd be most grateful. As it is after 6pm I probably won't read this again until morning. Thanks for any suggestions.
View 19 Replies
View Related
Mar 15, 2006
We need to configure a way to publish from development to testing and from testing to production. An easy, automated process would be best.
View 1 Replies
View Related
Jun 20, 2007
Hi
I seem to have a strange problem when applying a snapshot when the tables in the publication have been updated while the snapshot was being generated.
Say for example there is a table called RMAReplacedItem in the publication. When the snapshot starts being applied to the subscriber, a stored procedure called sp_MSins_RMAReplacedItem_msrepl_css gets created that handles an insert if the row already exists (ie it updates the row rather than inserting it). However, after all the data has been loaded into the tables, instead of calling this procedure, it tries to call one called sp_MSins_RMAReplacedIte_msrepl_cssm - it takes the last letter of the table name and adds it to the end of the procedure name.
The worst part is that this causes the application of the snapshot to fail, but it doesnt report what the error is, and instead it just tries applying the snapshot again. The only way i have managed to find which call is failing is to run profiler against the subscriber while the snapshot is being applied and see what errors.
I have run sp_broswereplcmds and the data in there is what is applied to the subscriber - ie the wrong procedure name.
All the servers involved are running sql 2005 service pack 2. The publisher and subscriber were both upgraded from sql 2000, but the distribution server is a fresh install of sql 2005.
View 1 Replies
View Related
Apr 27, 2007
Hello,
I have a table of compressed data and am looking for an efficient way to expand the data for reporting purposes.
The table is used to store the number of hours a given contractor works and is stored in the following fashion:
cntHours
58
20
58
20
The first row represents the number of sequential days where an employee worked the same # of hours. Once the # of hours changes, a new record is created. In this simple example, the first row shows an employee working Monday-Friday (5) for a total of 8 hours each day. The second row represents the weekend (2 days) where the employee worked 0 hours.
What I need to do is explode this out to show 1 record per day. Ideally I'd like to write a function to do this as I would be linking to another table which has the start and end date for the contractor and would allow me to apply individual dates to each record based on the contractor start date through to the end date.
Thanks for any help you can provide!
View 2 Replies
View Related
Feb 2, 2007
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.
Any suggestions would be much appreciated
aero1
View 8 Replies
View Related
Jun 11, 2015
I just completed a copy-Only compressed backup of a DB (with a FULL Recovery Model ) on SQL Server 2012 and the resulting backup (the bak file) is 1/100th the size of the data & log file. Is the compression in SQL Server 2012 just that good or did something else happen that I did not catch? Below is the T-SQL to re-create the backup. The size of the data file is 750MB and the log file is 75GB and is %95 used according to the SQLPERF command.
Does the compression in SQL Server 2012 simply that good
BACKUP DATABASE [MYBIGOLEDB] TO DISK = N'Z:Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupMYBIGOLEDB_20150611.bak' WITH COPY_ONLY, NOFORMAT, INIT, NAME = N'MYBIGOLEDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
View 1 Replies
View Related
Nov 23, 2005
One way to save storage space is to put the SQL data files into a compressedfile. Has anyone got any idea how this will affect the query speed?
View 2 Replies
View Related
Apr 24, 2007
Hi everyone,
already tried this in other SQL forums, but maybe i have some luck here.
I need mainly to restore database backups from customers. They arrive in all kind of formats (zip, rar, gz). I'd like to be able to restore those directly from the compressed file, because i'm talking up to 7GB rar files which take a while to uncompress in a separate step.
I'm working for 6 years in R&D environments, but mostly on Linux/Oracle where this is an easy task using pipes, but i haven't found a sinlge web page, post or even script to do this with MSSQL. The VDI is not really what i'm looking for, so aren't backup software like SQLBackup, Litespeed etc. because i can't force the customer to use those.
Anybody any idea or even the same problem maybe with solution?
Thx.
View 10 Replies
View Related
Jan 31, 2006
I am trying to read a 36 byte files that contains compressed data. I create my Flat File data source and SSIS reads it fine UNTIL it hits a x00 in the file. Then it stops reading and I can't get any data after it. There is data after the x00. Here the entire hex string: C7 C7 CF 6A 00 00 05 02 3D 03 21 01 E0 02 00 00 00 00 00 00 00 00 3D 3C 1E FD 02 C8 00 00 00 AE 41 E3 28 7C
To test, I changed the two x00 in bytes 5 and 6 to x01 and SSIS read until the next x00.
Anyone have any ideas?
Thanks,
Jack Lavender
View 3 Replies
View Related
Jan 25, 2007
Hi all,
I am trying to use bcp to output data to a compressed (zipped) folder.
The bcp command is called from a step in scheduled job in SQL 2005 (T-SQL) similar to:
SET@chvCommand =
'bcp [working_t] out D:EprojectsEdataCdata200701.dat'
+ ' -c -STPISQL -T'
EXECUTE master.dbo.xp_cmdshell @chvCommand, NO_OUTPUT
.... where Cdata is a compressed (zipped) folder.
The scheduled job seems to work without errors, but afterwards there is nothing in the compressed folder.
If Cdata is a regular folder everything works fine.
Thanks for you insight.
View 8 Replies
View Related
Nov 2, 2015
I have this bcp
EXEC xp_cmdshell 'bcp "SELECT top 10 * FROM [db].dbo.[u_activity]" queryout "C:bcpCustomers.csv" -c -b 10000 -t~ -S 10.20.8.149 -U user-P password'
which throws the data into a csv, but the issue is I also need to compress the file down in a .rar file,
can this be done in a SP while still executing the above bcp ?
View 2 Replies
View Related
Apr 14, 2014
Source System : Oracle
Target System : Ms Sql Server 2012
ETL Tool Used : SSIS
My source data is present in XML File which is stored in CLOB column Of Oracle. CLOB column is compressed.I need to Migrate data by Uncompressing XML to SQL 2012 .
Do I need to define XML column in SQL Server 2012 for storing Uncompressed CLOB values ?
How to uncompress the clob and extract the required data from XML using SSIS .
View 2 Replies
View Related
Sep 14, 2012
I have created a new report.what i do in application is i compress the image and save it in database.now i need to retrieve the compressed image and display in the report. I have used the following code to decompress the binary data save in the image.
I dont know after that what should i do. How to show the picture in SSRS Report. I need to show picture in many reports.one of my doubt is how to call this function in SSRS Report. The function accepts input as byte but in database the column in varbinary.
Should I convert the input type of function to varbinary instead of byte array?
Public Function Decompress(ByVal arr As Byte()) As Byte()
Dim notCompressed As Boolean
notCompressed = False
Dim MS As MemoryStream
MS = New MemoryStream()
[Code] ....
View 4 Replies
View Related
Feb 15, 2007
Hi, I am trying to lean ASP.NET. So I was trying to install SQL Server too. But, I could not install. The aleart box popped up:
"SQL Server set up cannot install files to the compressed or encrypted folder: C:Program FilesMicrosoft SQL Server"
Before this, I also got a warning that the hardware of my computer does not meet the hardware requirement.
I would like to know how I can install SQL Server into my computer. Or, shouldn't I do that? To learn ASP.NET I want to have a database ready. If I can not use SQL Server, I may try Access or something smaller...
Thanks in advance.
yyokota2
View 1 Replies
View Related
May 21, 2007
I have a sql 2005 publisher and distributor and a sql 2000 subscribers. for some reason on one of the subscribers i'm getting errors that it can't replicate the UDT's. i tried a new snapshot and made sure it was set not to replicate UDT's but i'm still getting Create Type errors.
would anyone have any idea why it's trying to create UDT's at the subscriber when i specify not to replicate UDT's?
View 5 Replies
View Related
Jun 29, 2004
if there is something wrong with the network link between publishing server and subscribing server,and will not be repaired in a few days ,what is the influence to immediate updating subscribers?????
View 1 Replies
View Related
Nov 10, 2006
I have a database on SQL Sever 2005 SP1 against which a Publication has been defined and to which many servers (both Workgroup and Express editions - SP1) Subscribe to.
I would like to be able to distinguish between the Publisher and the Subscribers programmatically via T-SQL.
From reading BoL and various forums it appears that the IsPublished, IsMergePublished and IsSubscribed options of the DatabasePropertyEx function should give me this information.
However within all our tested environments, whilst the IsMergePublished option returns expected values. IsPublished and IsSubscribed both return 0 on all servers (the Publisher and Subscribers).
Is this a know issue and how can I rectify the problem or alternatively does anyone know of another method to distinguishing between the Publishers and Subscribers.
Thanks
View 3 Replies
View Related
Nov 9, 2015
Is it possible to replicate data from one publishers to a multiple subscribers in transaction replication? In other words I have Server A, Server B, Server C with databases A,B,C respectively. I need to replicate 10 articles from A to B but only 1 from A to C. In other words I want to use the same publisher but select one article from that publisher instead of all 10 to Server C/database C.
If it is possible what will be the drawbacks of such implementation? Will performance be a hit?
View 5 Replies
View Related
Jan 25, 2007
Hi,
I am Using Transactional Replication with Updatable Subscriptions. Is there any limitations in the number of subscribers (Servers) used for this type of replication?
I have a scenario of configuring this replication in 60 Subscribers(Servers). And the replication should be in the Continuous running mode. Will Transactional Replication with Updatable Subscriptions work in this scenario??? Or is it meant to work for less than 10 subscribers?
Please reply asap.
Thanks in advance.
Regards,
Swapna.B.
View 3 Replies
View Related
Apr 21, 2006
Hi to all,
I have a merge (pull) replication between SQL Server 2005 and SQL Server Express clients.
Data synchronisation is ok, and I already made some schema changes (like adding new columns) at the publisher database which were applied to the subscriber as they should.
Now this doesn't work anymore. New columns added at the publisher (with ALTER TABLE or Management Studio) are no longer replicated to the subscribers.
I don't get any error messages, and I can't find any hints in the logs why this has stopped working.
The "Replicate_Schema_Changes" is still set to TRUE (in general, none of the publication options had been changed).
I tried sp_enumeratependingschemachanges to find out any "bad changes" put the sp returns nothing.
I remember that the last thing I did was adding a default constraint, which was replicated successfully to the subscribers.
Can anybody help?
Michael
View 8 Replies
View Related
Feb 7, 2007
I use merge replication and the topology is central publication. I have 20 subscribers.
when I run agent, I got message "No data needed to be merged". Publisher doesn't send data to this subscriber. When I update date again, I can send data to subscriber.
Why?!
How replication works?!
and How specifies records that must be send to the subscriber?!
Publisher and subscriber work with SQL 2000 with sp 3 installed on windows server 2003.
Please help me.
Thanks.
View 2 Replies
View Related
Feb 7, 2008
I am trying to figure out some scalability-related requirements for the project I'm working on, and unfortunately I cannot find out an answer to one of the questions by test-running, so I have to ask it in hope that someone has a definitive answer.
When two - or, more - different (distinct) users/subscribers
running the Report Manager from two different machines
[independently of each other] create their two subscriptions
with almost exactly the same parameters (same report name, same report rendering format, to be delivered via email at 6:00 AM every day, etc.),
and the only difference between those subscriptions is in email addresses (to whom the report shall be delivered)...
... when those subscriptions get executed at 6:00 AM,
are they executed only once (because those 2 subscriptions are the same except by whom they were created and to whom they should be delivered),
OR
is each of them executed separately?
In other words, if multiple users create absolutely the same subscriptions (except for a parameter to whom they should be delivered), how many times does the Report Server run that report?
Thank you very much.
View 1 Replies
View Related
Jan 8, 2004
When I try to open the subscribers tab in the publisher and distributor properties in SQL Enterprise Manager I get the following error:
SQL Enterprise Manager could not retrieve the list of registered subscribers.
Error 8152: String or binary data will be truncated
Anyone who has experienced similar problems or has a solution?
Best Regards
MF
View 1 Replies
View Related
May 26, 2007
We are running merge replication, SQL Server 2005 Enterprise with SQL Mobile 2005 (Windows Mobile 5) subscribers. Partitions are filtered on HOST_ID.
Ocassionally we experience a situation where a subscriber experiences an unusually long synchronization duration, and upon examining Replication Monitor, it appears that twice the number of rows, or X the number of rows (up to 7 times the number of rows) that should have been inserted are recorded as synchronized for the session: once the normal amount as inserts and once the normal amount as updates. This occurs for all tables in the subscription. This occurs on a first time synchronization to an empty subscriber database where there should be only inserts taking place.
I have examined the snapshot partition folders for these users on the file system and they appear to be identical in size and content as other subscribers. Checking the last partition snapshot job run and other characteristics for the the subscriber in question, everything appears to be the same as other subscribers functioning normally.
The HOST_ID for us is an employee ID used to filter employee specific data. I have seen this happen if the subscriber changes the value for the HOST_ID used in filtering, after the mobile database has already been populated (2 employees attempt to use the same mobile device and database). But, we have seen this happen recently where the HOST_ID was apparantly never changed midstream.
This just started happening recently. The only modification around the same time frame was the implementation of a custom business logic handler/custom conflict resolver that performs like "Latest Wins" but has logic added to update the a last-update datetime column for selected transaction tables at time of synchronization, so that an SSIS job can detect the changed rows for copying incremental database changes to another application database. This all seems to be working perfectly.
Any ideas?
Thanks,
Matt
View 1 Replies
View Related
Sep 21, 2006
Hi:
Is there a limit on the number of subscribers for Transactional replication?. My question is how many maximum number of subscribers can we have in transactional replication?. How do I retrieve this information throug T-SQL (any DMVs or system tables?). Please let me know.
I am looking to know for both SQL 2005 as well as SQL 2000 and also if anyone can tell me if anything has changed in SQL 2005 for transactional replication that would be great.
Can anyone please let me know?.
Thank you
AK
View 3 Replies
View Related
Jun 7, 2007
Hi All,
We are developing a system which will have to support more than 3000
subscribers. We will have to support both Transactional replication
and Merge replication.
I checked the following document about SQL 2005 replication <http://
www.microsoft.com/technet/prodtechnol/sql/2005/mergrepl.mspx>. The
document does not clearly specify what is the maximum number of
subscribers supported without a significant performance degradation.
The questions i have are:
1. Given the fact that there will be more than 3000 subscribers, there
will be more than 500-1000 subscribers trying to replicate at the same
time. Will be there be a performance degradtion in such a scenario
2. Has anyone used SQL Server 2005 in a scenario involving more than
3000 subscribers?
3. Will it be better if we develop our own system to perform
replication activity instead of relying on SQL Server 2005?
- Ngm
Mail me atnarasimha (DOT) gm (AT) gmail (DOT) com )
View 6 Replies
View Related
Nov 18, 2005
I implemented a merge replication publication on a SQL Server 2005
View 6 Replies
View Related
