Browsing articles tagged with "sqlserver - 160/164 - Admins Goodies"
Aug 9, 2011
tom

Restoring SQL Server backup to SQL Azure on SQL Management Studio

I am using Microsoft SQL Server Management Studio 2008 R2 to connect my SQL Azure account. I have had no problems connecting to the database. However, when I tried to backup existing database from my local SQL Server and restore it using SQL Mgmt Studio, I saw a weird situation. On my local database, when I right-click the database I can see options including but not limited to: Tasks -> Back up, Restore however, when […]

Continue Reading »
Aug 9, 2011
tom

SQL Server Database Design for Archiving Process

I am currently working on a large project to move an MS Access application to SQL server. Our team has been having discussions on the best way to design a part of the new database so I figured I would reach out for feedback. In the current MS Access design, we load data daily which is then processed in the UI. Since we are limited to the 2GB in size for Access, we do the […]

Continue Reading »
Aug 9, 2011
tom

Distributed File System (DFS) and MS SQL 2008

There are two physical servers on which we want to install MS SQL 2008 and our application. Our application is using DB in separate *.mdf file. Our application works only on one server at the point of time. Can we create this file (*.mdf) on the DFS? Will this file be the same if we start our application on second server? Two servers can’t access the same file simulaneously SQL Server doesn’t allow databases on […]

Continue Reading »
Aug 9, 2011
tom

Multiple Active Nodes with SQL Server 2008 R2

Is this possible either out of the box or using additional tools or frameworks? I’m looking to set up at least two simultaneously active machines to better understand how high scalability for databases works. Sites like Facebook must use multiple nodes to service their massive volume of queries and updates, correct? If I’ve missed any useful resources in my Googling, even links would be appreciated. SQL Server only supports active-passive clustering. It does not offer […]

Continue Reading »
Aug 9, 2011
tom

Nagios – host inherit from multiple templates?

Is it possible to have a host in Nagios use multiple templates? I’m trying to monitor some MSSQL servers. Some of these are standalone single servers with a single instance, and others are using MSCS to form 2-node HA clusters. The things I want to monitor on a particular server are different to the things I want to monitor for a particular SQL instance. e.g. I want to use a template windows-server to tell me […]

Continue Reading »
Aug 9, 2011
tom

What should I do if I find someone is brute forcing my server password?

I just checked the eventlog on my vps and found that someone is brute forcing both my sql server sa password and windows administrator password. (I have changed the account name from Administrator to something else but they are using the correct account name!) Is there anything that I can do to prevent them from doing it? My OS is Windows Server 2008 R2 and SQL Server 2008 R2 Express. Edit: It seems that the […]

Continue Reading »
Aug 9, 2011
tom

Which ports to open for DB Mirroring

We have two physical servers on the different area protected by firewalls. We need to use DB Mirroring for our purpose. So which ports we need to open between two area for using MS SQL 2008 DB Mirroring? There is no default port used for MS SQL database mirroring. It’s set by the administrator. So, discuss with your SQL admin and your Firewall admin and choose an appropriate port. See here and here. Specific quote […]

Continue Reading »
Aug 9, 2011
tom

DML Auditing (“Who did inserts?”)

Does an out-of-the-box newly created database (in SQL Server 2008) keep any DML audit records, such as: User ‘George’ did an insert on table ‘Alpha’ on 6/20/2011 User ‘Fred’ did a delete on table ‘Beta’ on 6/21/2011 If yes, how do I access that information? If not, what is the easiest way to turn it on (I presume these are the audit options). The transaction log would have this if the database is if full […]

Continue Reading »
Aug 9, 2011
tom

How can I restore a database such that I can use it on a server where I don’t have the security admin role

On a SQL 2008 instance, I have a login with the dbcreator and public roles. When I restore a database onto this server, I am then unable to use that database because all the user -> login mappings in it are broken (the ids are different on different servers). There is an sp_change_users_login ‘Auto_Fix’, userName stored procedure, but I can’t use this because I can’t access the database in the first place. Lastly, since I […]

Continue Reading »
Aug 9, 2011
tom

SQL 2005 – Disable Page Caching

I know this is kind of a funny request, but I’d like to disable page caching on our development database. I believe it would make it much easier to see the impact of various tweaks that are done, and I’m most curious about time to retrieve uncached pages, not so much how efficiently MSSQL can pull data from cache. However, this must be done on the database level, so I can’t just set the server’s […]

Continue Reading »