• Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • Home
  • About Darknet
  • Hacking Tools
  • Popular Posts
  • Darknet Archives
  • Contact Darknet
    • Advertise
    • Submit a Tool
Darknet – Hacking Tools, Hacker News & Cyber Security

Darknet - Hacking Tools, Hacker News & Cyber Security

Darknet is your best source for the latest hacking tools, hacker news, cyber security best practices, ethical hacking & pen-testing.

My SQL2005 Diary – Part1

March 29, 2006

Views: 9,023

[ad]

At the place I pretend to work, the time has come that most developers equally fear and love, upgrade time. We’ve been using MSSQL2000 for 90% of our work for about 4 years now, and it’s served us well, but when a change as big as 2005 server comes along, you have to make the leap and upgrade. I suppose a little background is in order, but I’ll have to keep it fairly general as we have some strict rules on what we talk about with people outside the development team.

What we do now

The company I work for is a travel company, one of the big ones, and as with most big travel companies we do a huge variety of things. We own resorts, broker our own insurance, sell for third parties, sell our own holidays, own/rent cruise ships, provide resort management for small hotels, and many other things, all of which is managed through 3 internal sites. We handle the telephone auto-diallers in the call centre, stock-management at our red-sea resort, the links to the main UK flight database, the payment system, our SMS marketing servers, basically, everything.
We have 3 main centres, our corporate headquarters in America, the headquarters in the UK and 1 huge sales centre in the UK also. In addition to that we have either fixed line or internet linked terminals at all our resorts, most of the major airports, all of which connects to our headquarters in the UK(It’s an ex-cupboard upstairs). Because of the international nature of our business, and the resort links the sites must run with 100% uptime 24/7, even though they are all internal.

The sites run on a variety of different platforms, but the vast majority run on old style ASP and SQL server 2000, with a heavy focus on SQL server. To put the workload in perspective, our ASP apps use approximately 5% of our server’s total resources, with SQL server taking the other 95% and another magical 1% running Reporting Services (An excellent application if you’ve never used it). We have a multitude of databases, but we currently run on 4 SQL servers with the databases split as equally as we can get them to avoid having to deal with load balancing. The databases range greatly in size, from a few MB for the HR database, too over 50GB for the lead details database (Call centre data).

Why were upgrading

Due to the size and complexity of the database, performance is extremely important and we have our indexes and maintenance jobs tuned to absolute perfection or the entire thing would come crashing down around us, and we would have a lot of angry people looking to have our heads. But recently we have hit SQL server 2000’s “roof”, which is one of the reasons MSSQL has never challenged Oracle in the big enterprise market, and its proving a big problem for us. SQL server 7 was never meant to be an enterprise level database server, and in typical MS style a lot of SQL server 2000 has come from that original code, as have a lot of the problems, mainly its inability to handle truly massive database. 2005 fixes this.

SQL server 2000 was also limited in that it handled everything via transactions and locking, so if you want to retrieve data from the database in an editable format you have to basically lock that information so nobody else can access it. This can cause all kinds of problems, such as one user being told they can’t perform an action, because their locking themselves (Usually through bad coding) or a deadlock which is data being altered while they are waiting for a lock to end. 2005 borrows from Oracle in that is uses a combination of locking and versioning, which takes a copy of the data, performs the action on it and then puts it back into the database. This presents its own problems, but it does mean users can always get to their data.

There are also some significant coding changes, including some very cool stuff that is new to database servers as a whole. The ability to include code from other languages is one of the main talking points, which basically allows you to execute .net code within your stored procs. This may not sound so great, but you have to consider how it changes the way a DBA will work. At the moment database code needs to be specific, because speed is always an issue the server has to constantly optimize the way it works, and it can’t do this with vague and dynamic code. For example…

Select * from Invoice

Would bring back everything from the invoice table. But what if we just wanted a price field?

Select Invoice.Price From Invoice

That’s easy enough. But what if we wanted the gross price, for example, from insurance items, but the net price for everything else. We would do this(Pseudo-code);

Select (if Invoice.catagory = ‘INSURANCE’ then Invoice.Gross else Invoice.net end if) from Invoice

Again, it looks simple enough, but unfortunately the real code to do this is very complicated and grossly in-efficient at the moment, not to mention completely impossible in certain situations. In 2005 the method above would be perfectly legal, and using Microsoft’s CLR compiler to pre-compile the code, it’s considered adequate (It’s still not as good as plain SQL, but its good enough). This and the performance improvements in the new server would be enough to warrant an upgrade on their own.

What were doing next

We have setup 2 MSDN’d 2005 servers and mirrored our web server as a test bed for upgrading our code. Fortunately the vast majority of our code will still work, but to take advantage of the upgrades and new features we will have to re-write vast swathes of code. And all of our 500+ DTS’s and jobs will have to be completely re-written. And then comes the fun of learning an entirely new interpreter and compiler, and tuning it for maximum performance.

I’ll keep you updated

Share
Tweet
Share
Buffer
WhatsApp
Email
0 Shares

Filed Under: Database Hacking Tagged With: darknet, Database, mssql, Oracle, SQL-Server-2005



Reader Interactions

Comments

  1. Dzof says

    March 29, 2006 at 6:08 am

    Aha. May I step in here and suggest that instead of handcoding SQL statements, you use a reporting program – like, say, Crystal Reports – to come up with the goods?

    Of course the fact that I give training in this area doesn’t make this comment self-serving in any way whatsoever… ;)

    Honestly, though, if you’re building extremely complex SQL statements to then present results to end-users, it would be worth investigating reporting tools. Even if it is (*cough*) MS SQL reporting services.

  2. Darknet says

    March 29, 2006 at 6:49 am

    Ah MSSQL becomes a truly transactional database finally..

    Should alleviate some of the load problems anyway…I always found it a resource hog though, needs some massive amounts of RAM.

    Sounds like a fairly monster project anyway..

    Did your guys not think of moving to Oracle to avoid this problem again with scaling in the future?

    I guess you don’t have a data abstraction layer though…so it’s all fairly MS-SQL dependant.

  3. tonyenkiducx says

    March 29, 2006 at 7:53 am

    Dzof – We literally do 90% of our work in the SQL layer and its a lot more than just reporting(I did mention we use reporting services for our reporting though). For example, if a customer at a hotel in south africa wants some flowers sending to his room, the desk clerk there puts it in his computer through our webapp, and it is instantly added to his file in the UK as a surcharge to his current holiday, his credit line is checked(Whatever it might be, credit score, credit card, direct debit), and authorization sent back to the clerk(Or a request for a cash payment), an extra invoice is queued in the letter batch for that night to be printed and sent and a sales rep has the file flagged in there list so they can check it to make sure that everything is ok. All of that is done by the SQL server.

    Darknet – The RAM thing in SQL is always a worry for people who dont use SQL server a lot, it does like to run away and steal RAM when it can, but with a bit of experience(No offense meant) you can easily manage that problem.

    The Americans use oracle, but even they are now moving away from it because of the prohibative costs(More on the costs in part 2). Your right that in an ideal world we would have an application layer in between the web app and the data later, but unfortunately the system were working on was designed and built by someone who had never even seen the internet before and just had a copy of dreamweaver, so they stuck with what they knew and went 99% sql. Its not ideal, but I have to admit it feels much neater and easier to have everything in 1 language and in 1 place.

  4. Haydies says

    March 30, 2006 at 11:08 am

    Ok, my 10 pence worth.

    Crystal reports is garbage, always has been, always will be. If it dosn’t crash it corrupts reports, one version wont modify another versions files… its generaly slow, clunky and well, there are so many better options.

    “including some very cool stuff that is new to database servers as a whole”, also not true. Oracle has been able to have embeded Java code for some time. It can even store “active” Java objects.

Primary Sidebar

Search Darknet

  • Email
  • Facebook
  • LinkedIn
  • RSS
  • Twitter

Advertise on Darknet

Latest Posts

Falco - Real-Time Threat Detection for Linux and Containers

Falco – Real-Time Threat Detection for Linux and Containers

Views: 296

Security visibility inside containers, Kubernetes, and cloud workloads remains among the hardest … ...More about Falco – Real-Time Threat Detection for Linux and Containers

Wazuh – Open Source Security Platform for Threat Detection, Visibility & Compliance

Wazuh – Open Source Security Platform for Threat Detection, Visibility & Compliance

Views: 590

As threat surfaces grow and attack sophistication increases, many security teams face the same … ...More about Wazuh – Open Source Security Platform for Threat Detection, Visibility & Compliance

Best Open Source HIDS Tools for Linux in 2025 (Compared & Ranked)

Views: 555

With more businesses running Linux in production—whether in bare metal, VMs, or containers—the need … ...More about Best Open Source HIDS Tools for Linux in 2025 (Compared & Ranked)

SUDO_KILLER - Auditing Sudo Configurations for Privilege Escalation Paths

SUDO_KILLER – Auditing Sudo Configurations for Privilege Escalation Paths

Views: 592

sudo is a powerful utility in Unix-like systems that allows permitted users to execute commands with … ...More about SUDO_KILLER – Auditing Sudo Configurations for Privilege Escalation Paths

Bantam - Advanced PHP Backdoor Management Tool For Post Exploitation

Bantam – Advanced PHP Backdoor Management Tool For Post Exploitation

Views: 451

Bantam is a lightweight post-exploitation utility written in C# that includes advanced payload … ...More about Bantam – Advanced PHP Backdoor Management Tool For Post Exploitation

AI-Powered Cybercrime in 2025 - The Dark Web’s New Arms Race

AI-Powered Cybercrime in 2025 – The Dark Web’s New Arms Race

Views: 676

In 2025, the dark web isn't just a marketplace for illicit goods—it's a development lab. … ...More about AI-Powered Cybercrime in 2025 – The Dark Web’s New Arms Race

Topics

  • Advertorial (28)
  • Apple (46)
  • Countermeasures (228)
  • Cryptography (82)
  • Database Hacking (89)
  • Events/Cons (7)
  • Exploits/Vulnerabilities (431)
  • Forensics (65)
  • GenAI (3)
  • Hacker Culture (8)
  • Hacking News (229)
  • Hacking Tools (684)
  • Hardware Hacking (82)
  • Legal Issues (179)
  • Linux Hacking (74)
  • Malware (238)
  • Networking Hacking Tools (352)
  • Password Cracking Tools (104)
  • Phishing (41)
  • Privacy (219)
  • Secure Coding (118)
  • Security Software (235)
  • Site News (51)
    • Authors (6)
  • Social Engineering (37)
  • Spammers & Scammers (76)
  • Stupid E-mails (6)
  • Telecomms Hacking (6)
  • UNIX Hacking (6)
  • Virology (6)
  • Web Hacking (384)
  • Windows Hacking (169)
  • Wireless Hacking (45)

Security Blogs

  • Dancho Danchev
  • F-Secure Weblog
  • Google Online Security
  • Graham Cluley
  • Internet Storm Center
  • Krebs on Security
  • Schneier on Security
  • TaoSecurity
  • Troy Hunt

Security Links

  • Exploits Database
  • Linux Security
  • Register – Security
  • SANS
  • Sec Lists
  • US CERT

Footer

Most Viewed Posts

  • Brutus Password Cracker – Download brutus-aet2.zip AET2 (2,297,515)
  • Darknet – Hacking Tools, Hacker News & Cyber Security (2,173,103)
  • Top 15 Security Utilities & Download Hacking Tools (2,096,637)
  • 10 Best Security Live CD Distros (Pen-Test, Forensics & Recovery) (1,199,691)
  • Password List Download Best Word List – Most Common Passwords (933,521)
  • wwwhack 1.9 – wwwhack19.zip Web Hacking Software Free Download (776,170)
  • Hack Tools/Exploits (673,298)
  • Wep0ff – Wireless WEP Key Cracker Tool (530,182)

Search

Recent Posts

  • Falco – Real-Time Threat Detection for Linux and Containers May 19, 2025
  • Wazuh – Open Source Security Platform for Threat Detection, Visibility & Compliance May 16, 2025
  • Best Open Source HIDS Tools for Linux in 2025 (Compared & Ranked) May 14, 2025
  • SUDO_KILLER – Auditing Sudo Configurations for Privilege Escalation Paths May 12, 2025
  • Bantam – Advanced PHP Backdoor Management Tool For Post Exploitation May 9, 2025
  • AI-Powered Cybercrime in 2025 – The Dark Web’s New Arms Race May 7, 2025

Tags

apple botnets computer-security darknet Database Hacking ddos dos exploits fuzzing google hacking-networks hacking-websites hacking-windows hacking tool Information-Security information gathering Legal Issues malware microsoft network-security Network Hacking Password Cracking pen-testing penetration-testing Phishing Privacy Python scammers Security Security Software spam spammers sql-injection trojan trojans virus viruses vulnerabilities web-application-security web-security windows windows-security Windows Hacking worms XSS

Copyright © 1999–2025 Darknet All Rights Reserved · Privacy Policy