SQL Safety Nets

  • cfradenburg (6/9/2011)


    Alan Vogan (6/9/2011)


    It would be nice if ...make some default decisions ...

    I'm not sure what I feel about with this one. We have a few applications with pretty poor maintenance plans delivered by the vendor. If they're willing to be flexible with us putting our own on then I'm fine but I know how prickly some vendors can get.

    Yeah, it's a tricky point. But if the decision points are there, at least during user set-up, the user may stop and think "Do I really know what I'm doing? Maybe I should call IT..." :w00t: Yeah, it's a fantasy, but it could happen. So, in general, I think a few more safety nets would be a good thing. Those of use with experience will know how to get around them if we need to.

  • Starting with SQL Server 2005, a lot of features that could potentially cause security holes are turned OFF by default, and I'd consider that a safety net. We also have the SQL Server Best Practices Analyzer, which scans configuration settings and meta data with a report based on suggested best practices. I don't know if it checks for backup schedules or data model deficiencies, but it theoretically could.

    Instead of scheduling things like backups and index maintenace plans by default, we could perhaps have a feature like the old MS Word Clippy that pops up on the DBA's desktop with and FYI about the state of the server or occasional offereings of sage advice.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Eric M Russell (6/9/2011)


    Instead of scheduling things like backups and index maintenace plans by default, we could perhaps have a feature like the old MS Word Clippy that pops up on the DBA's desktop with and FYI about the state of the server or occasional offereings of sage advice.

    Are you thinking a more paranoid Clippy? Or a grizzled old DBA Vet that's filled with sarcasm?

    I wouldn't mind seeing something like this built into SQL to raise flags if something isn't happening that should. There are a lot of tools out there that already do it but the same companies that don't want to have an experienced DBA are likely the same ones that won't pay for extra tools.

  • Are you thinking a more paranoid Clippy? Or a grizzled old DBA Vet that's filled with sarcasm?

    Yes! We could call the first one Twitchy and the other one Snarky. Or Bucky.

    Some form of interactive feedback during setup is good. I like how Linux setups often have a bajillion options, and among the first is basically 'how much detail do you want?' - that way I can select full detail, find the one checkbox I want to customize and go forward with otherwise simple defaults.

  • markjholmes (6/9/2011)


    Are you thinking a more paranoid Clippy? Or a grizzled old DBA Vet that's filled with sarcasm?

    Yes! We could call the first one Twitchy and the other one Snarky. Or Bucky.

    Some form of interactive feedback during setup is good. I like how Linux setups often have a bajillion options, and among the first is basically 'how much detail do you want?' - that way I can select full detail, find the one checkbox I want to customize and go forward with otherwise simple defaults.

    What would perhaps be useful is a feature that would temporarily suspend an operation and virtually tap you on the shoulder to ask what you think you're doing. For example, when you're about to drop a clustered index on a table with 10,000,000 rows and there are 100 active sessions in the database, or when you run the ad-hoc statement "delete from customer where customer_id = customer_id" from a SSMS query window.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • ORM is becoming more and more common in todays products and development of different solutions. With ORM we lose the power of security throu stored procedures.

    I would like to see SQL server provide more security options which are easy to use together with ORM.

  • IceDread (6/10/2011)


    ORM is becoming more and more common in todays products and development of different solutions. With ORM we lose the power of security throu stored procedures.

    I would like to see SQL server provide more security options which are easy to use together with ORM.

    I think ORM providers and reporting tools need to work with relational databases; not the other way around. They all connect via a login account, and that account doesn't need SYSADMIN or DBO membership (despite what the vendor tells us), just SELECT permission on specific tables and views and also VIEW_SCHEMA permission to enumerate the objects and definitions.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • IceDread (6/10/2011)


    ORM is becoming more and more common in todays products and development of different solutions. With ORM we lose the power of security throu stored procedures.

    I would like to see SQL server provide more security options which are easy to use together with ORM.

    With ORM you lose the power of the database engine and treat it as a datastore. Just use flatfiles and move on. If you're not using stored procedures, security, and the rest of the tools, why did you just drop 100k on licenses per server?

    ORM, in my personal opinion, should be taken around the back of the shed and shot, at least until developers realize at best ORM with very loosely coupled attachements into the data layer via the N-Tier is the most effecient way to go.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • One of the big lessons of the last 10 years for the traditional database community has been the emergence of the NoSQL movement. Whatever you think caused this to happen, an inescapable conclusion is that the major DBMS vendors - Oracle, IBM, Microsoft - totally failed to make their products relevant to the emerging business needs. If they had met the needs, then Google would host most of its data on a traditional RDBMS, not on BigTable.

    We need to continually make SQL Server easier to install and use. The business has a very limited attention span to arguements about the need for data integrity, etc, and can easily interpret these as meaning SQL Server cannot do the job needed. Going back to topic in the OP, I am definitely in favour of more safety nets, but only those that can be turned off for those situations where they are not helpful.

    One of the design goals for FineBuild is to simplify the installation of a secure and resilient SQL Server environment. Part of this is the automatic creation of backup and intelligent index rebuild jobs by FineBuild, and the provision of a switch to tell FineBuild to not do this work.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • Craig Farrell (6/10/2011)


    IceDread (6/10/2011)


    ORM is becoming more and more common in todays products and development of different solutions. With ORM we lose the power of security throu stored procedures.

    I would like to see SQL server provide more security options which are easy to use together with ORM.

    With ORM you lose the power of the database engine and treat it as a datastore. Just use flatfiles and move on. If you're not using stored procedures, security, and the rest of the tools, why did you just drop 100k on licenses per server?

    ORM, in my personal opinion, should be taken around the back of the shed and shot, at least until developers realize at best ORM with very loosely coupled attachements into the data layer via the N-Tier is the most effecient way to go.

    While that was true some years ago it's not the same these days. Many ORM have decent performance and will continue to improve. ORM is becoming more and more common and utilizing ORM is also a good strength when it comes to code management and refactoring and maintenance of a IT product. For several products the ORM performance is more than good enough. Then if something needs improvement, utilizing ORMs does not remove the option to write sql code yourself.

    I do believe however that those who only live with sql and databases might be scared of this coming and unstoppable change but so is our lives in IT, adapt and utilize the tools and new tools we get.

  • Eric M Russell (6/10/2011)


    IceDread (6/10/2011)


    ORM is becoming more and more common in todays products and development of different solutions. With ORM we lose the power of security throu stored procedures.

    I would like to see SQL server provide more security options which are easy to use together with ORM.

    I think ORM providers and reporting tools need to work with relational databases; not the other way around. They all connect via a login account, and that account doesn't need SYSADMIN or DBO membership (despite what the vendor tells us), just SELECT permission on specific tables and views and also VIEW_SCHEMA permission to enumerate the objects and definitions.

    I agree. ORM mappers still have a long way to go to become a really good but they already offers us good things like persistence and concurrency assistance for the applications.

    However, while developing a product, I let ORM create, drop, recreate my entire database from my objects and it's very fast.

    That is also very efficient since in the start of a new product and before a production release, this increases the development speed a lot than having to do this manually on every change. The objects to db mappings also goes a lot faster.

    Security I'm still concerned with thou...

    However, how many people have not seen a lot of .config files in production environments where the database connection information is not encrypted? I've seen a lot of that and it's not something I allow to happen if I have a say but I see it every now and then on different company production servers. So there the security is out the window anyway heh.

  • EdVassie (6/13/2011)


    One of the big lessons of the last 10 years for the traditional database community has been the emergence of the NoSQL movement. Whatever you think caused this to happen, an inescapable conclusion is that the major DBMS vendors - Oracle, IBM, Microsoft - totally failed to make their products relevant to the emerging business needs. If they had met the needs, then Google would host most of its data on a traditional RDBMS, not on BigTable.

    We need to continually make SQL Server easier to install and use. The business has a very limited attention span to arguements about the need for data integrity, etc, and can easily interpret these as meaning SQL Server cannot do the job needed. Going back to topic in the OP, I am definitely in favour of more safety nets, but only those that can be turned off for those situations where they are not helpful.

    One of the design goals for FineBuild is to simplify the installation of a secure and resilient SQL Server environment. Part of this is the automatic creation of backup and intelligent index rebuild jobs by FineBuild, and the provision of a switch to tell FineBuild to not do this work.

    After reading up on NoSQL alternatives and case studies where companies like FaceBook have implemented them, I'm left with the impression that these database engines were chosen over traditional relational databases like MS SQL and Oracle, not because they are easier to use or a better solution than RDMS in general, but because they wanted to implement very large EAV (entity-attribute-value) tables and also forgo ACID features to maximize performance (at the cost of data consistency). In other words, it's not so much that they want solution that isn't built on SQL but rather they need to fill a niche database requirement that isn't limited by relational standards. FaceBook has database requirements that are totally different from Bank Of America, eBay, or the IRS.

    MS SQL and Oracle are first and formost relational database engines. If Microsoft thinks NoSQL datbase solutions are a market worth breaking into, then I'd expect they would branch off and develope a new non-relational database product, and call it something like Microsoft NoSQL. It could perhaps be a service (like they're OLAP product Analysis Services) that can integrate with and augment SQL Server or optionally be run standalone.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Eric M Russell (6/14/2011)


    EdVassie (6/13/2011)


    One of the big lessons of the last 10 years for the traditional database community has been the emergence of the NoSQL movement. Whatever you think caused this to happen, an inescapable conclusion is that the major DBMS vendors - Oracle, IBM, Microsoft - totally failed to make their products relevant to the emerging business needs. If they had met the needs, then Google would host most of its data on a traditional RDBMS, not on BigTable.

    We need to continually make SQL Server easier to install and use. The business has a very limited attention span to arguements about the need for data integrity, etc, and can easily interpret these as meaning SQL Server cannot do the job needed. Going back to topic in the OP, I am definitely in favour of more safety nets, but only those that can be turned off for those situations where they are not helpful.

    One of the design goals for FineBuild is to simplify the installation of a secure and resilient SQL Server environment. Part of this is the automatic creation of backup and intelligent index rebuild jobs by FineBuild, and the provision of a switch to tell FineBuild to not do this work.

    After reading up on NoSQL alternatives and case studies where companies like FaceBook have implemented them, I'm left with the impression that these database engines were chosen over traditional relational databases like MS SQL and Oracle, not because they are easier to use or a better solution than RDMS in general, but because they wanted to implement very large EAV (entity-attribute-value) tables and also forgo ACID features to maximize performance (at the cost of data consistency). In other words, it's not so much that they want solution that isn't built on SQL but rather they need to fill a niche database requirement that isn't limited by relational standards. FaceBook has database requirements that are totally different from Bank Of America, eBay, or the IRS.

    MS SQL and Oracle are first and formost relational database engines. If Microsoft thinks NoSQL datbase solutions are a market worth breaking into, then I'd expect they would branch off and develope a new non-relational database product, and call it something like Microsoft NoSQL. It could perhaps be a service (like they're OLAP product Analysis Services) that can integrate with and augment SQL Server or optionally be run standalone.

    Perhaps but I believe it's also about different functionality available.

    Lets say you want to find your n3 or n4 friends, your friends friends friends to visualize for the user to let him see if he might know some of them.

    Writing that code with nosql is easier than with t-sql.

  • IceDread (6/15/2011)


    Eric M Russell (6/14/2011)


    EdVassie (6/13/2011)


    One of the big lessons of the last 10 years for the traditional database community has been the emergence of the NoSQL movement. Whatever you think caused this to happen, an inescapable conclusion is that the major DBMS vendors - Oracle, IBM, Microsoft - totally failed to make their products relevant to the emerging business needs. If they had met the needs, then Google would host most of its data on a traditional RDBMS, not on BigTable.

    We need to continually make SQL Server easier to install and use. The business has a very limited attention span to arguements about the need for data integrity, etc, and can easily interpret these as meaning SQL Server cannot do the job needed. Going back to topic in the OP, I am definitely in favour of more safety nets, but only those that can be turned off for those situations where they are not helpful.

    One of the design goals for FineBuild is to simplify the installation of a secure and resilient SQL Server environment. Part of this is the automatic creation of backup and intelligent index rebuild jobs by FineBuild, and the provision of a switch to tell FineBuild to not do this work.

    After reading up on NoSQL alternatives and case studies where companies like FaceBook have implemented them, I'm left with the impression that these database engines were chosen over traditional relational databases like MS SQL and Oracle, not because they are easier to use or a better solution than RDMS in general, but because they wanted to implement very large EAV (entity-attribute-value) tables and also forgo ACID features to maximize performance (at the cost of data consistency). In other words, it's not so much that they want solution that isn't built on SQL but rather they need to fill a niche database requirement that isn't limited by relational standards. FaceBook has database requirements that are totally different from Bank Of America, eBay, or the IRS.

    MS SQL and Oracle are first and formost relational database engines. If Microsoft thinks NoSQL datbase solutions are a market worth breaking into, then I'd expect they would branch off and develope a new non-relational database product, and call it something like Microsoft NoSQL. It could perhaps be a service (like they're OLAP product Analysis Services) that can integrate with and augment SQL Server or optionally be run standalone.

    Perhaps but I believe it's also about different functionality available.

    Lets say you want to find your n3 or n4 friends, your friends friends friends to visualize for the user to let him see if he might know some of them.

    Writing that code with nosql is easier than with t-sql.

    There are database applications that heavily utilize attributes contained in EAV tables or hierchal relationships between rows, but that's a niche data processing requirement. That type of application is more common today than 10 years ago, but in most cases it's peripheral to the primary function of the database. A relational database, say for CRM or clinical patient care, may have a need to determine relationships between members, but that functionality could be implmented in a separate NoSQL database the same way that relational databases rely on separate OLAP databases for reporting functionality. Microsoft actually has this service called Enterprise Metadata Management, but it's only intended for one thing: containing hierarchical groupings of metadata or attributes.

    http://msdn.microsoft.com/en-us/library/ee832800.aspx

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Eric M Russell (6/14/2011)


    MS SQL and Oracle are first and formost relational database engines. If Microsoft thinks NoSQL datbase solutions are a market worth breaking into, then I'd expect they would branch off and develope a new non-relational database product, and call it something like Microsoft NoSQL. It could perhaps be a service (like they're OLAP product Analysis Services) that can integrate with and augment SQL Server or optionally be run standalone.

    MS SQL and Oracle are first and foremost SQL database engines, not relational ones. As Ed suggested, the SQL model and its implementations have failed miserably to meet modern business needs (for example due to poor support for declarative data integrity, physical data independence or rich, extensible datatypes). Many CIOs realise this. Most .NET and Java developers certainly realise this because they have to grapple with the SQL dinosaur every day. In fact it's a frequent topic of discussion among those groups.

    Unfortunately the people who seem to notice the failings of SQL least are the ones whose voice ought to speak loudest, namely the database professionals who work with SQL DBMSs. That partly explains why most of the recent innovation in databases has come from the grassroots efforts of people outside the "traditional" data management community and DBMS vendors. I personally believe that the best thing SQL professionals could do for the future of our industry is to stop defending SQL and start talking about what its future replacement should be.

    The lazy assumption that SQL=relational and relational is what people want just doesn't cut it any more. SQL is a 30 year-old non-relational database model that doesn't match modern data management needs. NOSQL represents a movement away from the SQL legacy towards a set of approaches that meet some business needs better than SQL ever did. But NOSQL doesn't have to mean non-relational. The piece of the jigsaw that's missing is a truly relational industrial-strength DBMS that is not based on SQL. That's the NOSQL that ought to be really worth talking about here.

Viewing 15 posts - 16 through 30 (of 36 total)

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