Post reply

database owner best practices

  • January 3, 2008 at 9:25 am

    #764418

    We have been experimenting with VMWare on our QA. I cannot give any good feedbacks on it.

    The performance kinda sucks. (Atleast in our set up)

    -Roy

  • January 3, 2008 at 10:06 am

    #764457

    george sibbald (1/3/2008)

    Jim Russell (1/3/2008)

    What is recommended in the case where multiple independent dbs owned by unrelated individuals/groups/organizations are sharing the same server, and providing local DBA functions themselves, and wish their dbs to remain private and segregated?

    Jim,

    I don't work for a hosting company so I don't say this is an industry recommendation, but if these are totally independent dbs and organisations with their own dbas the only way to get true separation is to give each organisation its own SQL instance.

    Otherwise DBAs will have to be limited to DBO of their own databases and perhaps create db and manage logins rights, if they get sysadmin rights a DBA would normally expect they can intrude on everyone else's databases. In this case you should have one controlling DBA with sysadmin rights to do all the stuff the DBAs working for the companies renting space cannot do.

    SQL 2000 you can have up to 16 instances per server, in SQL 2005 thats gone up, to I think about 50 (can't remember). You will be need a BIG server, loads of memory, possibly 64 bit and lots of drives on lots of spindles for true separation.

    I second that...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 3, 2008 at 1:36 pm

    #764564

    VMWare works well in a Dev\QA environment for us but at this time I would never use it for a Production environment, especially a high volume environment. Also, Microsoft's version (Virtual Server 2005) pales in comparison to VMWare.

  • January 7, 2008 at 7:54 am

    #765345

    Probably a good opportunity for VMWare

    If your IO is quick enough...

  • January 7, 2008 at 7:58 am

    #765347

    Roy Ernest (1/3/2008)

    We have been experimenting with VMWare on our QA. I cannot give any good feedbacks on it.

    The performance kinda sucks. (Atleast in our set up)

    Just out of curiosity what's your setup? We've used VMWare for multitude of things and its been a saving grace for us. Also are you on SAN or using local storage? If on SAN you may want to check with your SAN admin as to how he has the LUNS carved and disk arrays setup, etc.

    =============================================================
    /* Backups are worthless, Restores are priceless */

    Get your learn on at SQL University!
    Follow me on Twitter | Connect on LinkedIn
    My blog: http://sqlchicken.com
    My book: Pro Server 2008 Policy-Based Management

  • January 7, 2008 at 8:09 am

    #765351

    We are on an IPSAN for the QA. Min Disk space 600 Gig for virtual machine. When we do a restore it takes way too much time. Sometimes it does not even complete. It weill throw an error. But if we do a restore on server sharing the same IP SAN, we get it done in 3 Hrs max.

    Our DB is 320 Gig

    -Roy

  • January 7, 2008 at 8:18 am

    #765358

    I have a friend that's moved a lot of servers to VMWare, including some production SQL Servers. They use SAN storage and so far performance has been good. I'm trying to find some time to get over there and provide you some information.

  • January 7, 2008 at 8:23 am

    #765362

    Our QA IPSAN has always been slow. Our production SAN is very very quick. But I dont want to take a risk right now with out testing the VMWare very well in QA.

    BTW Steve, Congrats on becoming MVP for the year 2008:)

    It is party time...:-)

    -Roy

  • February 13, 2008 at 11:25 pm

    #778210

    hi,

    I was wondering if there's a white paper that address the question posted by the OP. In the past, I've always set the database owner to sa, but at this new company, I'm working for, they are thinking it is not secured to have 'sa' as the db owner. I like to change that so only 'sa' will own databases and no some random user.

    When working with sql 2005 in the past, certain processes will fail when the database isn't own by 'sa' or when the trustworthy bit is set to false.

    thanks.

    [edit]

    I found a white paper for sql 2005 best practices:

    Database Ownership and Trust

    A SQL Server instance can contain multiple user databases. Each user database has a specific owner; the owner defaults to the database creator. By definition, members of the sysadmin server role (including system administrators if they have access to SQL Server through their default group account) are database owners (DBOs) in every user database. In addition, there is a database role, db_owner, in every user database. Members of the db_owner role have approximately the same privileges as the dbo user.

    SQL Server can be thought of as running in two distinct modes, which can be referred to as IT department mode and ISV mode. These are not database settings but simply different ways to manage SQL Server. In an IT department, the sysadmin of the instance manages all user databases. In an Internet service provider environment (say, a Web-hosting service), each customer is permitted to manage their own database and is restricted from accessing system databases or other user databases. For example, the databases of two competing companies could be hosted by the same Internet service provider (ISV) and exist in the same SQL Server instance. Dangerous code could be added to a user database when attached to its original instance, and the code would be enabled on the ISV instance when deployed. This situation makes controlling cross-database access crucial.

    If each database is owned and managed by the same general entity, it is still not a good practice to establish a "trust relationship" with a database unless an application-specific feature, such as cross-database Service Broker communication, is required. A trust relationship between databases can be established by allowing cross-database ownership chaining or by marking a database as trusted by the instance by using the TRUSTWORTHY property. An example of setting the TRUSTWORTHY property follows:

    ALTER DATABASE pubs SET TRUSTWORTHY ON

    Best practices for database ownership and trust

    •Have distinct owners for databases; not all databases should be owned by sa.

    •Minimize the number of owners for each database.

    •Confer trust selectively.

    •Leave the Cross-Database Ownership Chaining setting off unless multiple databases are deployed at a single unit.

    •Migrate usage to selective trust instead of using the TRUSTWORTHY property

    So if I'm reading this right. All databases that's in an IT department should be own be 'sa' and if it is hosted, the users can have ownership of it?

    -----------------------------
    www.cbtr.net
    .: SQL Backup Admin Tool[/url] :.

Viewing 9 posts - 16 through 23 (of 23 total)

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