21

Straight backup and restore is obviously out. I also wouldn't consider replication of any kind. Database mirroring is relatively simple to set up, but requires real-time connectivity between the two servers, setting up of partners and endpoints, etc. Availability Groups could be an option, but on top of the networking complications you also have to have ...


17

I recently migrated 15tb across 6 databases using mirroring. Very simple and worked perfectly with just a couple seconds of failover time. Edits: I had two new virtualized SQL Servers. The databases were coming from 3 servers that they had just plain outgrown, and were impacting performance on the smaller databases hosted on them. The process was very ...


14

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


11

If you DROP the tables, it'll cause minimal logs to be generated because it doesn't immediately force garbage collection to occur on the used space, it merely basically just removes the reference. You similarly can accomplish the same outcome with TRUNCATE (though I think on large tables there could be more overhead than DROP, I'll have to research this and ...


10

You are facing what is called orphaned entries in logshipping tables in msdb. You can use below tsql to just manually clean up logshipping orphan entries. For your case, since you don't have the primary database available, you should just run the secondary part or you can try running sp_delete_log_shipping_secondary_database N'secondary_database'. It will ...


10

It just seems so fragile. Logshipping is tested and proved since sql server 2000 (and even older) days. Its not fragile. Look at the errors ... Last Restored File: \server\folder\db_20160105060002.trn, Logshipping is trying to restore Destination: '\server\folder\db_20160105080001.trn' This means you have a gap in the log sequence. There might ...


9

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.


9

There is no point in HA if anyhow DR technologies are allowed in simple recovery. Transaction log is a must for point-in-time recovery. The transaction log is what a DR technology uses to replay transactions that are done on Logshipped/mirrored databases, and since a log backup is not possible in simple recovery, you cannot configure HA/DR in simple recovery ...


9

If I understand you correctly, your major problem is the log files during the several hours of backup. From your opening statement I understand that the 1.5 TB database normally runs in SIMPLE recovery, and thus no log backups to do. Disclaimer: I have never done log shipping on this scale. Of course, you should ask whether you can get more space allocated ...


9

The recovery model for the secondary will be the same as the primary database, because you're backing up the primary, restoring it on the secondary node using NORECOVERY/STANDBY, and then restoring subsequent log files to it. In a log shipping configuration, you cannot make changes to the secondary database. If you could, then the databases would be out of ...


9

From this page in the MS documentation: There are two options for configuration when you place the secondary database in standby mode: You can choose to have database users disconnected when transaction log backups are being restored. If you choose this option, users will be disconnected from the database each time the log shipping restore ...


8

Yes, but only manually - Azure Blob Storage isn't integrated into the log shipping wizards. You would need to do your own backups to Azure Blob Storage, and then have the secondary server(s) restore from ABS using the same certificates. When other shops have done similar projects with AWS's S3 storage (writing their SQL Server backups there using 3rd party ...


8

It's because these methods rely on the transaction log, which itself is only reliable in the full recovery model. Please read the following page in its entirety, as there are several hints there about the differences between simple and full recovery, and why full is so important for DR: Why Does the Transaction Log Keep Growing or Run Out of Space? If you ...


8

There are a few possible things going on here, here's a non-exhaustive list: the execution plan cache is cleared by the log restore, so plans will need to be recompiled the first time. If your plans have long compile times, this could explain the difference. You didn't mention exactly how long the delay is on the first run compared to the subsequent runs ...


7

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


7

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


7

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


7

Do not disable TDE. Besides being a lengthy size-of-data operation, it was established initially for a reason and you may be breaking whatever compliance/operations reason exists for TDE. Rather set up log shipping properly in the presence of TDE. Follow the steps described in Move a TDE Protected Database to Another SQL Server. You are going to export the ...


7

SQL Server always seems to set snapshot isolation (SI) on when a database is made read only. I have no idea why it does this, but it does. You can try to turn it off, and the ALTER DATABASE statement will succeed, but snapshot isolation remains enabled while the database is read only. One theory is that setting SI prevents unexpected errors for people ...


7

As the error message mentions, you can't use WITH STANDBY when restoring to a newer version of SQL Server. The database upgrade process has to run in that case, and it can't do that in standby mode because the database is essentially read-only. This is documented in RESTORE Statements - Arguments (Transact-SQL): STANDBY is also not allowed when a ...


6

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.


6

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


6

Suppose I run the following query on a database with a recovery model of full: INSERT INTO dbo.MY_FAVORITE_MSFT_EMPLOYEES WITH (TABLOCK) SELECT 'SEAN GALLARDY' UNION ALL SELECT 'JOE SACK'; Under the full recovery model, SQL Server will write some representation of the data that was inserted to the transaction log. This allows redo threads on the secondary ...


6

Yes you can, I remember a shop where we had this kind of setup in that case even the copy job was disabled because there were no need to copy the files. You just need to be sure your file retention is ok and the Share is available at all the times. You don't want your share folder go offline during a long weekend or something and your transaction log on ...


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


5

Here are a few ways: Compare pg_current_xlog_location() on the primary with pg_last_xlog_replay_location() on the standby. That will give you the lag in bytes, which might not be very useful for alerting, but it can be useful to chart it. Monitor pg_last_xact_replay_timestamp() on the standby against the current time. Have a cron job on the primary ...


5

This is kind of how log shipping has to work. You need exclusive access to a database to apply a log backup, which means your script has to set the database to single_user or otherwise evict all the users before it can apply the log backup(s). Some workarounds: Schedule your restores for outside of business hours, and let them continue querying stale data ...


5

Well it didn't fixed. I think I will recreate the log shipping again Before , you give a try with that, why don't you go for looking the most recent differential backup and restore it on the secondary. We also had situations as mentioned above and found that: This happened due to NW glitch due to which the folder shared(on primary as common backup ...


5

If you were referring to Database Mirroring when you say mirroring (as opposed to SAN mirroring), then don't. It has been deprecated since 2012 now so it is a potential candidate for complete removal in the future releases. Go with Always On Availability Groups. The difference in DBA overhead should be mostly the same. What is different, IMHO, is the ...


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