Database Structure

Question

Hi,

I need some help if possible please with regards to my databases within Management Studio.

I have 2 servers, each with only one database.
Server 1 - Database - Comps
Server 2 - Database - Apps

What I have is a copy of each database (CompsCopy & AppsCopy) on Server 3. However, as it's a copy, the data is only up to date at the time of being copied over. Ideally, I need these copied databases to link to the live database so it updates a fews times a day.

What we can't do, is use the live database due to the company infrastructure hence taking a copy each time and placing them into server 3.

Does anyone know if this can be possible and how to do it, please?

Answer 1

What we can't do, is use the live database due to the company infrastructure hence taking a copy each time and placing them into server 3.

This suggests that there are some restrictions, but since we don't know exactly which they are, it's difficult to say whether they put obstacles to the various options there are. But I can make a guess.

Some common options for what you are asking are:

• Log shipping. The database is standby mode, and log backups are regularly applied to the database. But since restoring a database backup was not an option, log shipping may be out of scope to you. However, this is probably the simplest solution to set up and maintain.
• Transaction replication. Several posters have suggest this. This may work, but it is fairly complex to set up and keep running.
• Availbility group with a readonly replica. Several posters have suggested this, but I can't see that this could be an option for you.
• To this comes some custom sync that you write yourself, which is likely to be over the top.

Answer 2

Hi @Bone_12 ,

Replication is a good option for you, you need to administer and monitor it and a poor setup could make performance problems worse, if you want to use it, please learn about the best options for performance and the bset setup for performance.
Best Practices for Replication Administration

AlwaysOn Availability Groups with the concept of an Active Secondary for reading purpose, but there are some issues, you maybe need in Enterprise edition, in Standard edition will have limitations

Creating a read only replica of SQL Server database for reports
Best Solution to have a Live copy of a Database when replication is not an Option

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I am using SQL Server Management Studio version 15.0.18330.0. All servers are using the same version of Management Studio.

That's just the management tool, the version doesn't matter.
The version of the database engines is important, you can query them with

select @@VERSION

Answer 4

It depends on your needs. Do you need read-write access to the copy? What version of SQL Server are you using? Are all servers using the same version.

You could use AlwaysOn or Replication to accomplish this:

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver15

https://learn.microsoft.com/en-us/sql/relational-databases/replication/types-of-replication?view=sql-server-ver15