SQL DBA Questions

Discussion in 'Pre-sales questions' started by Bruckner8, Oct 17, 2009.

  1. Bruckner8

    Bruckner8 Guest

    Hello Ray, Curtis, and other Winhost people!

    My questions come from the DBA side of your SQL Server implementation.

    1) Do you allow triggers?
    2) Do you allow SSIS?
    3) DO you allow jobs?
    4) What's your backup/recovery plan? Are we at liberty to setup our own BU job, and download the resulting BAK file to store in a safe place, away from Winhost?
    5) SQL databases use transaction logs (under full recovery option)...shall I assume this file is included in the "database size" limit? (Some might argue that the MDF is the "database.")
    6) Do you host web sites and SQL Server databases on the same physical servers?

    That's enough for now....I'll ask more after these are answered.
     
    Last edited by a moderator: Oct 14, 2015
  2. FredC

    FredC Winhost Staff

    Yes

    No

    No.

    We backup your database on a daily basis. We do not currently allow users to download the bak file. If you want to backup your database, you'll have to use the SQL database publish wizard.

    No. Transaction log is separated from the database size. We assign up to 1G for transaction log.

    Separate database server.
     
  3. Bruckner8

    Bruckner8 Guest

    OK, so you don't allow jobs. I guess I'll have to use the .NET scheduling feature to call a web page that calls a SQL Server SP. No biggie.

    NO SSIS? Bummer. Hmm, I guess I'll have to rewrite some import processes as .NET services instead. Grrr...might be a deal-breaker. I'll have to ponder for a few days.

    <<We backup your database on a daily basis. We do not currently allow user to download bak file. If you want to backup your database, you'll have to use SQL database publish wizard.>>

    Umm...definite deal breaker. I OWN THE BAK FILES. So I had better be allowed to get them. Think about it this way: If my only BU is the *.sql file that the Publishing Wizard creates, then I'm pulling down a HUGE text file (with all of the extra text for the INSERTS!!!!). I'm sure this will be more than twice the size of that BAK! This doesn't make sense. What would make a lot more sense is for Winhost to post the TRN and BAK files to a folder where the web service can download them as needed.

    OK, if we do it your way, then you take on the complete BU/RESTORE process...is that right? If there is a crash, or if I want to rollback my database to a POINT IN TIME within the last 24 hours, you will provide that? (after all...I can't get to the TRNs or BAKs!)

    Here are my additonal questions:

    7) Exactly what is your BACKUP/RESTORE policy? (be sure to include a description of the process of the BACKUP and the process of the RESTORE)

    8) (I better ask this point-blank): Do you allow POINT-IN-TIME restore?

    9) If I'm stuck with "geewhiz, can't ya just use the DB Publisher to get your data," will I be charged for the bandwidth I'll suck up when pulling down the file every day?

    Thanks for your time.
     
    Last edited by a moderator: Oct 14, 2015
  4. No. I think you would be very hard pressed to find a budget host that provides that. I'm trying to think of one shared host at any price point that provides that service, and honestly, I can't.

    That's more of an enterprise feature, since an individual company can typically afford to maintain massive full and differential database backup storage for its corporate databases. If you imagine how much space and how many resources it would require to do that for thousands of databases you get an idea of why it is not really feasible for a shared host to do it.

    Don't forget that the databases are on separate servers. There would be a resource impact inherent in moving thousands of database backups to the web servers en masse. I could see eventually making that available as an option, but doing it globally by default would be difficult and I dare say, unnecessary.

    Well, we don't charge separately for bandwidth. Will it count against your quota? Yes. But downloading a database backup, even two or three times a day, is not going to have a tremendously negative impact on your 50 gig bandwidth quota when the database limit is 100mb.

    But I suppose potentially, yes, if you wanted to pull down a backup every hour or something, you could see an impact. But then you would run into storage problems of your own maintaining that many backups.
     
    Last edited: Oct 14, 2015
  5. Bruckner8

    Bruckner8 Guest

    You have not answered my 7th question, which was:

    "7) Exactly what is your BACKUP/RESTORE policy? (be sure to include a description of the process of the BACKUP and the process of the RESTORE)"

    I suppose that from your (Winhost's) previous replies I can guess the answer is:

    "We backup once per day, using simple backup/recovery option [or some non-SQL Server BU program]. Therefore, in a crash, you will lose all data between that point-in-time and the most recent backup, since we do not backup transaction logs. The last backup will be restored."

    10) Is that correct? (I'd rather you answer my question 7 specifically)
     
    Last edited by a moderator: Oct 14, 2015
  6. You're answering the questions for me. But you're correct, of course.

    If you need enterprise-level database protection and services you are going to have to pay enterprise-level prices. That shouldn't be a surprise. You will not find what you are looking for in any shared hosting solution.
     
  7. Bruckner8

    Bruckner8 Guest

    Yeah, no sweat. I'm just looking for clear answers, thanks.

    http://www.discountasp.net/sp_sqlhosting_tools.aspx

    not exactly TRN (Point-in-time), but at least the Backups are managed (owned) by the end user. Granted, part of your sales pitch is "we simplify all of that stuff by not even mentioning it to you!", lol, but I'm a dba :)

    I'll ask discountasp.net the same questions...
     
  8. No problem asking questions, we're glad to clarify anything that isn't spelled out.

    Bear in mind that DiscountASP is four times the price of Winhost if you want to use SQL, so you are paying for the API and backup flexibility.
     
    Last edited: Oct 14, 2015
  9. Bruckner8

    Bruckner8 Guest

    Right. I'm just trying to find that sweet-spot!

    This much I know: If Winhost is backing up the SQL databases at all, then there exists a hardware and network impact, no matter what. (doesn't matter if you're using Veritas/SQL Backup/something else). Given that there IS a hardware/network impact, why not just place the backup on the customer's webspace, easily downloaded. That way, you're in between the service you offer now (nothing), and discountASP (fancy in-house API that allows customer to make BAK files and restore them at will).

    At least then I'd be assured that my BAK file is ready every morning, and I can d/l it into my development environment, and do a proper test (DEV>>STAGING) before deploying new features to production.

    But, to your point, maybe I'm projecting enterprise standards on to small-time shared hosting, which isn't fair! Maybe 99% of the data-driven websites out there only use one table!

    Thanks again. I hope you guys succeed; looks pretty good.
     
    Last edited by a moderator: Oct 14, 2015
  10. Well, most databases consist of more than one table of course, but they do share some pretty common characteristics: 1) they are relatively small, and 2) once established, they don't change dramatically or very often. The same can be said about most web sites in general; they are small and do not change much. Of course that applies to 95% of the web sites in the wild, not just those with us.

    So you cater to your hard-core-user audience as much as you can, while bearing in mind that most of the things you're spending time and resources developing will not even be noticed by the majority of your users.

    So you see, we are always trying to find that sweet spot as well. ;)
     
  11. Bruckner8 backup questions are good ones. Being new to web dev and eight years removed from any development whatsoever, I'm certainly no expert on the server/network resource impact of bak vs. the other thing. Just curious, but is there a resource impact comparison by numbers that can be cited that objectively precludes allowing backup to bak?

    on edit: Or is there a security factor in play?
     
  12. Ray

    Ray

    Its really more of a security factor. The SQL servers is setup as a shared hosting database. What that means is within that SQL server you may have multiple databases. Each database can only be accessed by the associated db logins created within them. Guest accounts are disabled so that no one else can access them. To run a backup procedure of your database directly from the SQL server will require you to have specific rights. These rights and privilages will enable you to access other peoples database even if their guest accounts are disabled.
     
  13. Ray,

    I know you guys are just getting started and are controlling the scope of support. That's a wise thing to do. But has Winhost considered at some point down the road hosting client owned servers as long as the server brands/models/configs are selected by Winhost to simplify support?

    Thanks,
    David
     
    Last edited by a moderator: Oct 14, 2015
  14. curtis

    curtis Winhost Staff

    We don't plan to go into colocation services. That business is not something we will do.
     
  15. Bruckner8

    Bruckner8 Guest

    What I've learned so far

    I did lots of research. I had no idea how many hosts there were out there! An infinite array of options to suit any possible need.

    My needs are:

    1) ASP.NET
    2) SQL Server
    3) Use of SQL Server Management Studio
    4) Simple d/l u/l of database files (either BAK or MDF)

    My first shock was the number of hosts that missed on 3). It's amazing the contradictory information one gets from these hosting sites. "Microsoft recommends SSMS for security purposes." Then on another site "Microsoft recommends NOT USING SSMS for security purproses." Maddening.

    However, 4) was the single most important deal-breaker, and will push me to DiscountASP.NET, unless Winhost plans on changing its tune by January 1:

    Any host that requires the use of the Database Publishing Wizard in any capacity is out of the question. This includes Winhost (http://support.Winhost.com/KB/a694/publishing-your-sql-2008-database-to-Winhost-sql-2008-server.aspx) and GoDaddy (http://help.godaddy.com/article/4504#MDF).

    I'm not going to pull-down nor push-up *.sql files loaded with INSERTS to populate tables, especially if it counts against bandwidth numbers (as it does with Winhost). I can't imagine how long this script would take against my Music Publishing eCommerce database!

    I don't care if MS built the DPW, and says it's the best, most-secure thing ever. It's a cumbersome, almost totally useless process for all but the simplest databases. It's a complete joke from any serious developer's standpoint. (Yes, I actually Views, Stored Procedures, Table and Scalar Functions, Triggers, etc...The DPW does NOT create them in the proper dependency order!)

    Winhost can't be beat for the price, if SQL Server database needs are very simple. (And if they're THAT simple, then I don't know why people even bother with SQL Server....MySQL or Access are just as useful!)

    They've certainly targeted their market (KISS), and I don't blame them one bit. But for those of us with a "little" bit more need to control SQL DBs, Winhost won't suffice.

    I'm begging you, Winhost, to rethink your strategy on this. Even if you can just add the feature "BAK available for d/l" by Jan 1 (that's incredibly EASY, DARN-IT), with a promise that you're planning on allowing upload of BAK, or attachment of MDFs in the future, I'll be there!

    Yes, downloading of BAK is that important!! here are a few reasons:

    1) multiple redundancy: With all due respect to your backup process, I'd like to have a copy of my own BAK file every day, thank you very much.

    2) makes it easier for developers to test their scripting in development environment before pushing to Production. This is a common step in any dev cycle: Bring down last night's production backup, restore to staging environment, run the new scripts, test the changes, then run the scripts on production.

    Anyway, it's down to DiscountASP.Net and http://www.arvixe.com/

    Cheers!
     
    Last edited by a moderator: Oct 14, 2015
  16. curtis

    curtis Winhost Staff

    We don't currently have a d/l u/l system for sql. And it is not as simple as you may think it is. Nothing is when you have to think about all the things that can happen when you have many thousands of customers using a tool. If this feature was trivial then we would already have it and every host out there would also have it. But you were only able to narrow down to two hosts with your research.

    We understand that some customers want this feature and it is something we will be looking at for future enhancements.
     

Share This Page