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 |
|
lexiz
Starting Member
17 Posts |
 Posted - 2010-09-06 : 17:20:16
|
| I have had a web site in development for a while now, the database is 2008 and the dedicated server is windows 2008.it is on the verge of going live I am ok with developing sql but I dont feel confident about knowing what to set up /check / monitor etc when the site (and therefore databse) go live.about the only thing I can think needs to be done is to set up a regular backup.I was going to try find a DBA on one of the freelancing sites, but I think it would help I had a better idea of what what sort of things they should be doing to ready the databse for a live environment or are there any specific questions I could ask to find out of they are any good (not so easy as they can look stuff up o the net)any advice appreciated |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-09-07 : 03:03:58
|
| Do you have dedicated servers or are you on a hosted server? What kind of access do you have to it?- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-07 : 08:51:10
|
| In addition to Tara's blog we:Log Sprocs that fail, and duration of Sproc execution - so we can react to slow running SprocsWe log page-render time for pages too and, similarly, react to slow-rendering pages. We also log errors during web page rendering.We log "this should never happen" points in the application, and have an Alerts List that shows those sorts of things.We log 404 and 500.100 errors that are unexpected - they often come from people trying to hack into the system; we add known hacks (like MySQL/Admin) to a list that are ignored in the Alerts list, but we review the others to check that none could actually penetrate our system. Trying to store a 1,000 character script into a 40 character Name field (which then raises an error) is the sort of early warning we are looking out for. |
|
|
|
lexiz
Starting Member
17 Posts |
Posted - 2010-09-10 : 19:05:02
|
| tkizer, thanks that link is really very useful, thanks very much.Lumbago, yeah its dedicated server- from 1&1 - had to take the cheap option, remote connection - if thats what you mean by access.Kristen, this is very intresting, I would love to know more.apart from your 1st point (which i can easiely do). How do you do the rest?, 2. like page render time. is the GUI registering and sending through a start time when it makes a db call, then the db records the time when the response is sent back to the webserver?3. "this should never happen", do you mean errors?, I asume not, byut could you give me an example please.4. We log 404 and 500.100 errors that are unexpected - This also sounds like something I want to do, but isnt this normally in the site logs?, is there an advantage to doing in the db- Not that i even have a clue how to start recording it. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-09-13 : 02:43:25
|
| Depending on the size of the database/website (the amount of traffic) there is a good chance that you can do the dba related stuff yourself. Are you on top of the security part of things? ->- When you have a single dedicated server you should deny remote access to the sql server- Run sql server services with unique local accounts - Connect your website to the database through a trusted connection using integrated security (windows authentication)- You should transfer your backups off-server/site- Do you have a firewall? Allow only port 80/443 connections to anyone, full access to your own IPHere are some links:http://msdn.microsoft.com/en-us/library/ms144228.aspxhttp://learn.iis.net/page.aspx/731/secure-your-sql-server-database/- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-13 : 07:25:13
|
[quote]Originally posted by lexiz
"2. like page render time."We record the time of the Request to the server, the time that we finish sending the Response, and the web page "calls home" to indicate when the Browser gets to Ready state - so we know how long the page takes to render client-side (e.g. accounting for large images / local cache / and pages that never get to that point)"3. "this should never happen", do you mean errors?, I asume not, byut could you give me an example please."IF @blnFlag = 0BEGIN ... do this ...ENDELSE IF @blnFlag = 1BEGIN ... do that ...ENDELSEBEGIN ... record that we got a stupid value and bail-out ...END "4. We log 404 and 500.100 errors that are unexpected - This also sounds like something I want to do, but isnt this normally in the site logs?, is there an advantage to doing in the db- Not that i even have a clue how to start recording it."Depends how easily you can process the web logs, and how easily you can relate that info. back to the actual process that caused it.We handle certain 404 errors ... so, for example, some twit sends out an email with a typo - \iNages\myimage.jpg - so we can put a record in the 404 handler "redirects" tabe to say that should RESPONSE with \iMages\myimage.jpg. (In fact the system would sort this automatically, any JPG, PNG or GIF that 404's, but which is known to the database, will RESPONSE with \IMAGES\filename - so accidents like that are self-correcting).But Clients want mydomain.com/COOL-SALE in their paper-campaign, and again the 404 handler can take care of that (you could do it with URL Rewrite too, but usually that would require Admin to set up, our 404 handler just has a Back Office Maintenance page that a Client User can set up such stuff in.)Users can also see what 404's have occurred. For example if Customers are typing in mydomain.com/COOLSALE, mydomain.com/COOL_SALE, mydomain.com/COOL%20SALE etc. then the Client User can add some 404 handler entries.At a more technical level all such entries are recorded associated with their session ID, so we can review Page render, which Sprocs were called, what the parameters were, how long it took, what 404's (or other errors) occurred. During DEV these are alerted to the Developer in real-time as the page is rendered - so if you have a broken IMG tag you get that alert (because the 404 handler logged it, and the Page Render process checked the Log before shipping the page [in DEV mode], so the Developer knows there is a missing image, even if its a 1px x 1px transparent GIF  whilst working on that page).We are also looking at housekeeping of stale / redundant images this way. Move all / "old" images to "Archive". 404 handler, when asked for a "known to exist" image will move it back from Archive to Images, then after a while anything left is probably not used - or at least is worth humans checking. |
|
|
|
lexiz
Starting Member
17 Posts |
Posted - 2010-09-13 : 22:30:39
|
| Lumbago, "there is a good chance that you can do the dba related stuff yourself" That comment sent sent shivers down my spine,I’m developing a lot of the SQL myself, also the main part of the site a unique Line-Of-Business application written in Silverlight/c#(which i had to learn first so I could write it) oh also coordinating 5 developers, graphic artist and content writer, to finish the remaining work ..... I sure it would be within my abilities but I need the extra help, also there is a possibility that it will be rolled out to 10-15,000 businesses in the London area (I'm doing the marketing to) this would be on top of any other traffic. Its being released in 2 languages then an addition 8-ish later on. So I would prefer having someone that a bit more experienced than me ready to take responsibility.So I am trying to make it as professional, error resilient as possible before going live, I don’t like fire fighting!Those additional items and links I have made a note of, I did have issues with allowing remote connections, which resulted in the test site going offline for 1 day before I figured out how to fix it. I think I a windows Expert who can do/ and check a lot of that stuff for meKristen, so much good information there thanks, I have made note of all of it and am going to start shipping it out the developers todo lists ;-)I had been trying to find good managed hosting companies / or similar that I could look to using after the site going live, and traffic/income increase.So if anyone has recommendations of good companies in the UK that would also be a great help, im currently with 1&1 which I don’t think is a good long term option.Thanks again to all responses, keep posting if you have any additional points I will keep checking the post. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-09-14 : 02:55:36
|
| My apologies...I didn't realize the size of your project but now that I do I totally agree that a dba would be nice. If you can't get one in time make absolutely sure you have a solid backup plan in place. Not having all the other stuff might make your site slower or not work optimally, but backups are crucial. Taras backup script will do it all for you: http://weblogs.sqlteam.com/tarad/archive/2008/05/21/60606.aspxNow in regards to dba's I don't really know of anyone but I do have first hand experience with a dedicated hosting provider that was just plain awesome. It's been almost 4 years since I worked in the UK so this might have changed since then but www.rackspace.co.uk were just fantastic (for the record I have no connection to them whatsoever). They were sort of pricy but support, uptime and everything was simply amazing.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-14 : 03:19:11
|
We have failover virtual hosting with Xtra Host. They have been aggressively pro active in wanting details of issues that we have discussed with them before we were even at the point where we could even put our finger on whether there was a problem, or not! (me: "Server feels a bit sluggish but I need to try to get some decent data togeth ..." them: "What are the queries? what volume of transaction? have you considered A, B, C, ...Z, Epsilon, Omega ..." Me : "OMG ..." We have our eCommerce web sites and SQL databases with them - servers doing 6,000 orders a day (if that's any sort of reference point for your app?!)I can do consultancy for you and/or temporary DBA whilst you get up and running if that's any help (I have done that before with folk who I met here on SQL Team ). I think you could do it yourself too (if you want to), the real risk is not knowing that you haven't done something that you should have done, and perhaps a bit of checking that you are using best practice, or at the least not doing anything in a horrifically inefficient way.Folk here are a cracking help resource too, of course. |
|
|
|
lexiz
Starting Member
17 Posts |
Posted - 2010-09-17 : 07:24:58
|
| I’ve already had some conversations with rackspace and fasthosts. I am always a bit careful when it comes to larger companies, I start to feel like im just one of many. I do prefer dealing with companies that have been recommended. I did look up xtra host, and noticed they have offices in brick lane - which was where I had one of my previous businesses. I will probably start talking to them nearer the time I am ready to consider moving server providers.Kristen, I would be very interested in some consultancy, it wouldn’t be right away, but if you could email me your email address that would be great. Then I could give you a better idea of what I need. I did try emailing you before, but didn’t get a response, so might have been spammed.thanks again all for your help, the information has been invaluable, cheers ;-) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-17 : 09:09:03
|
| We were with some outfit that was bought by Energis, and they were bought by C&W ... and we became far too small a fish in their sea.Found a message from you, sorry I hadn't associated it with this thread and lots of people who use this forum seem to think its OK to mail their queries to regulars here direct, rather than using the forum, so I tend to not look at them too closely if I don't recognise the sender. I'll reply now so you know to be expecting something :) |
|
|
|
|
|
|
|
|
putting a database/server live