none
2012 Always On Functionality vs Transaction Replication

    Question

  • XAction Replication in 2008 and/or Always On in 2012?

    e currently use MS SQL 2008 as the back-end to 3,339 tables.  908 of which do not have PK's.  We would like a solid method for replicating the db.  We looked at Transactional replication, but with 27% of the tables not available for replication due to the missing PK's , this was not an acceptable solution.  Is there something that 2012 will deliver to address this?  Perhaps the "always on" functionality? At the end of the day the goal is to have a real-time "copy" of the entire db for reporting purposes.

    Thanks.

    Brian


    • Edited by Brian Poissant Thursday, July 12, 2012 3:20 PM
    • Moved by Tom Phillips Thursday, July 12, 2012 7:51 PM Probably better answer from Replication forum (From:SQL Server Database Engine)
    Thursday, July 12, 2012 3:19 PM

Answers

All replies

  • One thing to look into with SQL Server 2008 is Mirroring and Snapshot databases.   A Mirror is an exact copy of the real database, maintained on another SQL Server.  The problem is that you cannot read from it.  But you can create Snapshot databases to give you a readable view of the mirrored database. See:

    http://technet.microsoft.com/en-us/library/ms175511

    You can do much the same thing by using log shipping. This has the advantages of more than 1 other server, as mirroring is limited to.  Using the log shipped database still requires database snapshots for reading.  See:

    http://msdn.microsoft.com/en-us/library/ms188698(v=sql.105).aspx

    AlwaysOn with SQL Server 2012 is indeed more capable, basically taking all those technoligies up another level, but it comes at the cost of SQL Server 2012 Enterprise Edition and all the other licensing issues.  For an overview of AlwaysOn see:

    http://msdn.microsoft.com/en-us/library/ff877884.aspx

    So, bottom line is you can do better than replication with 2008, depending on your requirements.

    RLF


    Thursday, July 12, 2012 3:45 PM
  • Brian Poissant,
    Russell Fields is correct, AlwaysOn with SQL Server 2012 comes at the cost of SQL Server 2012 Enterprise Edition. If you are considering transactional replication or any other HA optio, please go through below white papers, I am sure these will help you.

    High Availability with SQL Server 2008 :
    http://msdn.microsoft.com/en-us/library/ee523927.aspx

    SQL Server High Availability Whitepaper by SQL CAT Team - Failure Is Not an Option: Zero Data Loss and High Availability :
    http://blogs.technet.com/b/sqlman/archive/2010/11/12/whitepaper-sql-server-high-availability-whitepaper-by-sql-cat-team-failure-is-not-an-option-zero-data-loss-and-high-availability.aspx

    Using Replication for High Availability and Disaster Recovery (SQL server 2008) :
    http://sqlcat.com/sqlcat/b/whitepapers/archive/2009/09/23/using-replication-for-high-availability-and-disaster-recovery.aspx

    Proven SQL Server Architectures for High Availability and Disaster Recovery (SQL server 2005/2008):
    http://sqlcat.com/sqlcat/b/whitepapers/archive/2010/06/07/proven-sql-server-architectures-for-high-availability-and-disaster-recovery.aspx

    BR, Rishabh Mishra

    Friday, July 13, 2012 9:19 AM
  • Hi,

    As with Brian, we would like to create a replica just for offloading Live reporting, and transactional replication is not possible because of no PK's on all tables.  There seems to be not other solution to have a near real-time reporting copy so was hoping AlwaysOn would allow this.

    Does anyone know whether AlwaysOn requires tables to have PK's?

    Thanks

    Tuesday, July 24, 2012 7:27 PM
  • You should read: http://msdn.microsoft.com/en-us/library/ff877884.aspx

    It says at one point: "The primary replica sends transaction log records of each primary database to every secondary database."  So, no, a Primary Key is not needed technically. 

    (Design Hat:  Almost every table of discreet information should have a PK, even if it is made from multiple columns, even if it is not currently declared as a primary key. When you say, 'No primary key' do you actually mean 'No Single Column Primary Key'?)

    In essence, you can think of it as automated "Log Shipping" to one or more targets.  AlwaysOn also implements readable target databases, whereas 2008 R2 and earlier are unreadable since they are constantly 'recovering'.  

    If you have a secondary database (using mirroring or log shipping) you can create snapshots that provide a stable view of the data.  In theory, a report could do the following:

    1. Create a Snapshot database
    2. Run the report agains the Snapshot database
    3. Drop the Snapshot database

    There are some coding challenges, but just so that you can thing about today's tools.

    AlwaysOn does not need the snapshots, so the use of the secondary is considerably easier.  If you can afford it, go for it.  But remember, Enterprise Edition is required for AlwaysOn.  This is something that you would need to budget.  (Is it worth it?  It is very compelling, if your pockets are deep enough.)

    RLF

    Wednesday, July 25, 2012 12:24 AM
  • Thanks for the reply.

    At the core of our business we have a system which is constantly heavily reported from, affecting application users.  I think the cost of Enterprise could be justifiable (I work in Education so we would get some discount) if it would allow us to split off reporting workload and get very near realtime reporting.

    As I understand, as you say, log shipping is constantly recovering (depending on the schedule) so probably won't be accepted as a solution by users, and I think creating replicas from mirroring is typically done on a long term schedule (such as daily), not minutes which we require.

    So, AlwaysOn sounds like the only real option.

    The application tables definately don't all have PK's - a colleague at another institution which have the same issue with the same application, and reporting, have opted to log ship the data to another server for reporting, though they have the issue of reports sometime have to be manually rerun by users when the database is in log restore state.

    We can't create PK's on the application table as it's a third party product.

    Wednesday, July 25, 2012 8:05 AM
  • Yes, AlwaysOn sounds like a good fit for you.  I definitely think it is a big step forward and is the must have of SQL Server 2012.  (Yes, there are other fine features, but this is the big one.)

    My suggested alternates were just to give you a chance to think about whether you wanted to leverage your existing technology a bit longer.

    All the best,
    RLF

    Wednesday, July 25, 2012 12:20 PM