always on read only on 2nd instance server

  • Trying to make things happen in budget. Considering trying to move 2008 acitve/passive cluster with log ship to day old read only 2008 server to 2012 active/passive to 2012 AG Read only server. Only problem is that read only instance may have to be a 2nd instance on a server. The new box is a beast 64 core 256 gig of RAM hp so this is no dog. So I have these choices

    migrate 2008 active/passive cluster to 2012 active passive (this will be it's own ordeal)

    take new monster box and build two instances, one that will run the AG read only database, the other will house reporitng services and analysis services and a few dw databases. We are not heavy into deep dive analysis services yet kind of in it's infancy. Not sure if this other instance will be sql 2008R2 , may be able to do 2012. MY also have a few small sharepoint databases but they barely use it. If I can get the drives configured is this viable or do I drink too much.

    Second choice is to plead for money and get a 2012 AG read only box all for itself. All these boxes are enterprise edition and all are proc licensed.

  • tcronin 95651 (7/18/2014)


    Trying to make things happen in budget. Considering trying to move 2008 acitve/passive cluster with log ship to day old read only 2008 server to 2012 active/passive to 2012 AG Read only server. Only problem is that read only instance may have to be a 2nd instance on a server. The new box is a beast 64 core 256 gig of RAM hp so this is no dog. So I have these choices

    migrate 2008 active/passive cluster to 2012 active passive (this will be it's own ordeal)

    take new monster box and build two instances, one that will run the AG read only database, the other will house reporitng services and analysis services and a few dw databases. We are not heavy into deep dive analysis services yet kind of in it's infancy. Not sure if this other instance will be sql 2008R2 , may be able to do 2012. MY also have a few small sharepoint databases but they barely use it. If I can get the drives configured is this viable or do I drink too much.

    Second choice is to plead for money and get a 2012 AG read only box all for itself. All these boxes are enterprise edition and all are proc licensed.

    Your server isn't a "beast" if you have 20TB of databases and run thousands of complex queries per day. Also you don't mention the IO subsystem. So we are hard pressed to offer up guidance.

    Given sufficient IO and small enough databases/small enough workloads, your notion of everything on one server should be workable. You cannot do Always On to less than SQL 2012, so having the other instance be 2008R2 won't work.

    PLEASE do your homework (and possibly get professional help)!!! AGs are COMPLEX come with a LOT of caveats, provisos, limitations, GOTCHAS, etc. For example, if you set up that read-only copy it will MODIFY YOUR PRIMARY DATABASE to do snapshotting/version store, which will potentially CRUSH your tempdb AND add 14 byte pointer to modified data thus almost certainly FRAGMENTING your data/indexes severely!!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • been a sqk dba for 20 years your response was fairly condescending, I completely realize disk IO is a big factor, current db less than a terabyte and this is not something I am taking lightly or doing this week. Being humble with your knowledge is a much greater virtue than the knowledge itslelf

  • tcronin 95651 (7/18/2014)


    been a sqk dba for 20 years your response was fairly condescending, I completely realize disk IO is a big factor, current db less than a terabyte and this is not something I am taking lightly or doing this week. Being humble with your knowledge is a much greater virtue than the knowledge itslelf

    How exactly am I supposed to know from a forum post that you have 20 years of experience? And I can tell you without question that I have met some very "senior" SQL Server DBAs that didn't know their head from a hole in the ground because they had simply been repeating the same day at work for years without doing anything to try to better their craft.

    My view on answering forum posts is to provide the best possible advice I can for the question(s) asked, completely without regard to the individual doing the asking unless it is obvious from the post that they are super-skilled or very poorly skilled. It is MUCH better to assume that you are just Joe Below Average and proceed accordingly than to assume you know a lot and leave out (critically) important information. My postings have NOTHING to do with being (un)humble with my knowledge. I have it and provide it freely here - it's that simple. But I feel I have a responsibility if you will to keep people from trouble and/or to best help them get out of it if they are already in it.

    I would bet you would have a MUCH greater appreciation for my post if you had seen the same stuff being done over and over and over with AGs at both clients and on forums. The things that you likely know the vast majority of posters here DO NOT, and I have lost track of the number of times that people have slapped together AlwaysOn and had issues.

    I also note that you are asking questions that I would have expected a 20 year SQL DBA to fully set up on the new box and done proper load testing to validate operations, failure operations, etc. I really think you should do that!! :Whistling:

    I wish you the best of luck with your project. It is really interesting stuff to get to play with for real!! 😎

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply