Tag Info

Hot answers tagged

7

You could be utilizing the transaction log backups that are taken by the log shipping process. In fact, you can't double-up on transaction log backups. So, as long as you have the actual transaction log backups and the ability to copy them to other storage (in order for your custom backup preservation and retention for Disaster Recovery), you should be ...


5

When you restored the primary the log shipping has broken. The only way to resume it is to re-initiate it, with a new full backup. End of discussion. We do schema and data changes for new Release deployment on to D2 database. And that is your real problem. Do all changes as code changes and deploy by running deployment scripts. When you want to migrate ...


5

I have set up log shipping between 2 SQL Servers (SQL Server 2008 and SQL Server 2012) This is a ONE WAY situation. When you have to failover, it will be easy as the logs from sql server 2008 can be restored to sql server 2012. The stopping stone will be FAIL BACK - if sql server 2008 is Primary and SQL Server 2012 is secondary (standby) server, after ...


4

You can monitor the process on the primary and secondaries, here's where a monitor comes in really useful though... Let's say your log backups and copies are running just fine, no problems, but one of your secondaries goes down. You aren't monitoring the secondary and so are not aware of this. Your log shipping restore alerts are based off of the secondary ...


3

The 180 day evaluation version from Microsoft is Enterprise Edition. AFAIK there is no ability to "trial" Standard edition from Microsoft outside of a licensing agreement you might have. From the point of view of overhead between editions, there's negligible difference. If you want something longer than 180 days, get the developer edition as its feature ...


3

In a word, no. You have to do it live. What you'll want to do it add 3 new files. Then simply start doing index rebuild operations. As long as you are doing rebuilds not defrags SQL will start spreading the data across all the data files. I'm assuming that you are running on SQL Enterprise Edition and can do online index rebuilds.


3

Transactional replication gets you close, but as you said, changing the subscriber schema is a recipe for disaster. Transactional replication uses stored procedures to apply the data changes. You could, with every table change, change those procedures to deal with the underlying changes. That is a lot of additional manual work, but I don't think you will ...


3

As always, it depends on a lot of factors, but I'll answer like this: If the SSIS package takes only the deltas from the last run and performs well, I'd keep using the SSIS, since its something that is already working and I assume you are familiar with. Log shipping and replication will work, but they have drawbacks. For instance, with log shipping you ...


2

No, log shipping is not supported on SQL Express 2008 R2, even as the monitor. The official MSDN page does not state this explicitly, but I found this case where someone tried to use Express and got an error. If you have either a Workgroup or Web edition licence lying around unused, those support what you need, as well as Standard (and higher).


2

RESTORE DATABASE dbname FROM DISK = 'dbname .bak' WITH REPLACE, RECOVERY --force restore or just RESTORE DATABASE dbname WITH RECOVERY the REPLACE Overwrite the existing database, do it only if you are sure you want to override your existing database as you mentioned you dont care to delete it RESTORE WITH RECOVERY is the default behavior which ...


2

I would like to answer my own question - Jungle disk can successfully be used as transport for logs to be delivered to off-site location. I have it working with SQL Server 2008. Before following steps I outlined below I recommend familiarizing yourself with log shipping by first setting it up within the same LAN - I found that wizard works very well for ...


2

My first suggestion would be to use intelligent re-indexing rather than just blindly re-indexing everything. This will help to minimise the impact on the logs un-necessarily. Ola Hallengren's offers one such solution. See http://ola.hallengren.com/ External storage on a SAN that uses either cheap discs (SATA), or dedupe technology, makes it more affordable ...


2

I have my transaction log shipping every 2 days, plus daily backup of the db. My hard disk is 500 GB, but the database size is already reaching 490 GB. I'm afraid it'll run out of diskspace soon. Taking transaction log backups, as are required for log shipping, internally clears portions of the transaction log, allowing the physical space on disk to be ...


2

It depends on your workload. As a baseline, use Performance Monitor and log the network counters when not using Log Shipping or Transactional Replication and measure for 1 hour. Then setup Log Shipping and use Performance Monitor to log the network counters for 1 hour. Then setup Transactional Replication and use Peformance Monitor to log the network ...


2

By "log shipping" I'm guessing you're meaning log backup? Log shipping is a high availability strategy. If your full backups are taking the better part of a day, yes I recommend that you switch things up a bit. But that all depends on a lot of environment specific parameters. For instance, do you have a big window on the weekend where you will be able to ...


2

Two options: On your log shipping primary server, find a user in that database with the permissions you want (or create one). If you create one, log shipping will then transfer that over (since that's a database-level object). Then, to find the SID of the user you want on your primary server: select sid from sys.database_principals dp where type = 'S' and ...


1

So in SQL Server 2012 SP1 I did this: EXEC msdb..sp_helptext 'sp_verify_jobstep'; I scanned for 14234 (the error number you received), and right above the relevant RAISERROR call, I saw this: IF (@server IS NOT NULL) AND (NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE (UPPER(srvname) = UPPER(@server)))) So, run this code with a PRINT: IF ...


1

One approach is to use sp_help_log_shipping_alert_job to find the log shipping alert job ID and then use sp_update_job to suspend the job during maintenance. Enable it back when maintenance is done. I don't know whether the job would not immediately send an alert upon being enabled back though.


1

This normally occurs when the log-shipping is not removed cleanly. To remove the orphan entries you have to delete them from msdb logshipping tables. On Primary : delete from msdb..log_shipping_monitor_secondary where secondary_database = '' -- secondary db name goes here delete from dbo.log_shipping_secondary where primary_database='' -- primary db name ...


1

If your server has only 4GB of RAM, AWE isn't going to help you much. AWE is designed for when you have greater than 4GB of RAM you need to use. Timeouts are harder to define. The timeout period itself is likely defined in your application, so it's not inherently SQL Server that's causing the timeouts. However, what you're running into are queries that ...


1

If the log backup's are able to be restored while the secondary database is in NORECOVERY and only fails when it is in READ-ONLY/STANDBY then I would assume the log backups themselves are ok and not corrupted. It could be that your reporting component has an open connection to the database therefore when restoring the log file it is unable to obtain an ...


1

The question is confusing but I think what you're trying to say is that you want to access the data at City-B, using your existing log-shipping setup. With regards to the log shipping, at City-B, there is an option [RESTORE ... WITH STANDBY] which will leave the database in restoring mode, however in a READONLY state so it's accessible for SELECT queries. ...


1

As Thomas said, transaction log backups cannot be used unless you have a full database backup. But having a long chain of transaction log backups and a full database backup at the beginning of it will cause the restore to a point-in-time process to be very slow. Imagine you have to roll back several hundred of transaction log backups. The best would be to ...


1

The best preventative measure to stop your server running out of disk space is to switch off 'AUTOGROW'. That will prevent your database from grabbing more space, but obviously errors will occur if there is no space left in the file. One thing to note is that even though your files are using 490MB, you may not actually be using all that space. Use ...


1

I have had similar issues to this and every time it was caused by a cloned machine where the SQL components had not been renamed (@@SERVERNAME, Database Mail SMTP, and so on). I would check that possibility first. If the LSAlert job is stopped you should not be receiving mails any more.


1

Pass-through is a feature of Windows authentication. It's not related to SQL Server at all, and it's "on" by default (I'm not sure if there's a way to turn it off, now that I think about it). Which accounts you use depends on the environment, and how the permissions are configured on the backup share. I'm assuming in this scenario that the copy job will be ...


1

Log shipping is just what it says - shipping of the transaction log files. Using a full database backup will not copy the transaction log file from the original server. It creates a new LDF file, the size is the same as the size of the original one, but it's just inflated. So you'll get a LDF file with no data but active portion of the transaction log, which ...


1

If you don't include the transaction log, then it's not really log shipping, is it? Your full backup is restored to that point in time, and that's where it will stay until you either (a) apply a log or (b) apply a differential or another full backup. Yes, the restore does also bring over the transaction log, but only the state of the transaction log at ...


1

If you incure heavy log overhead because of rebuilds I would advise a strategy where you would change to reorganize instead of rebuild. Yes it's true on higher fragmentation reorganize is less efficient then rebuild but the great thing about reorganize is that you can stop it, and next time when yu start it it will continue where it left. So you could ...


1

First point is that in any situation where data is changing rapidly and extended-duration table locks are taken, you're going to have induced latency in updates to those locked tables. Unless you use snapshot isolation that is. Caveat: snapshot isolation may not be appropriate for a reporting server (it increases memory usage and can hit tempdb hard), it ...



Only top voted, non community-wiki answers of a minimum length are eligible