Thursday, 9 August 2018

MS-SQL Auto Increment Identity Column Value Jump 1000

Long time ago I noticed a bug in one of my Databases, which I discovered later it was in relation with one of Microsoft's features :) :) :). That bug/feature was not stable and occurs in lower rates "lower than 1% of the time". In my case it happened three times along 8 months. Recently, I caught it again on one of my servers.

The bug is all about the Identity field of any table. Where ID sequence misses its order in an unexpected way. So, instead of counting (1, 2, 3, 4 .. etc) it counts (1, 2, 1001, 1002, 1003, .. etc) or (1, 2, 10001, 10002, 10003, .. etc). Sometimes, SQL jumps the sequence of normal counting in the ID field by 1000 or 10000 steps at once.

That makes too much trouble for my Database. Every time occurs, I manually fix the difference and returns sequence to its normality. But, today, and after some investigation I found the reason and the solution.

The Reason: 
Unexpected SQL-Server restarting causes the cache-technique done for the identity to be mis-confused and then SQL rests it to some new steps ahead, in case of "int" it steps 1000 ahead and in case of "bigint" it steps 10000 ahead.

The solution:
I found four solutions. You can select one of them according to your case:
1- Use [SEQUENCE] object: click here
Which means too much change in your application-code and architecture of the data-provider-layer if you have any.
2- Change server trace flag [-t272]: click here
It is a server wide option that is used to disable any cache of any identity-field in any table of any database in the server. That means other databases may be affected if you have multiple.
3- Use [IDENTITY_CAHCE]: click here
It is Only applied for "SQL-2017 (14.x)", and it is used to disable caching technique for specific databases only.
4- Use [DBCC CHECKIDENT]: click here
Create a stored procedure in master database and add it to the starting chain using [sp_procoption], this master wide [STORED PROCEDURE] should be used to reset [IDENTITY] manually for specific tables in specific database, so, every time SQL-Server is restarted it starts and reset Identity using [DBCC CHECKIDENT].

Monday, 19 March 2018

Teredo, IPV6-tunneling over IPV4-traffic

What Is IPV4?
IPv4 is defined and specified in IETF publication RCF 791. It is used in the packet-switched link layer in the OSI model. IPv4 uses 32-bit addresses for Ethernet communication in five classes, named A, B, C, D and E. Classes A, B and C have a different bit length for addressing the network host. Class D addresses are reserved for multicasting, while class E addresses are reserved for future use. This addressing scheme denotes how many hosts (computers) we can have directly connected to the internet. Class A addresses to cannot be used and is reserved for loopback and diagnostic functions.

Network/Host ID
Subnet Mask
Hosts per Network
126 (27 – 2)
16,777,214 (224 – 2)
16,382 (214 – 2)
65,534 (216 – 2)
2,097,150 (221 – 2)
254 (28 – 2)
Reserved for Multicasting
Experimental; used for research

The TCP/IP Version 4 packet is composed of a number of different fields that can be used by the source and intermediary devices to determine the way a specific packet is treated when being transported. 

What Is NAT?
Image resultNetwork Address Translation (NAT) is a method of remapping one IP address space into another by modifying network address information in Internet Protocol (IP) datagram packet headers while they are in transit across a traffic routing device. (NAT) is the process where a network device, usually a firewall, assigns a public address to a computer (or group of computers) inside a private network. The main use of NAT is to limit the number of public IP addresses an organization or company must use, for both economy and security purposes. NAT falls into two categories; Static NAT “i.e. Restricted Cone NAT” and Dynamic NAT “i.e. Port Restricted Cone NAT, Symmetric NAT”. Static NAT is where administrators manually create and maintain the NAT mappings. Dynamic NAT is where the router creates and maintains mappings automatically on demand.

What is Network Interface?
network interface is the point of interconnection between a computer and a private or public network. A network interface is generally a network interface card (NIC), but does not have to have a physical form. Instead, the network interface can be implemented in software.

What Is IPV6?
IPv6 is the Internet's next-generation protocol, designed to replace the current Internet Protocol “i.e. IP-Version-4”. IPv6 allows more users and devices to communicate on the Internet by using bigger numbers to create IP addresses. Under IPv4, every IP address is 32 bits long, which allows 4.3 billion unique addresses. Under IPV6, every IP is 128 bits long, which allows 4.3x1038 unique addresses. It is not a new technology it is here since 90th and is a prominent aspect of the Internet-Of-Things era, believe or not it can provide a 4.3x1028 individual IP-addresses for every human of the 7Billion population on the earth. IPv6 is the future of the Internet Protocol, there is no doubt about that. With only a small handful of people running it today however, it will take a long time until we can effectively 'turn off' IPv4 on our networks. Some people think this may be as long as the year 2025.

To test if your computer “the host” can access IPV6 websites “the web server” you can ping IPV6-address of google “i.e. ping -6”, such like that:

You can trace its route also using “tracert -6”, such like that:

IPv6 on IPv4 Networks can be routed on the current IPv4 Networks, but special things must be done in order for the IPv4 addressing to work. There are 4 main methodologies to executing this task, which then have specific implementations. The methods are dual-stack, tunneling, translation, and gateway. They each have pros and cons, and each are in use. Dual stack works by using both protocols on each router and computer on the network that does the actual routing and delivery of the traffic. Tunneling encapsulates IPv6 packets, in whole, into IPv4 packets and adding some information to the IPv4 header and sending it. The reciever sees the special info and strips the IPv6 packet out and uses it. Translation is used when an IPv4 only node on the network tries to communicate with an IPv6 only node. There are a few protocols that handle this translation, such as NAT-PT and Bump-in-the-API. Gateway methods are fairly popular (and many times free!) and are easy to use. A gateway computer is set up on some network, which can be your Internet service provider (if you are lucky) or a 3rd-party company such as 6gate or SixXs (links open in new window). See this simple representation of how this works. This picture shows a client computer running IPv6 connecting to The Internet through a gateway that allows IPv4 sites to be reached that normally would not be. This also works in the other direction, IPv4 clients could connect to IPv6 servers and services through the gateway. Also note that not all computers on The Internet run IPv4, and some do indeed run IPv6 and utilize translational gateways such as this. Another important issue with gateways is that they tend to be application specific. That means that most likely the gateway only handle one or a handful of protocols, such as http. This can be a problem if the client wants to use IPv6 only and connect to a service that uses IPv4 and the gateway doesn't support it. At this point the client must use some other means of access.

What Is Teredo? is an IPv6 over IPv4 tunneling mechanism. Its important distinguishing feature when compared to other tunneling methods is that it works even if there are NATs in the IPv4 path. This Microsoft developped tunnelling protocol is defined by RFC4380. Windows has a built in Teredo implementation. There are also implementations of it for at least Linux, FreeBSD and Mac OS X. These include Miredo and nici-teredo. Teredo uses the IPv6 prefix 2001:0::/32 to represent the IPv4 identity of the host. Teredo clients (hosts) connect to a Teredo server when they start up. After that connection is established the client and the server negotiate the address for the client. Public Teredo servers establishes a trusted connection with hosts transmitting bubble-packets. On May 4th, 2015, Microsoft began deactivating its publicly available Teredo servers that were configured as the default servers for Windows Vista and Windows 7 clients, “” not work (as per October 2015). Teredo works well using a third party Teredo Server, Ones you picked up the one you want, you may add it as being your teredo server to use. Third party Teredo servers are open to anyone. To configure a Teredo client, use one of the following server addresses:

Use netsh command line
Netsh is a command-line scripting utility that allows you to, either locally or remotely, display or modify the network configuration of a computer that is currently running. Netsh also provides a scripting feature that allows you to run a group of commands in batch mode against a specified computer. You can use the following command line to set a Teredo server “netsh int ipv6 set teredo client”.

  • To change Teredo state: netsh interface Teredo set state type=default
  • To Show Teredo  state: netsh interface Teredo show state
  • To disable Teredo: netsh interface teredo set state disable
  • To set a serever: netsh interface teredo set state servername=terdoServerName

Use OpenDNS!
Domain Name Servers (DNS) are the Internet's equivalent of a phone book. They maintain a directory of domain names and translate them to Internet Protocol (IP) addresses. This is necessary because, although domain names are easy for people to remember, computers or machines, access websites based on IP addresses. OpenDNS now supports IPv6 addresses — meaning that, by using the OpenDNS Sandbox, you’ll be able to resolve your DNS using IPv6 DNS servers. IPv6 supports a far larger number of addresses than IPv4, which is why the change is taking place now — since IPv4 was implemented in 1981, the Internet has grown dramatically, and there are no more available IPv4 addresses. The last block of IPv4 addresses have been allocated, and it’s time to get your network ready.
Teredo Port Security!
Windows Firewall with Advanced Security includes a stateful firewall that allows you to determine which network traffic is permitted to pass between your computer and the network. It also includes connection security rules that use Internet Protocol security (IPsec) to protect traffic as it travels across the network. Create a rule that applies to inbound connections, go to the Inbound Rules section. Create a rule that applies to outbound connections, go to the Outbound Rules section. Then, you can use the Actions panel on the right side of the window and click or tap New Rule. Select Port and enable Teredo to map your IPV6 packets through 62675 port.

Solve Windows Home Edition Problems!
  • You will need to manually create a registry key to enable IPV6 transitioning: HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\TCPIP\v6Transition With a value named “Teredo_DefaultQualified” and equals “Enabled”.
  • You will need to manually enable Teredo by modifying the registry value “DisabledComponents” to become “0x0” instead of “0x8e” at the registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip6\Parameters

Test your IPv6 connectivity

Visit to determine reachability of IPV6 servers. This test is entirely a client-side JavaScript with a series of ajax requests that are made from the web server, using various DNS names that force the use of IPv6 only, or dual stack, or other such scenarios. The pass/fail of such fetches, as well as how long they take, are considered.

Monday, 1 February 2016

Effective User Story

A BA, "i.e. Business Analyst", is someone who can conduct minds of business to minds of developers, he is a SME "Subject Matter Expert" in the area of requirements,  he can evaluate business model against technology, he can ask and answer tough questions to elaborate needs and requirements, he usually thinks in product, team, service, revenue, finance, profit, profile, business plan, projects, market, idea, sales, strategy, innovation, customer, goals, management, opportunity and competition. He can capture, clarify and confirm user stories.

<<As a BAI can Author user-stories based on five rules, To simplify Elaboration process>>

This article is all about writing effective user stories, so let us talk business analysis and discover an approach that may help us more when we get to play a BA role in a project. User stories, are proving to be a phenomenal tool in a BA's tool kit for defining the business need. The user stories have rapidly become one of the most popular form for expressing stakeholder's requirements on projects whether the project follows Agile methodology or any other traditional methodology.

Some people like Ellen Gottesdiener, has different opinion about user-stories, she considers a user-story as not a kind of requirements but it is a way to enrich conversations in order to elaborate wants into needs and then into requirements, she believes the real requirements is the acceptance criteria that must be in context to be communicated with stakeholders and the team.

However, in this article I will try to discuss how to write effective user stories, that express business needs and minimize misunderstandings. The effective user story "in brief" is simple, complete, well structured, understood and measurable. Down here, I will try to write in details about this effectiveness phenomena, from a view point that I captured out off Henrik-Kniberg's book "Lean from the Trenches"Tom's BA-EXPERTS channel and PMI's Business Analysis Virtual Conference 2015.

User-story Components
The user-story has a known mold with three popular fields, the "As a ....." field, which is used to express the role that the author wants to represent. The "I/we ....." field, which is used to state the feature, the ability or the functionality that is needed for something with certain qualities. The "To ...." field, that is used to state the goal or the objective that the author wants to reach.

<<As a {role}{can or do or have something with featured qualities}To {achieve a business goal}>>

Those are not the only dimensions of user-stories, where Ellen Gottesdiener has defined seven dimensions that are User, Interface, Action, Data, Business rules, Environment and Quality-attributes, where she classified Interface, Environment and Quality-attributes as a non-functional dimensions, but the remaining dimensions are functional.

However, If you follow the user story paradigm faithfully, you will capture your user-story on the front of a "3 X 5" index card, but you can use any other tool to capture it, at the end you must make sure to constrain the length of the user story to 1 or 2 brief sentences, and you have to author one of the two widely recognized molds:

<<As a {role}, I can {do or have something with measurable qualities}, To {achieve a business goal}>>.
<<To {achieve a business goal}, {role} can {do or have something with measurable qualities}>>

Let's take some examples using the first mold:

<<As a Registered Student, I can download all training that I need, To study for the exam.>>
<<As an Arabian, I can differentiate Arabic sounds, To comprehend what others are saying.>>

The first example gives the developer great guidance while the second lacks for that value. As a result we may need a sort of guidelines and rules to go beyond the mold and to write effective user story.

Rule #1 <<Keep user story simple>>
This simple rule states only one thing! however, it makes it the best; it just advises you to not express too much in a single sentence in order to avoid confusion, by just keeping your various flavors in various user-stories

Take this complex example:
<<As an applicant, I can navigate to the coverage screen, enter personal and vehicle data, and submit the application online, To request automobile insurance coverage>>

It will be clear if you express this multiple flavors user-story as:
<<As an applicant, I can navigate to the coverage screen, To select the insurance coverage I need>>
<<As an applicant, I can enter personal and vehicle data, To compare premiums>>
<<As an applicant, I can submit an application online, To request automobile insurance coverage>>

This user story, contains compound sentences, which are by definition never simple, that means you should avoid using "IFs, ANDs, ORs, BUTs ... etc" in a user story. The word "AND" can be a sign to a compound sentence if the sentence contains two phrases that contain an action "verb" and a subject or object "nouns", but the usage of the word "AND" to create a list of items with common characteristics "e.g. Broccoli and Spinach and Garrote" is not considered compound sentence. So, the conjunction "AND" in the phrase "and submit the application" is being used to create a compound sentence while it has been used in the phrase "and vehicle data" to create a connected data.

The following example also shows another form of compound sentences:
<<As an underwriter, I can override a coverage denial for an applicant to increase our customer base unless the denial was due to base credit in which case I can confirm the denial, To protect our customer base>>

It will be much clearer if we phrase it as two user stories as follows:
<<As underwriter, I can override a coverage denial for an applicant with good credit, To increase our customer base>>
<<As an underwriter, I can confirm the coverage denial for an applicant with bad credit, To protect our customer base>>

Delimiting phrases like "UNLESS, EXCEPT, WITHOUT ... etc", commonly create a user story with two different phrases of two different goals, and by expressing each goal in a separate user story the intent and the purpose of each becomes much clearer.

Following this rule facilitates user story elaboration. Elaboration process, is the process that your developers should use to ensure that they understand the story and they can implement it.

Rule #2 <<An effective user story emphasizes what should be done not how to do it>>
Builders and particularly developers have been asking the business community "the customers" to tell them what they want to build, not how to build it. After all, the "HOW" is relating to the domain of the builder community, it is their job, so, this rule is all about the "NOT HOW"!

As a BA authoring a user-story, you should focus on business results and avoid thinking about preconceived solutions or how to achieve them, that is because your developers team will simply implement what you asked for, without considering better alternatives, which is called "solution trap"  or "HOW trap". Thinking in business results includes but not limited to "business plan, team, projects, market, idea, sales, profile, strategy, innovation, customer, goals, management, opportunity, competition .. etc". Let the developers think about how they achieve the results; given your parameters. You can do this by thinking about the destination instead of the journey. See this Example:
<<As a passenger, I can select my destination from a drop-down box, To avoid entering an invalid city name>>

This complies with rule#1, so it is simple, but expressing that way is limiting the developer choices, it is a preconceived solution because it assumes passenger selects a destination from a drop-down box, it is better to rewrite this user story as follows:
<<As a passenger, I can submit a valid destination, To ensure correct booking>>

Briefly, rule#2 says: "As effective user story expresses WHAT should be done, not HOW to accomplish it", by doing: (1)Avoiding preconceived solutions (2)Describing the business result not the technology needed (3)Describing the destination not the journey.

Rule#3 <<An effective user story is relevant to the project>>
This rule is all about the content of the user story, the thing that your project will deliver, you have a couple of options for delineating what the project can and can not do. (1) The project charter and (2) The project scope statement. A project charter typically describes the project in general terms at the time that the scope statement is usually much more specific. So, while the charter can be some what vague, the scope statement typically specifies business processes, functions, organizational units, roles or jobs that the project might affect or influence. Scope statement can also specifically excludes certain components to further clarify what the project will not do.
In an Agile environment, the agile team should ask to elaborate the story before they are ready to start coding. In a conventional environment the business analyst will do the same before publishing a BRD "Business Requirements Document". In either case, discovering that a user story is not in scope of the project in that time is unsettling to say the least, there is no cheaper time to change than right this time "prior to coding".  If you express your user story so it is relevant to the project, then you wast less-time riding, elaborating and discarding them, you also avoid plotting the backlog or the BRD with user stories that the project will not implement, which save considerable time for the entire team.

A more difficult to discover in a user story is its tail, the tail of a user story encompasses the consequences of its implementation because of incorrect assumption, those assumptions that are relating to the BA's awareness of law, environment, facilities, hierarchical organization, stakeholders, workflow and decision powers. Consider what the user story could cause of cascading change that exceeds the project charter or scope.

To summaries rule#3, it reads: "An effective user story targets components that are relevant to the project in that it falls with the charter or the scope statement, it defines something about the solution that the business community needs or wants and has a short tail, does not create a cascading effective changes that exceed the project authority"

Keeping your user story relevant will save you and the project time and money, and make you too much confident within the IT department.

Rule#4 <<An effective user story is clear, understood and not ambiguous>>
The major obstacle to effective communication is ambiguity. You will create ambiguity when you use terms and phrases that different members of your target audience will interpret it differently. If your project is using an Agile software development approach the project team will address ambiguity "conversationally" during the elaboration of the user story, however, even in an Agile scenario it can be beneficial to remove an ambiguity earlier. The less ambiguity you have the phrasing of a user story at the beginning, the more likely it is that the solution will deliver what you want with minimal cost. What causes ambiguity in the first place, is you as the author think that "it seems so simple" that the rest of the word should understand it right away.

Because you know what do you meant when you wrote the user story, so, it is difficult for you to identify ambiguous phrases, and you have to switch hats to identify them, where, you need to be the reader not the author. That is by simply read your user stories and try your best to misunderstand them. To get the biggest effect from this exercise it is recommend for you to perform the critical review in a different environment than the one you wrote the statements. For instance, if you wrote the user stories in the morning, review them on the afternoon, or visa versa, if you wrote them on your desk then review them at home or on your computer. By changing the time or the physical environment, you might change your perception of what you wrote. This activity is called "Desk Checking Discovering Ambiguity", and you might be amaze that how much ambiguity you can identify when you really focus on it.

What about others?, since the user story is a fundamental tool of communication between the business community and the IT team, and it forms the foundation of a future IT solution, then, you might run this check by a colleague, a peer or your manager to get their tick on it.

One of the best ways to test whether or not someone else truly understands a user story the way you intent, is by asking him or here to rewrite it, and not use any of your words, except for certain articles "a, an, the", prepositions, pronouns and conjunctions. Specifically, the other person can not use any of your nouns, verbs, adverbs or adjectives of the user story. This little exercise actually forces the other persons to think outside the box, it forces him/her to use terms that are different but mean the same thing to them. If you can read their user story and you both agree that it still means the same original user story , you can feel a lot more confident that you are getting your point across. If however, you have to ask them why they use that specific word, that means something different than you intent and should be a red flag to consider revising your user story and make sure that the two of you agree on a common meaning. If you are going to try this little technique, two words of advice, (1) Due to the physical structure of our brains, different genders often think differently, you will get better feedback if you pick someone from the opposite sex to interpret your sentence, (2) In addition different job functions requires different thinking styles, so you might ask a developer or designer, someone who would later actually have to understand the sentence to do the rewrite. Following those two recommendations, should drastically improve the quality of your feedback and the entire process of assessing the ambiguity in your user story, which will definitely improve the quality of the delivered solution.

So, rule#4 says: "An effective user story is easily understandable, unambiguous and clear to all target audiences". Removing ambiguity is definitely a first step for improving communication between those who want a solution and those who deliver it.

Rule#5 <<An effective user story has measurable non functional requirements>>
Developers could simply accept the user story as expressed or more correctly as they interpret, then they implement it with immunity. But, if they challenge the statement before they start development, it might change, and imagine what? there is no cheaper time to change than the time prior to coding.

Following the user story paradigm, developers are concerned with any details about the user story until they initiate the development process for the selected user story. At that time they should schedule sometime with the author of the user story to dive into the nitty-gritty details "Elaboration Discussion". During this discussion the developer will explain on the back of the index card, or any appropriate tool, how they can proof the solution meets author's needs, once they deliver it. If the measurable quality of the user story is expressed in specific numbers, the discussion can focus on why this particular number is important, how much leeway "if any" is there in the number, and possibly, how exactly the quality will be counted.

Non functional requirements are one of the biggest issues we face in the world of business analysis, they are the most commonly missed, misunderstood or misrepresented types of requirements. It is not enough to know what you want, you need to be able to specify in measurable terms how a third party "developers" can deliver what you want, the latest possible time when you have to define a measurable dimensions is immediately before the developers start coding. You should prepare yourself however by thinking of this in advance. If your non-functional requirements are not objectively measurable, you need to revise, rewrite or expand them.

Measurable qualities define acceptable behavior of the system from the user perspective, the challenge is that there are two categories of measurable qualities (1)Objective Measure, (2)Subjective Measure. Objective measures contain numbers such as "10,000 transactions per hour" or "One second response time" or "6 packs", which can be objectively measured and validated by a third party. Subjective qualities, like "easy to maintain", "high quality", "good sound", by definition can not be objectively measured, these subjective qualities are valid from the business perspective as they consider it a performance needs, but to be usable in a user story you need to clarify them.

As examples of non functional requirements that you might need to define, whether you use user-story or any other form to express your requirements:
(1) frequency, how often the people playing the identified role need this user story?
(2) Urgency, how quickly does the application have to respond to a users needs?
(3) Volume, how much business data will the application maintains for this user story?
(4) Accuracy, how precisely and timely does the data have to be, from a business perspective?
(5) Usability, what features make the application easily usable by the role?
(6) Learn-ability, how quickly can your user in those role learn how to use the application?
(7) Flexibility, or scalability, how fast you anticipate frequency and volume to change?
(8) Reliability, how critical is it that the application does not fail?.

The key point is that the business community needs to define these qualities from the business perspective before the project move into the development or purchasing phase. The technology can achieve almost any goal you can think of, but only if the business community defines the goals and measurable terms in advance that they have a legitimate right to expect and that the delivered solution will meet their needs.

The question is not whether you need to specify measurable qualities in  the user stories, but rather, whose responsible for them, the business-community, the business-analyst, or the developer-community. The correct answer is, all three!. Developers are obviously involved in getting the technology to work fast enough to meet the highest expected traffic, but it is the business communities job to anticipate just how high that is "e.g. we are planing for an annual 15% growth rate", and the one wearing the business analysis hat, is responsible for capturing clarifying and confirming user stories, he also has to ask the tough questions, to make the business community aware of the undefined or unmatched qualities.

The Assumption "i.e. non-functional requirements will take care of themselves" has proven to be a high risk endeavor for many organizations, so take care!