Hot answers tagged server
6
The transaction log does not not contain statements, it contains the physical changes occurred in the database. If you see a log record that indicate a delete you cannot know if this was a DELETE statement, a MERGE statement or a wide (split) UPDATE statement. If you see an operation indicating an INSERT you cannot know if it was an INSERT (...) VALUES (...) ...
6
Sadly mirroring of the distributor isn't supported. I'm not sure if it would work as the server names wouldn't be correct when the agents would log in, not to mention you'd have to manually setup all the agents on the backup box. Currently your only HA option for the distributor is Windows Clustering.
6
Well, you could not grant execute on it explicitly to end users (deny it even), and only enable it in stored procedures that use EXECUTE AS with some login that does have execute permissions. Then grant execute on only that stored procedure to the user that needs to run the command.
First, make sure xp_cmdshell is enabled for the instance:
EXEC ...
6
use dbx;
select foo
from db1.dbo.table
join db2.dbo.table on condition
where some_function();
This query consumed lots of CPU and requested a large memory grant. In which database? The information you want simply doesn't exist as a concept. As a human with insight knowledge and with hindsight benefit, you probably would be able to explain why 75% of CPU is ...
5
You can also setup a proxy account using sp_xp_cmdshell_proxy_account to allow non-administrators to use xp_cmdshell. This will allow you to setup a less privileged Windows account rather than xp_cmdshell always using the SQL Server service account. This is similar to setting up proxy accounts for SQL Agent jobs.
3
"Isn't it possible to scale database servers through virtualization,
like forever?"
Yes, you can make more and more database servers and you can also make a larger and larger database server (but the scale up limit can be reached very quickly), but in the scale out scenario are the servers you mention supposed to cooperate in any way or are they ...
3
The only option to think about is disk layout but you only have six of them.
This isn't enough for "optimum performance"
A RAID 1 volume with 2 disks will be around 135GB so if your database is 100GB+ you'd need RAID5/RAID 10 with 4 disks, but then you don't have enough for separate logs/OS/Tempdb volumes. As an aside, with 100GB+ DB why only 16GB RAM? ...
3
I one up'd the answer above because, technically, it's correct on how to accomplish it. However, I would make the opposite recommendation. Meaning, I would recommend using linked servers for what you are doing. This is because it seams like you're asking the question about architecting Federated Servers using Distributed Partitioned Views techniques.
There ...
3
You can indeed do this, but I recommend against it. You can create a linked server on DB-SERVER-1, connecting it to DB-SERVER-2. You then could write a query using four part naming convention as follows:
(assuming you are running this query on DB-SERVER-1)
select *
from DB1.dbo.YourTable a
join [DB-SERVER-2].DB2.dbo.OtherTable b on b.ID = a.ID
...
3
I don't think you can get the query from the transaction log itself - now I could be wrong but I have never seen it done - though I have never tried either. You can however get the data that was modified during a transaction.
The article below presents a stored procedure that will read the modified data from the transaction log; I haven't used it myself but ...
2
The easiest way I have used to see if any objects are using a particular command is syscomments. The text column of this table in SQL 2000 has the actual command that makes up the object.
The caveat to this is if the procedure was created using WITH ENCRYPTION you will not be able to see or check the text column. I believe something like this should return ...
2
What you can do is restrict access to the SQL server and the Windows server that SQL is on. There are database server level roles with sysadmin giving you the highest privileges. Review this link to see what you will need to give your temp. The temp should never be in the sysadmin server level role, based on what I read from your requirements.
Then there ...
2
You cannot have an unique constraints backed by an aligned index (or plain unique non-clustered indexes) unless you add the partitioning column to the the unique expression. So if you have partitioned your table on column [datetime] then your unique constraint (or the unique index) must be ([datetime], [xyz]). Since more often than not this is not ...
2
It seems that postgresql doesn't like your postgresql.conf file. The log message clearly says that it wasn't expecting a " character on line 392, but it doesn't tell you which one (of two " on this line) is it.
I would assume that there is an unclosed " earlier in the config file.
2
Start by commenting out the offending line in your postgresql.conf and retry. It is possible that a change made some time ago caused this problem and you are only now seeing it because a reload operation or similar failed, and so PostgreSQL was running on old configuration. If that works address what you have on that line (maybe get rid of the quotes?)
If ...
2
The no-beep option causes the mysql command line client not to beep when errors occur.
If that's an option you want, it should be in the [mysql] section of your my.cnf. This error suggests that it's in the [client] section of your my.cnf, which causes mysql_upgrade to throw the error since the option isn't recognized.
Check your my.cnf and either remove ...
2
For this scenario, the database server should not be publicly exposed, for obvious security reasons.
You should expose a public interface (API) of your application, probably by using something like web services. The mobile application instances then only communicate directly with the API.
This separation (decoupling) allows for all sorts of good things to ...
2
Xp_cmdshell would work fine - just access the file using the UNC path (\server1\share\file.exe). As the other person mentioned, make sure you have proper permissions for you sql agent service account to access the file.
If, instead, you actually want to run the file on the remote server, you can use a tool called PSEXEC that will run am exe on another ...
2
You could still use xp_cmdshell; however, you'll need to make sure that your SQL Server Service account (the AD account used to run SQL Server) has access to the directory containing the exe file and any other directories that that program may require.
xp_cmdshell runs on your SQL Server itself; you can use it just like any other command window.
If, on the ...
2
No. The instance is configured to use cluster resources and as such if the cluster is removed the instance will not function correctly.
If you like to play with fire in an experimental-not-in-production-learn-by-trial-and-error way, you can do the opposite of How to move SQL Server from Standalone to Cluster environment.
1
Without knowing anything about your cluster I am going to say NO. Since SQL Server was installed as part of the cluster, removing the resource is going remove the clusters knowledge of the SQL Server instance. There will be nothing for users to connect to because an entry path does not exist. Everything will still be there, but waiting in stasis until ...
1
For Snapshot and Transactional:
SELECT
name
FROM sysobjects WITH (NOLOCK)
WHERE ((xtype = 'U') OR (xtype = 'V') OR (xtype = 'P')) AND
category <> 2 AND
name NOT IN (SELECT DISTINCT object_name(OBJID) FROM sysarticles)
For Merge:
SELECT
name
FROM sysobjects WITH (NOLOCK)
WHERE ((xtype = 'U') OR (xtype = 'V') OR (xtype = 'P')) ...
1
Note you say this is in the server log and there are no known problems with clients. Consequently, I don't think that it is libpq per se. Additionally on doing some additional searches, it looks like this is a standard Windows "connection refused" message, which suggests that PostgreSQL is trying to connect to something and being refused.
If you are ...
1
Depends on what it is be used for really. For lots of writes I would look at raid 1 or 10 not raid 5 which would be used for lots reads (reporting). Unless you have a relatively new SAN then 5 would be ok due to better caching.
A different Physical disc for data and log files if you can. ALWAYS keep backups on a different disk to the data files this should ...
1
I would recommend the following approach:
a) Each developer has their own database that they can work with, try out different things, destroy and restore at will independent of the other team members.
b) Have a shared database instance for integration testing so that the whole team can see the shared progress at any one time
c) Use windows ...
1
None of what you have listed as required is necessary if you use fully qualified domain names in the TNSNAMES and client TNSNAMES files and you are using a Windows domain.
This is the reason why hard coding IP addresses is a maintenance headache. Instead of
MY_DB= (DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=192.168.0.2)
...
1
Percona, a leading MySQL support and consulting company, offers a MySQL configuration wizard. It asks several questions, and produces my.cnf file based on your answers. It's a good starting point, and should produce somewhat safe values.
Several people from Percona offer wrote a book called "High Performance MySQL". The third edition was recently ...
1
Please keep in mind that prior to MySQL 5.1.38, InnoDB was not designed to access multiple CPUs. You have one of two options to get MySQL to take advantage of multiple CPUs.
OPTION 1 : Install MySQL 5.5
OPTION 2 : Install MySQL 5.1 InnoDB Plugin
There are new options available since MySQL 5.1.38 (InnoDB Plugin Only) that is now fully available in MySQL ...
Only top voted, non community-wiki answers of a minimum length are eligible
