Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mossbs
Starting Member
11 Posts |
 Posted - 2014-12-12 : 06:14:43
|
| Hi guys, I've never worked with a clustered environment until recently so excuse the possible lack of knowledge on this one...Basically we have a 4 DB application and each month we do a data publication that happens offline and then means that 2 of the DBs have to be detached and the new updated DBs are attached during a maintenance window - While this worked in non clustered environments as the process was pretty simple... -Detach DBs from LIVE Server-Attach new DBs-DONE... We're not LIVE yet with the new clustered system yet but from what I can tell by trying to replicate the process is that it seems rather long and drawn out to get new DBs onto the server.. from what I have been doing the process to do the above on new clustered system seems as follows... -Remove databases from Availability group on Secondary Server-Delete databases from Secondary server-Remove databases from Availability group on Primary Server-Detach DBs from Primary Server-Attached DBs to primary Server-Change recovery mode to FULL for both DBs-Perform a full backup of each new DB-Copy the backup into the share so the secondary server can see-Add DBs back into the Availability group on the primary server-Perform FULL synchronisation between servers.-DONEAm I missing a much simpler way of doing this - or is that just how it needs to happen?any help/guidance/direction is greatly appreciated... Cheers! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-12-12 : 13:40:18
|
| Yes that would be the process. You have to apply a tlog in order to start a database in an AG.But I would probably change your whole process. What are you doing to the database between the time it is dropped and when it is attached?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
mossbs
Starting Member
11 Posts |
Posted - 2014-12-15 : 04:43:01
|
| Hi Tara thanks for your reply.What happens is a new DB is prepped offline - basically it has much more data published to it - so nothing really happens to the DB that is dropped - it is just replaced with a new one. There are 2 DBs (out of 4) dropped and attached - these are basically reference DBs that nothing is written to.Cheers! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-12-15 : 12:43:50
|
| Why not do it as an online event?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
mossbs
Starting Member
11 Posts |
Posted - 2014-12-16 : 05:55:14
|
| Hi TaraIt's plainly just due to how log the data takes to publish to the DB - we have monthly data cut-offs where the data team will stop creating the new data - and then this has to be processed from one system into another system which is then attached to a QC system - passed - then a UAT system and if given green light it then goes live.I think the initial idea behind having a cluster was that as this system will be global - the time differences mean that there isn't a maintenance window that will suit all countries to take the system down to publish the new DBs - so clustering (the management was told) would mean that you could take 1 DB down - publish to that one whilst the application talks to the secondary server then when DBs are available on server 1 you can you can switch the app back to look at that one whilst taking the 2nd down and publishing to that - meaning zero down time for the application.I wasn't asked about any of this before hand so have been trying to get to grips with what has been given to me!I think you've answered my initial question though - that is - I am doing the the process the correct way - and I think you are right in your suggestion that basically the whole of our process needs to change to accommodate this - if infact they decide to continue down the SQL Cluster path.Cheers!Please do shout though if I've missed something that I should be seeing! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-12-16 : 12:08:06
|
| I don't think an Availability Group is the right solution given how these databases are published/managed. I would use a traditional SQL Server Failover Cluster instead.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
Attaching DBs to Server/Availbility Group