April 30, 2016 at 9:10 am
sorry, if posted in wrong forum, but I do know that some of the heavy hitters on this site regularly post here.....and I am after some well seasoned / experienced advice please.
Ok....first and foremost I am NOT a DBA, far from it, just know enough to be dangerous!.
I appreciate that many of you will think that "my friend" is actually me, but I can assure you that is not the case.
....So, after a long conversation with my friend, here is my understanding of his potential "problem".
Single server running :
Windows 2008 OS fully patched
SQL Server 2005 fully patched
SSIS
SSAS
SQL Agent
Linked servers to legacy apps
The db's are not transactional, but being used for the ALL the corporate management reporting from three legacy systems.
All db's are set to "Simple" recovery mode
They run SSIS for ETL process from legacy apps
They use SSAS cubes to present info to Excel users
They have Excel apps that directly query SQL
They have MS Access apps that directly query SQL.
Approx 80 users have access to the various management reports on SQL.....both LAN/WAN
All users are managed thro their active directory groups.
Access to legacy apps by SQL is either thro linked servers or hardcoded Ip address
Excel/Access connections for users to SQL is either thro linked servers or hardcoded Ip address
Now...here is why we had the phone call.
This server is now over 8 years old and has Blue screened three times in the last month.
The solution for them (there are only two IT support, neither with SQL DBA experience) has been to reboot the server and so far this has worked.
The question posed to me was what do they need to consider if they wish to move to a new server (or maybe VM).
I am told they have "back ups" (daily).....but these only appear to be for the reporting db's and they have never attempted a recovery before.
This is far beyond what I have qualified experience of and my initial reaction was "hire a consultant".
His response was "we cant afford one.....surely we can just build a new server and install the back ups????".
I am concerned for him, becasue if that server dies and cannot be recovered, then his arse will be on the line.
Any thoughts, suggestions, pointers will be vey much appreciated (polite or otherwise !)
Many thanks
JLS
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 30, 2016 at 9:36 am
First things first: protect the data. Get him to get backups and to restore them on another server to verify them.
Then ensure that regular backups are in place.
Then think about handling the server. Investigate the reason for the BSOD by checking logs / event viewer etc.
Build a plan of attack for what to do in the event of an unrecoverable problem.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 30, 2016 at 9:42 am
Phil Parkin (4/30/2016)
First things first: protect the data. Get him to get backups and to restore them on another server to verify them.Then ensure that regular backups are in place.
Then think about handling the server. Investigate the reason for the BSOD by checking logs / event viewer etc.
Build a plan of attack for what to do in the event of an unrecoverable problem.
Thanks Phil
problem is that at the mo they have not got a spare server.......so need to build one.
what thoughts on potential issues with a new server.....eg recreating users/logins, linked servers etc.
they are way out of their depth me thinks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 30, 2016 at 11:01 am
most important thing for now is ensuring that backups are done to all db's.
For new server - first of get it setup - make sure the server will allow for the existing software. if not then more recent OS may be required and that may cause odther issues.
then install sql server instances using exactly same names as on the original one - this will most likely save you some hassle once you migrate to new server.
create existing logins
restore db's to it just to ensure that backups are working.
linked servers - set them up and ensure they are working.
now what else..
you need to ensure that all software that may be in use on the server is installed onto the new one. things like oracle drivers, Zip software, custom code (outside sql server) etc.
Permissions - in order to ensure that there is a minimum amount of disruption make sure that both SQL Server and all its roles are using the same windows domain account as the old server.
mapped drives - again map all drives exactly as they are on the current server.
shared drives - you will need to setup all folders, shares and permissions as they are on the old server.
Specific software running on the old server - you may have schedulled tasks running that no one remembers - make sure you dig deep and ensure all is implemented on new server taking due care not to duplicate and enable tasks that move files for example
Going for a new server will most likely mean more memory and more cpu´s - apart from the possible licensing issues you may have, be aware that some queries may perform worst when they have more resources available. Testing is the only way to identify possible issues and in some cases you may be required to rewrite some of it.
May 1, 2016 at 6:53 am
First up, Phil has it right. Get backups of *ALL* the databases on the server and test restore them. It doesn't have to be to a "server," they could have someone set up SQL2005 on their desktop machine and restore there. Make sure the backups are good!
Boiling it down, in order of importance, I'd:
1. Set up backups of all the databases
2. Test those backups on another system (server, desktop, someone's spare laptop from home, doesn't matter)
3. Investigate the cause of the BSODs (could be a fixable hardware issue)
The other thing is, as they're on SQL2005, they're either going to have to scrounge up a SQL2005 Developer Edition, violate licensing and install their existing copy on that 2nd PC, or if someone has an MSDN Subscription they might be able to get a copy from there.
But the point they need to keep in mind is not "fix the server," it's PROTECT THE DATA. And that means making sure not only that it's being backed up, but that it can be restored and used.
May 1, 2016 at 9:15 am
jasona.work (5/1/2016)
First up, Phil has it right. Get backups of *ALL* the databases on the server and test restore them. It doesn't have to be to a "server," they could have someone set up SQL2005 on their desktop machine and restore there. Make sure the backups are good!Boiling it down, in order of importance, I'd:
1. Set up backups of all the databases
2. Test those backups on another system (server, desktop, someone's spare laptop from home, doesn't matter)
3. Investigate the cause of the BSODs (could be a fixable hardware issue)
The other thing is, as they're on SQL2005, they're either going to have to scrounge up a SQL2005 Developer Edition, violate licensing and install their existing copy on that 2nd PC, or if someone has an MSDN Subscription they might be able to get a copy from there.
But the point they need to keep in mind is not "fix the server," it's PROTECT THE DATA. And that means making sure not only that it's being backed up, but that it can be restored and used.
Thanks for your response.
I think that "PROTECT THE DATA" is the least of their problems.......this can easily be reloaded via the ETL scripts in SSIS. This is only a reporting system and is non transactional
Unless a full backup of SQL dbs and system dbs already covers the below requirements what do they need to do for the following......
recreate linked servers
recreate all users and logins
recreate all SSIS packages
recreate all SSAS cubes
recreate all SQL agent jobs
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply