RSS

Monthly Archives: December 2010

Style and less code with CSS

I started with a simple HTML book in 7th grade and posted my first webpage before high school. It was easy with notepad but rather time consuming. When I landed a decent part time job I used dreamweaver. If you hadn’t used Dreamweaver it basically had a MS Word view and a code view that you could switch back and forth to make web pages. On top of that, there were buttons to quickly perform regular actions such as adding images and making lists.

The problem with this was the ultra-super-mega crap it generated for code. I haven’t used it in a few years so maybe it has gotten better. It was a code generator that didn’t know how to refactor. The HTML was very verbose. I maintained a website with over 4000 documents and found myself on slow days going through pages and trimming the unnecessary code.

About that same time I discovered CSS. Its an easy way to refactor your web pages. Instead of putting the font size, weight and type in every single paragraph definition you define a style once and reuse it. CSS has evolved past the simple text manipulation into layouts and page templates. Tables used to be the best way to divide up you webpage into sections for menus, content and ads. Now with css and applying styles to div tags you can achieve a much slicker look. Also, it will be much more cross browser compliment and look better at different resolutions.

When you get bored with your website you can swap the entire look at feel out and put in a new .css file easily.

 
Leave a comment

Posted by on December 29, 2010 in Uncategorized

 

SQL Recovery models

The default is “Full” which you can view in SSMS by going to database properties->options. Full is the best recovery option, but is it the best option for you? Full has the highest likelihood of causing transaction log issues because every IUD is logged. If you create a new database with the default settings and never back it up, your log file will grow and grow and grow until it fills up the disk.

My theory is “Simple” mode is the best choice for 80% of SQL databases. Simple mode and nightly backups. If you have a group of 20 users and your database fails at 2:10pm in the afternoon some users might be angry that they have to re-do all their work that day so proceed with caution. With simple you won’t have the option of a point in time recovery if your data file is corrupt so you’ll have to go to your nightly full. However, most application users totally accept the fact that they will have to re-do 1 day of work given the circumstances of a server or drive failure. Also, in the case of a DBA oops like dropping or truncating a table you can restore your nightly full to a separate database and do an object level restore. You will have to understand the design of the tables and relationships to pull this off but it is possible.

There is a happy medium recovery model called “Bulk-Logged”. For most transactions it gives you the point-in-time restore capability and for bulk operations it will give you the ability to restore before or after the operation.

So what do you choose??? It depends. If you don’t know all of your users or have transactions you can’t get back, choose full.

 
Leave a comment

Posted by on December 13, 2010 in SQL Admin

 

SQL Database users and SQL Server Logins

There are multiple layers to the security in SQL Server. At the server level the SQL port (default TCP 1433) must be allowed for remote connections in the firewall. From there, the login attempt is made to the database engine. This is where you must have a server level login defined. If not, by default the failed attempt is logged. The database user must be defined and mapped to the login and that user must have the correct object privileges.

If no user is found, this is logged as “failed to open default database” if the initial catalog is defined in the connection string. In windows authentication logins and users are mapped automatically. In SQL Authentication users can become “orphaned” and you will have to remap the login to the user with the sp_change_users_login procedure.
Sometimes when users need access to the data they don’t have a clue what user is actually accessing the data. If the process goes through a web server there are a few options. One of the popular ones is:

1. client logs in with domainuser1 account to their PC
2. vists website that passes domainuser1 to authenticate
3. website application pool runs as domainapp1
4. domainapp1 accesses SQL

Using this method you can easily deny your users direct access to the SQL Server. Or, this way would also enable an easy way for domainuser1 to have a read only SQL account for reporting.
SELECT/UPDATE/etc Privileges can be granted to database users. Below database users you can define schemas and roles and grant privileges to those instead of directly to the user.

So the security hurdles in order are

PC—External Firewall —- Server Firewall—Server Login—Database User—Role—Schema—Object Privilege

In the future I would like to create a Failed Login alert for all of my SQL servers. Also, I would like to log when users get denied access to objects. These would be helpful when debugging connectivity issues.

 
Leave a comment

Posted by on December 7, 2010 in SQL Admin, SQL Dev