Microsoft SQL Server
Let’s say for you want to move a database from one environment (possibly production) to another (possibly development) on a semi-regular basis. After about the 10th time doing it manually, you’re ready to automate the process. But how? That’s what I intend to help with in this post. There are a couple ways you could go about this task. One way would be to write stored procedures and call them with a batch file using osql.exe and SQL Agent job or your enterprise scheduler. There’s nothing wrong with that approach, but I think that for this task, SSIS packages give more options, flexibility, and simplicity for ongoing management.
Have you ever used Perfmon to collect performance statistics about a particular Windows Server? Do you hate manually picking the counters out of the list? Do you trace basically the same counters every time? If you answered “yes” to any of these questions, here’s a tip that can save you some time.
Logon triggers were introduced in SQL server 2005 SP2. They fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. You can extract useful XML data about Logon events inside Logon by using the EVENTDATA function. I will demonstrate a little later how we can use this function to audit log-ins to a user table.
SQL Server 2008 is out of the bag and—luckily for my team—at Pythian we are already seeing customer interest in upgrading, even from SQL Server 2000 and 2005. There are many new features and there will definitely be more blog posts coming from the team regarding them, but for now, I was just browsing around the new 2008 Dynamic Management Views (DMVs), and did a quick overview of some that spiked my attention:
I got the idea for this article when one of our clients complained that their server’s performance was degrading during business hours. They thought it was weird that at the same time, SQL Server would list more than 1200 connections on SQL server Activity Monitor. This is how to fix it.
I’m going to write a small series of posts on this server virtualization because its something that will affect a lot of us even if you aren’t a DBA. I’m starting at the very beginning, so if you’ve already created virtual templates for all of your core applications, this post won’t have anything new for you. If you’re wondering what about “virtual” is virtual, this is the post for you to start at.
he morning begins with this page: “a large number of sessions are blocked on one of your managed SQL Server 2005.” So you go and check out the Activity Monitor, and you can tell something unusual is going on. Knowing the procedure, it’s then a matter of discarding possibilities. The common causes of COMPILE locking are well documented on the Microsoft KB, “Description of SQL Server blocking caused by compile locks”, so if you have a chance, go on and read that. If you don’t, then this is the gist of it here.
Scenario: Someone wants to know which of the over 50 MySQL users have certain privileges. There are many ways to solve this problem. Some of these scenarios are tedious and repetitious, others take no time at all. The issue, of course, lies in what the “certain” privileges are. If it is “who has the SUPER privilege?” then a simple SELECT user,host FROM mysql.user WHERE Super_priv=’Y'; is sufficient. If it is “who has write access to the foo database”, you might write: SELECT user,host FROM db WHERE Db=’foo’ AND Select_priv=’Y'; but that only shows who explicitly has read permissions on that database; it does not include those who have global read permissions. The full query would be….
I’m writing to help get the word out that Microsoft announced a major security vulnerability in GDI+, a component that is included and vulnerable to remote code execution exploits in every supported release of SQL Server 2005. You can find our more about the vulnerabilities and affected products (there’s a long list, not just SQL 2005) at the Microsoft announcement here.
I am often asked what ways are there to minimize downtime when upgrading from SQL Server 2000 to SQL Server 2005, or when moving databases to a new (probably bigger and stronger) environment. Well, if you can afford having both–old environment and new environment in parallel–this task can be very easy and straightforward . Here are your options.
Recent Comments