Archive for March, 2013

Business Secrets we learn everyday March 22nd, 2013

Vinod Kumar

My role involves meeting multiple customers and this it is so much fun to learn from so many of you. In this blog post let me pen down some of the awesome learning I have made over the years on what successful businesses and successful people have taught me. This learning can never be enough and I look forward to the same learning experiences in the years ahead too. So hope you will love this post and do let me know what did you learn from this post.

Here are my top 25 things I have learnt from my customers during lunch times:

  1. Businesses are about making people happy, not about just making making money.
  2. Businesses wins hearts first being honest, the business may not come in the short term but eventually come later.
  3. Only way customers will care for you is when you care for them first.
  4. Strategies help you build your runway, execution helps you take off.
  5. Motivating people is easier when you believe in yourself and are self motivated.
  6. You save more when you spend it at the right time.
  7. Just because social media is free doesn’t mean we will get constant flow of results.
  8. You passion must speak in whatever you do. Do it with conviction.
  9. More than competing, cooperating helps grow your business.
  10. You might be quitting when you are nearing success. So keep going till the end.
  11. The owner doesn’t pay your paychecks, it is the customer who pays for you.
  12. Always keep the a room free – the room for making process / product improvements.
  13. Social Networking can help you boost your business but you must create a personality to drive the same.
  14. Know to say Thank You and Apologies without apprehensions. These can be a great way to start a conversation.
  15. Discover the happiness within you if you plan to making customers happy.
  16. Constantly network and open your wings to discipline yourself in this.
  17. Businesses must have the willingness to learn and adapt accordingly.
  18. You might face failures instantly, but success comes gradually. You need to keep chipping away slowly.
  19. Let your product / services speak more than you.
  20. Think big, think globally. Act locally.
  21. Value the little things and give importance to less important people too. There will always be times when you will need them.
  22. Work harder, plan accordingly and once you made up your mind. Never consider the possibility of failure.
  23. Don’t fall for data. Sometimes data doesn’t tell you if you are doing the right things.
  24. Be professional while doing business.
  25. << Let me leave this space to learn from you >>

The loads of lunch table conversations have taught me so much that I found all these to share. I am sure you would have learnt some from your leaders in your respective companies. Do feel free to share with me the same. Would love to learn the same from you.

Continue reading...


Picture Post: Partitioning our time March 14th, 2013

Vinod Kumar

Time is what we want most, but what we use worst – William Penn

It is sometimes interesting to see how we dismiss with the fact that –“We don’t have time”. Time is relative and I keep telling people that every one has the same 24 hours. It is upon us to make sure how we make use of this time. We have different perspective on time utilization. Here is my view of taking your time and slicing it against 4 quadrants. This is my humble shot at how we box our time management.

A great leader’s courage to fulfill his vision comes from passion, not position – John Maxwell

Passion, though a bad regulator, is a powerful spring — Ralph Waldo Emerson

Does this make sense? Where do you think you are spending most of your time. Is it Top left (1), Top right (2), bottom left (3) or in the bottom right (4) quadrant? I have interestingly seen people get to quadrant 4 and in couple of years feel they have actually selected quadrant 2. What are your thoughts? Do drop me a line and would love to hear your perspective.

PS: For a change thought to make the picture really colorful, hope that is ok ;)

Want and boredom are indeed the twin poles of human life.

Continue reading...


TechEd India 2013: SQL Server and Architecture March 13th, 2013

Vinod Kumar


This time of the year that I get heads-down to bring some cool demo for TechEd and the exploration never stops. This year TechEd India 2013 happens in two cities – Bangalore and Pune. This time next week the Bangalore event would have got over and I am sure we will be in exhausted state to relive the past two days and staring at Pune to execute the same. There is always extra care, attention and preparation that goes behind for every TechEd. This year is no different. I am also excited because this will mark my 10th Year of TechEd on stage. The memories of doing this in 2003 is still fresh and the excitement never dies every single year.

The twitter hashtag to follow for this year TechEd is: #TechEdIn.

What is special about TechEd?

If you ask me there are a number of reasons to be at TechEd personally. My drivers for you will be:

  1. It is a place to socialize and at the same time feel alone. If you attend the tech sessions, you might feel there is so much more to learn and this sometimes alienates people in a good way. Get motivated and get to see what the future of technology is.
  2. Two days of pure learning and no work is something we will never get :). I know it is not a nice thing to say but I couldn’t resist from mentioning. Let me reiterate, it is not vacation time but learning time.
  3. It is a hub for networking. This is one of the key and most important reason to attend. Meet peers, speakers from Redmond, local speakers and many more.
  4. With close to 6+ tracks, HOLs – the choice of sessions are too many. It is always fun !!!
  5. It is also a place to make business contacts, find mentors, make new friends and all these will help you to make your future.

SQL Server – Security Session

For the past two years I have talked around Security in SQL Server. Each year I try to bring some unique perspective and what I find useful from a security point of view. This year it will be new additions to Security with SQL Server 2012. You will get to see:

  1. What has changed in Security
  2. How to use User Defined Roles and Default schema
  3. How to keep DBA out of database (access)
  4. Enhancements to Auditing
  5. Audit filtering and Audit resilience
  6. Contained Databases basics
  7. Using Logon Triggers with Contained Databases
  8. SQL Server 2012 – Service account changes
  9. and if time permits few more demo’s are available …

So don’t miss the action. I have infact given away what to expect out of my 1 hour session. Lots of demo and lots of detailing on how to use SQL Server security effectively.

SQL Server – Transaction Logs Session

Going back to the basics is important. So every TechEd there is one topic that I talk about which is about going back to basics. One question that I get back from customers almost every other session is around Transaction Logs functioning. I think it will be great to have a session around that.

  1. What is Transaction Logs? Why do we need them?
  2. Understanding what VLF’s are
  3. Creation of Logs and the basics
  4. Cyclic Logs and how they function
  5. What is written to Logs and how are they written
  6. What happens when Page Split happens with Logs
  7. How does minimal Logging work and what should we know
  8. Finally if time permits, how does recovery work …

This is just a teaser to what is in store for the session. We have many more interesting concepts to discuss and learn during the session – so don’t miss out.

Architecture Track – Evaluating Availability Options

This is going to be an interesting topic. As part of MTC I discuss about scale out to almost every single customer. Most of the times the discussion boils down to when to use what. We have solutions from Log shipping, Mirroring, Clustering, Replication and now with SQL Server 2012 – AlwaysOn. So the session is around:

  1. Why Scale out? Cant I just scale up?
  2. What changes do I need to do in my application?
  3. When to use what technology – Pre SQL 2012
  4. With SQL 2012 – What is AlwaysOn
  5. What are the 12+ different ways to deploy AlwaysOn for my environment?
  6. How do I decide and what will be the RPO and RTO for each?

This is a pure whiteboarding diagram based discussion. You will learn the theory and the possibilities when working with SQL Server 2012.


As I said, if you are in Bangalore or Pune – don’t forget to attend my session. Most importantly don’t forget to pass me a “Hi” after the session. I will be walking around and it will always great to make a new friend.

PS: If you are in Bangalore or Pune – the Usergroups have given some amazing discounts that you don’t want to miss. It is still not late, check out the Bangalore .NET and SQL Usergroup announcements.

Continue reading...


SQL Server: Security Practices and Checklist March 8th, 2013

Vinod Kumar

Now that you did get a chance to see my previous post around Database designs there have been few who have asked more of these posts. In continuation to that concept, this blog post will concentrate on highlighting the SQL Server security practices that I often call out and recommend to customers in our Architecture and design sessions. As always the disclaimer remains the same – these are generic practices / suggestions and might not be the same in your environment and deployment scenarios.

Security is a topic that is always seen as a after thought and I start reiterating to our every single customer how critical and important this is. Most of these are to be done by the database administrator of the environment. It is also left to us to keep track and constantly audit if any of them have been deviated or broken.

Other Practices posts have been:

SQL Server: Database Design Practices

This by no means is not the end-all list of security tips I suggest. But this is a great starting point for you.

General Suggestions

  1. The SQL Server service pack level is current.
  2. Ensure physical security of your server and only authorized people get access to the datacenter.
  3. Make sure SQL is installed with Windows Authentication as much as possible.
  4. Cross database ownership chaining is disabled.
  5. Always block TCP port 1433 and UDP port 1434 on your perimeter firewall. If named instances are listening on additional ports, block those too.
  6. Database Engine Ad Hoc distributed queries is disabled.
  7. Make sure xp_cmdshell is disabled. Don’t use it.
  8. The default trace option is enabled by default. Though non-intrusive, check the need if the same needs to be on production environment.
  9. The DB software installation account is restricted to authorized users only.
  10. Automated tools are used to provide audit trail reports using System Center or any other tool.
  11. SQL Server Agent jobs are monitored in accordance with procedures for failures and success.
  12. Database data files are stored in the same logical storage partition and permissions are proper.
  13. Never install SQL Server on a domain controller.
  14. The ports which the DB uses should be configured appropriately and must not be left to default (1433).
  15. SQL Server registry keys should be properly secured.
  16. Plan for an upgrade/migration plan to address an unsupported DB software version.
  17. Remove sample databases from production servers.

Logins Related

  1. Make sure DB password complexity has not been disabled.
  2. DB account passwords are set to expire every 60 days or more frequently based on your environment.
  3. The sa password has been changed from the default. Disable "sa" and make sure to rename them.
  4. Make sure the ‘remote admin connections’ (also referred to as the Dedicated Administrator Connection (DAC)) option is not disabled.
  5. Dedicated accounts have to be designated for SQL Server Agent proxies.
  6. The Database installation account is separate from the logged in user on the server.
  7. Do review changes to DBA role assignments and the logins who have DBA role access.
  8. Make sure no unnecessary privileges granted to the Windows Service accounts or to Windows Users on the local machine.
  9. The SQL Server service uses a least-privileged local or domain user account.
  10. The SQL Server service account is not assigned excess host system privileges.

Audit Related

  1. Audit trail data is not maintained for one year.
  2. The auditing of logins is configured for failed (or all) logins.
  3. Database executable and configuration files are being monitored for unauthorized modifications.
  4. Transaction logs have to be reviewed for unauthorized modification of classified data.
  5. Audit trail data is reviewed regularly to detect database access by unauthorized applications.
  6. Audit records are restricted only to authorized individuals.
  7. Make sure audit logs are included in backup operations regularly.
  8. Auditing attempts to bypass access controls must be logged.
  9. Trace Rollover should be enabled for audit traces that have a maximum trace file size.

Encryption related

  1. Database passwords used by batch and/or job processes should be in encrypted format.
  2. Sensitive information stored in the database has been identified and protected using encryption.
  3. The Service Master Key is backed up and stored offline and off site.
  4. Make sure the procedures and restrictions for import of production data to development databases are implemented. If obfuscation of data needs to be done while importing, this needs to be adhered to.
  5. Access to Master Key is restricted, controlled and audited.
  6. Symmetric keys should use a master key, certificate, or asymmetric key to encrypt the key.

Permission related

  1. Permissions assigned to PUBLIC or GUEST for a database must be reviewed monthly.
  2. Application user privilege has to be reviewed monthly or more frequently to ensure compliance with least privilege and documented policy of your organization.
  3. Track who has permissions in using the WITH GRANT OPTION.
  4. Never grant permissions to the public database role.
  5. Do not enable the guest account.
  6. Periodically scan fixed server and database roles to ensure that membership is only granted to trusted individuals.
  7. Ensure that the mapping between database users and logins at the server level is correct.
  8. Assign permissions to roles rather than directly to users.
  9. Objects may be owned by roles / schemas, rather than directly by users, if you want to avoid application changes when the owning user is dropped.

Common Security Lapses

  1. DB backup and restoration files are not protected from unauthorized access.
  2. Make sure the Database Master Key passwords are not stored in credentials within the database.
  3. Database backup procedures are defined and implemented properly.
  4. Backup and recover procedures have been implemented/tested.
  5. Check for unapproved inactive or expired database accounts on the database/server.
  6. The DB is periodically tested for vulnerability management and compliance.
  7. Make sure the database is NOT accessible to internet users and is located in a DMZ.
  8. Replication snapshot folders are protected from unauthorized access.
    Linked server providers should not allow ad hoc access.

Miscellaneous Points

  1. Developers are not assigned excess privileges on production databases.
  2. The Named Pipes network protocol is disabled.
  3. The Analysis Services ad hoc data mining queries configuration option has been disabled.
  4. Analysis Services Anonymous Connections are disabled.
  5. Track and audit stored procedures executing at startup.
  6. Error log retention should be set to meet log retention policy. Else increase number of Log files.
  7. Database TRUSTWORTHY status should be authorized and documented or set to off.
  8. The SMO and DMO SPs option should be set to disabled if not required. Try not to use this.
  9. The Agent XPs option should be set to disabled if not required.
  10. Database Engine Ad Hoc distributed queries should be disabled.
  11. Run separate SQL Server services under separate Windows accounts.
  12. Assign static ports to named instances of SQL Server.
  13. Keep an inventory of all versions, editions, and languages of SQL Server for which you are responsible. Include instances of Express Editions in your inventory as it will help in patching.

Do tell me if you found this useful and I am sure there are areas of Development and coding that I will write in future blog posts.

Continue reading...


SQL Server: Database Design Practices March 6th, 2013

Vinod Kumar

Talking with customers and solving their real world problems is something I love doing. This is my day job and I never get tired of this. As we meet customers almost every single day we also have our own feel good factors of suggestions. I am not calling these as best practices explicitly because these are general guidelines and there can be exception to these in your environment for the data set and project that you are working. So read this blog post keeping them in mind. Don’t use them something written on stone but as a mini-checklist used to meet your needs.

This will be Part I of many more parts to come in the future as I gather my thoughts on various suggestions I give customers. Do feel free to drop me a line if you want something covered here.

UPDATE: Part II: SQL Server: Security Practices and Checklist

* These are my perspectives of various DO’s and DONT’s. These can be different and you might have your very own standards. These are *general* tips and can be overridden based on the application under question.

Database Design Tips

This is a high level summary of a number of tips discussed later in this blog.

  1. Do create tables with a primary key.
  2. Do Not create tables without clustered index (typically we will have clustered primary keys).
  3. Do Not enforce foreign key relationships in the database with code. Always use Referential Integrity and foreign key declarations.
  4. Do Not develop a system by writing any code before the database schema is modeled and approved by design team.
  5. Do Not reverse-engineer the database schema after the system is built.
  6. Do have consistent data types between columns that are in a primary/foreign key relationship.
  7. Do make database objects descriptive.

Physical DB Model

A physical data model graphically represents the implementation of a database. It is constrained both by the logical data model it represents and the features of the underlying DB. The process of translating a logical model into a physical model includes the following tasks:

  1. Do create “legal” DB names for entities and attributes.
  2. Do assign appropriate data types and null-ability for each attribute.
  3. Do define primary keys, foreign keys and indexes.
  4. Do define rules and defaults.
  5. Do de-normalize if necessary for performance (typically in a Datamart or data Wharehouse database).

Design for Performance

As mentioned earlier, know what is the type of application we are designing. Is it an OLTP, OLAP or Reporting application. Based on this, designs can vary by miles.

  1. Do define the Primary Key on a single integer column which is typically implemented with an identity property. This keeps the dependent tables with a narrower row width and therefore, more rows per page which results in fewer scans.
  2. Do define the Primary Key as clustered. This keeps the secondary indexes much smaller.
  3. Do create non-clustered indexes on Foreign Key columns.
  4. Do make sure the tables have statistics. Keeping “Auto Update Statistics” and “Auto Create Statistics” options ON in the database is the default. For read only databases, it may be necessary to manually create statistics.
  5. Do select the correct “Recovery Model” for a database.
  6. Do use 100% Fill Factor on read-only or seldom updated tables.
  7. Do Not over-index, make sure you know which indexes will be used.

Naming Conventions

A database consists of a collection of tables that contain data and other objects, such as views, indexes, stored procedures, and triggers, defined to support activities performed with the data. The data stored in a database is usually related to a particular subject or process.

Database objects should be named clearly and unambiguously. The name should provide as much information as possible about the object. A name is used to uniquely identifies the object. Make sure this naming is consistent wherever the object is used throughout the database. Saving keystrokes should never be used as a criterion in selecting a name.

The following rules should be followed for all database object names:

  1. Do Not abbreviate unless absolutely necessary due to length restrictions.  If abbreviations are used, it should remain consistent throughout a project and be documented.
  2. Do Not use special characters when creating database objects.
  3. Do restrict names to alphanumeric characters.
  4. Do make the database objects descriptive.
  5. Do use Pascal Naming by forming names by capitalizing the first letter of each word without embedded spaces (e.g., InvoiceDetail).
Abbreviation Standards
  1. Do avoid using abbreviations.
  2. Do Not use a different letter to start an abbreviation then the first letter of the word being abbreviated (i.e., XFER is not an acceptable abbreviation for Transfer).
  3. Do eliminate vowels first, followed by the least significant consonants of the word.
  4. Double letters can be eliminated unless they are required for clarity.
  5. Do Not use an abbreviation that strongly suggests another word. (Example: the word “parent” could be abbreviated PRNT but this would strongly suggest the word “print.”. A better abbreviation for parent is PARNT.)
  6. Do use consistent abbreviations of similar words. If Charge is CHRG then Change should be CHNG.
  7. Do Not use acronyms if the acronym is not generally known or is subject to multiple interpretations.
  8. Do use abbreviations consistently in the system.
  9. Do Not try to deviate from consistency. I have seen systems mark as _dt for some date field while the datatype is of Varchar. These are great confusions and a recipe for disaster.

I have seen a number of database designs and some of the abbreviations are pathetic and an eye sore. Sometimes when I read them and turn towards the developer – they carry a smile with a look that says: “I didn’t design or name the system :)”.

**** Below are a bunch of naming conventions I have seen. You are free to use them or even ignore them if they don’t fit the bill of your organization / project conventions. ****

Database Names

Database names should use the same convention as described above for database objects: no special characters, alphanumeric characters only and initial capitals for delimiters between words.

  1. Do Not use DB as a suffix for Database name.
  2. Do Not make the database name plural, Example: Use LegalPartner not LegalPartners.
  3. Do use qualifier suffixes for database names like ODS, DataMart, Factory, Staging, Extract and Archive for special type of databases.
  4. Do make the database name mnemonic and/or descriptive.
  5. Do use Pascal Naming by forming names by capitalizing the first letter of each word without embedded spaces or underscores (e.g., LegalPartner).

Table Names

  1. Do Not make the table name plural, Example: Use Organization not Organizations.
  2. Do make the table name mnemonic and/or descriptive.
  3. Do use Pascal Naming by forming names by capitalizing the first letter of each word without embedded spaces or underscores (e.g., BillingDetail).
  4. Do name Associative tables, tables created to resolve a one to many or many to many cardinality, a concatenation of the two referenced table names.
  5. Example: PersonAddress resolves many to many between the Person and Address tables.

Personally I am not a big fan of prefix of tbl to all the tables as it can be easily got from meta data of sys.tables in one shot easily.

The same principle applies to Views. But it is worthwhile to prefix the view based on where it is being used. This can be standard view (vw), Reporting View (rv), ETL view (etl) etc. So feel free to use them appropriately.

Column Names

This can get little complicated and specific to how people design systems. I like a three part name: [Primary][Qualifier][Classifier]. As the names suggests the Primary describes the column a bit more like in EnglishProductName or AlternateProductID. The qualifier can be the descriptive word like in: InventoryInHandQty. The final classifier can give me an idea of what datatype we might be using Qty = Quantity = Integer, ID = Identifier = BigInt, Dt = Data datatype etc.

  1. Do Not use spaces or special characters in column names.
  2. Do use underscores ONLY when making Foreign Key columns that require a Modifier to make the column name more understandable.
  3. Do Not abbreviate the column name.
  4. Do use the table name primary word when creating a primary key.
  5. Do use “ID” as a column name suffix, if it is an INTEGER identifier. If it’s a VARCHAR column it should end with “Code”.

The following attributes can be used where tracking when/who changed a row of data is required.

RowStatus_OptionID – (Optional) Used to mark row as Active/InActive.

Other General recommendation

  1. Do Not use spaces or special characters, underscores are the exception for stored procedures.
  2. Do Not name stored procedure with numbers (e.g. spc_myProc666).
  3. Do spell out the whole column name when defining Indexes.
  4. Do Not number the indexes.
  5. Do use the word “All” for an index that covers all columns in the table.
  6. Do use underscores between the column names help readability.
  7. Do name Primary Keys with PK_ followed by the table name, only.
  8. Do name Foreign Keys with FK_ followed by the child table name, then the column name.
  9. Do Not name Foreign Keys with number suffixes, as in FK_Table_Column01.
  10. Do Not use Double Underscores.
  11. Do name Check Constraints with CK_ followed by the table name, then a colum name.

Final Words

I know this is just a subset of what is available in your development checklist. These are just design list that I have made. The next will be add a security list, development list, deployment list, high-availability practices list etc. I think those will follow in the future.

Continue reading...