Sql 2005 - 2012

anything I don't mind

anything I don't mind

anything I don't mind

Permabanned
Joined
28 Dec 2009
Posts
13,052
Location
london
Just a quick question. I have to do an upgrade and my boss suggests the way to do is to create a cluster and then replicate all the databases across.

Now we have several different database on the old sql server for different applications. Is setting up a cluster realy the best way to do it?

Personally i would have just restored each database individually from backup as i would have thought some applications require databases to be upgraded using specific tools and so on.

Is it common to use clustering and replication to upgrade from 2005 to 2012?

I should add this will be a new 2012 server going from physical to virtual.
 
You're right, he's wrong. You're going to want to test everything as it comes over anyway. So doing it in one shot by shoving it over from another cluster is a terrible idea on every level.

Just export the DBs as .bak files and restore them into the new 2012 environment. Then you can look at turning them into Partially Contained databases for High Availability.

If you're going to be running virtualised clustered SQL I'd be careful about the underlying storage. On the cheaper end of the scale we tend to recommend CSVFS which gives good performance in large single file situations - it is not suitable or a replacement for normal DFSr. If you've got a bit more money just sling it on dedicated shared storage, job done.
 
Thanks for response.

Backup and restore method and running the upgrade advisor on each db was what i was going to do. We only have one instance. I know it is difficult to discuss if you don't know the exact details of load and size etc. So if i may.

It is a small site of 100 users. The main db is DMS which is 20gb and the next size db is 1.5gb with most of the dbs being very small and insignificant in load, stuff like BES and citrix xenapp db.

All we have at the moment is netapp 2240 which is setup with NFS to 3 hp gen 8 hosts. I have not looked in to storage yet for sql 2012 as just starting the project but I was probably just going to stick the drives on the main OS datastore as that would be the fastest storage.

The netapp is setup as raid dp with two active controllers giving 5tb across both controllers, with 1tb exchange and 1tb document store and only 20 virtual machines or guests. There is plently of resources on the hosts for the sql server.

The questions i have, would we benefit from putting the DMS database on its own instance or can we do the same and put all 6 databases on the same instance. Is this storage going to run in to any issues based on the information you have above?

There is no real scope for additional storage so we stuck with what we have, i could make another volume but may be pointless in terms of performance. I was going to create one disk for Os, one for data, one for logs, but on the same datastore with other OS disks, not all 20 mind and the exchange dbs are on their own volumes as well on different controllers, i tried to share the load.

Without going in to, it should be set up in ISCSI for exchange, do you think i have any issues with our config?

I had no plans to add additional sql servers and the current one is a physical gen 5 from 2008, so we may see big increase in performance.
 
anything I don't mind said:
Is this storage going to run in to any issues based on the information you have above?
Click to expand...
The size is fine but that says nothing about how transactional the databases are. Moving them to a host with more RAM will give you a big boost over 2005 without any effort, after that play it by ear I guess.

anything I don't mind said:
The questions i have, would we benefit from putting the DMS database on its own instance or can we do the same and put all 6 databases on the same instance.
Click to expand...
I'm not an SQL admin nor do I have any formal training in 2012 yet. The main times I've seen different SQL instances used is when large systems are loaded on to the same cluster, eg, Sharepoint, CRM systems and assorted DBs in the same cluster but on different instances. In HA environments this makes sense because you don't want to lose all your DBs at once if you have one configuration issue.

anything I don't mind said:
I was going to create one disk for Os, one for data, one for logs, but on the same datastore with other OS disks, not all 20 mind and the exchange dbs are on their own volumes as well on different controllers, i tried to share the load.
Click to expand...
I'd put the OS and Logs on one store and the Data on a separate one. If you lose either store you can recover relatively easily versus having everyone on one store.

anything I don't mind said:
Without going in to, it should be set up in ISCSI for exchange, do you think i have any issues with our config?
Click to expand...
Nothing obvious but I'm only going on what you've told me. Don't forget your heartbeat network if you're using a cluster!
 
something to be aware of, and after working with SQL databases from 2000 to 2012 - are you going to use point in time recovery if a database goes **** up or restore from a full backup - if not going to use point in time restore, then set the recovery model to simple rather than full, or else your log file will grow very rapidly depending on number of transactions
 
I have started this installation today, I have windows server 2012 r2 and sql 2012 sp1.

The questions i have is around the service accounts. On the old 2005 server the sql database service is a domain account. Is it ok to just create a new domain account for the new server because that service account is over used and has domain admin rights on it, which i don't think is required for an sql service account. Or would you recommend keeping all the service accounts with the same name as the 2005 server to prevent any problems with database migration?

I am keeping the agent service with the same account because the main database needs that account to run some complicated scripts and processes.

I should add it is my first SQL installation, outside of express dbs.

I have granted logon as a service to the new account and the old agent account on new server.
 
Last edited:
I am using standard. I was planning on putting sp2 on there, using the sp1 iso so far. Not sure on application compatibility with sp2 yet, still investigating.

I have been looking for guides but not find many decent ones for a side by side upgrade, do you have any with detailed steps?

Basically what i have learned so far is to use the sql data tools move logins task. I made the mistake of installing the data tools on the sql server and ended up deleting the entire vm and starting again because i didnt want two instances on there as some people reported problems, i tried uninstalling it but it was such a mess at that point i thought would be easier to start again as only takes 30 mins.

After moving the logins, its pretty much ready to start restoring databases to the new sql server, right?

I found this guide, http://www.slideshare.net/talktosavjani/sql-2012-upgradeguide

Has some detailed steps, it says i need to run check dbcc with special options on each database after moving it across.
 
Look at using a Managed Service Account for your SQL service account. They are fully supported on 2012. It is much more secure than using a Domain Admin account, especially across multiple hosts.

Run DBCC CHECKDB WITH NO_INFOMSGS; after each database is migrated to check for errors first. This will merely report - it won't change anything.

If you need to run DBCC CHECKDB with any of the repair options, wrap the command with BEGIN TRANSACTION And GO;. You can then commit or rollback any changes made if they are undesirable.
 
I can do most things without stressing but for some reason sql makes me stress out. I realy dislike working with it for some reason. Out of everything Vmware, storage, windows, exchange and so on i dislike dealing with sql the most. It just seems so fragile and so easy to break. Nothing is straight forward and everything has a gotcha that is not documented.

I have managed to migrate the logins and jobs now. I think it was more the uncertainty about things. I have migrated the xenapp db and all ok, so i think i was just over reacting to it.
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom