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 ...


5

You could do log shipping with the standby option rather than norecovery. The database would be read-only, and the users would get kicked off during each restore, but it might suit your needs better.


5

Can I take it to a read only, non-restoring state, temporarily, to check the data, and then put it back to restoring, without breaking the whole restore chain? The key to that question is "breaking the restore chain." If you want to query and verify numbers, then standby mode in logshipping is your best option. If you have SQL Server 2012 Enterprise ...


4

The log file on the replica follows exactly the file on the master. They can't be different. You can shrink the master and the replica will automatically change to match.


4

You just need to run two stored procedures to remove the setup of log shipping from the primary database How to: Remove Log Shipping (Transact-SQL). As the secondary doesn't exist you shouldn't need to perform step 2 & 4.


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 ...


4

WITH STANDBY is only supported when both SQL Servers are the same version. You can only use WITH NORECOVERY An alternative if you need read access to the destination databases, is to use replication.


4

The easiest solution to this issue is to set the database to simple recovery, shrink the log, then set it back to full recovery. In T-SQL this would be: ALTER DATABASE [database] SET RECOVERY SIMPLE WITH NO_WAIT DBCC SHRINKFILE([logfilename], 1) ALTER DATABASE [database] SET RECOVERY FULL WITH NO_WAIT Changing the database to simple recovery may cause ...


4

I would suggest configuring the log shipping to STANDBY mode instead of NORECOVERY. This will allow you to query the secondary db for any reporting, etc. When you use NORECOVERY mode, the secondary database will not allow any users to access it, so the database does not have to worry about uncommitted transactions. The log can just be restored as it ...


3

Running DBCC SHRINKFILE without the TRUNCATEONLY option on the primary will propagate the change to the secondary once the log is applied, so it will shrink to the same size..


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

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 ...


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

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 ...


3

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 ...


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

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

Turns out the log shipping configuration has reverted to NORECOVERY instead of STANDBY. I think the is that there must have been an open session on the database, SSMS or alike and after a set period the log shipping is configured to revert back to NORECOVERY. The original configuration was did not have the 'disconnect users currently connected' option ...


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

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 ...


2

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 ...


2

In my opinion, log shipping has those advantages: I understand the direction is only one-way (master to slave). Replication is a complex solution better suited to two-way data replication or replication to multiple sources. Log shipping doesn't need a special design of the database (e.g. primary keys as GUID's and not as autoincrement integers). Log ...


2

Log Shipping essentially involves three jobs. The first job backs up the log on the primary server and stores those backups in a local folder. The second job copies those files across the network to the secondary server. The third jobs restores those backups using the WITH STANDBY option. Log Shipping is set up on an entire database. The database on the ...


2

You should take some time and check out the Database Mirroring Best Practices and Performance Considerations online article. This can save you a lot of time and help you make the right choice



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