Blog: SQL Server 2008

I needed to move a database from a SQL 2005 server to SQL 2008 server.  I had no problems backing up and restoring the database to the other server, but the database had a local user account created for services on the application server with which to authenticate.  I did not know the user’s password to manually recreate the user on the new database server.  [more]

What I found from this helpful article http://www.techrepublic.com/blog/howdoi/how-do-i-transfer-logins-from-one-sql-server-2005-instance-to-another/140 is a script that you can run to get all of the user accounts displayed in a nice SQL syntax that you can copy and paste into the new database server.

After you recreate the user account, the next step is to resynchronize logins to fix permission settings.

Even if you do not have the password for local SQL user, you can recreate the user on another server using this method.

We have noticed some problems when SQL grabs all the memory on a machine and leaves no memory for other processes. This is especially true if you are running multiple instances (named instances) of SQL Server on the same box. There is an MSDN article that describes the issue and the steps to remedy the problem (http://msdn.microsoft.com/en-us/library/ms178067.aspx). Here is a blurb from that article: [more]

Running Multiple Instances of SQL Server

When you are running multiple instances of the Database Engine, there are three approaches you can use to manage memory:

• Use max server memory to control memory usage. Establish maximum settings for each instance, being careful that the total allowance is not more than the total physical memory on your machine. You might want to give each instance memory proportional to its expected workload or database size. This approach has the advantage that when new processes or instances start up, free memory will be available to them immediately. The drawback is that if you are not running all of the instances, none of the running instances will be able to utilize the remaining free memory.
• Use min server memory to control memory usage. Establish minimum settings for each instance, so that the sum of these minimums is 1-2 GB less than the total physical memory on your machine. Again, you may establish these minimums proportionately to the expected load of that instance. This approach has the advantage that if not all instances are running at the same time, the ones that are running can use the remaining free memory. This approach is also useful when there is another memory-intensive process on the computer, since it would insure that SQL Server would at least get a reasonable amount of memory. The drawback is that when a new instance (or any other process) starts, it may take some time for the running instances to release memory, especially if they must write modified pages back to their databases to do so. You may also need to increase the size of your paging file significantly.
• Do nothing (not recommended). The first instances presented with a workload will tend to allocate all of memory. Idle instances or instances started later may end up running with only a minimal amount of memory available. SQL Server makes no attempt to balance memory usage across instances. All instances will, however, respond to Windows Memory Notification signals to adjust the size of their buffer pools. As of Windows Server 2003 SP1, Windows does not balance memory across applications with the Memory Notification API. It merely provides global feedback as to the availability of memory on the system.

You can change these settings without restarting the instances, so you can easily experiment to find the best settings for your usage pattern.