Questions regarding Perms, and unconnected servers...
Brett Valjalo
Posts: 2
Evaluating the Compare 6 program, looks very nice so far, but I have a few questions.
1) Our production server is located across the country, and administered by an outside firm. We would like to be able to use the SQL Compare program to create snapshots on production to compare with our in-house development/staging copies of our database(s). These administrators can be a little difficult to work with at times, and we're wondering it there's any way we can 'remotely cause' snapshots to get made for our use on our end, perhaps via a simple script we execute as a SS2K job, or ... Basically, is there ANY way to create snapshots that doesn't involve our being at the mercy of these admin people, the only ones that are physically connected to the database? We can have them set up permissions and such, that's no problem, but we just don't want to have to rely on them to properly run the tool via the regular GUI on a regular basis.
2) Would this scenario, however implemented, require us to purchase two copies of the program, when we'll only be running the actual comparisons on our local machine?
3) Would you have any idea why, when I compare tables in two databases (alpha, where I'm SA, and Beta, where I'm schema_reader only), EVERYWHERE where there is a DEFAULT value set for a column, I see "-- Text Was Encrypted" instead of the actual default value saved in the beta version (usually, this is (GetDate()) in my alpha copy, but not always)? It seems that this is a somewhat generic indication of permission issues, since I get this message for ALL views (I assume due to my NOT having permissions on the tables referenced in the views, since they are in a separate database), but I cannot understand how JUST the value of default constraints could be permission-related? Do you know how to fix this problem? The reason it's a problem is that it causes MASSIVE numbers of tables to show as 'not matching' when they in fact DO match. There is no encryption involved here, so ...
Anyways, that's my questions for now. Any help is appreciated
1) Our production server is located across the country, and administered by an outside firm. We would like to be able to use the SQL Compare program to create snapshots on production to compare with our in-house development/staging copies of our database(s). These administrators can be a little difficult to work with at times, and we're wondering it there's any way we can 'remotely cause' snapshots to get made for our use on our end, perhaps via a simple script we execute as a SS2K job, or ... Basically, is there ANY way to create snapshots that doesn't involve our being at the mercy of these admin people, the only ones that are physically connected to the database? We can have them set up permissions and such, that's no problem, but we just don't want to have to rely on them to properly run the tool via the regular GUI on a regular basis.
2) Would this scenario, however implemented, require us to purchase two copies of the program, when we'll only be running the actual comparisons on our local machine?
3) Would you have any idea why, when I compare tables in two databases (alpha, where I'm SA, and Beta, where I'm schema_reader only), EVERYWHERE where there is a DEFAULT value set for a column, I see "-- Text Was Encrypted" instead of the actual default value saved in the beta version (usually, this is (GetDate()) in my alpha copy, but not always)? It seems that this is a somewhat generic indication of permission issues, since I get this message for ALL views (I assume due to my NOT having permissions on the tables referenced in the views, since they are in a separate database), but I cannot understand how JUST the value of default constraints could be permission-related? Do you know how to fix this problem? The reason it's a problem is that it causes MASSIVE numbers of tables to show as 'not matching' when they in fact DO match. There is no encryption involved here, so ...
Anyways, that's my questions for now. Any help is appreciated
Comments
I'll attempt to answer each of your questions in turn:
1. As long as you can connect to the remote SQL Server, you will only need to have SQL Compare installed on your workstation. If you can connect to the SQL Server using SSMS or EM then you shouldn't have any problems connecting with SQL Compare. If you would like to automate the creation of snapshots, or synchronisation of the schemas you can use the SQL Compare command line interface. As long as you have permission to connect to the SQL Server you will be able to create a batch file to automate the process on your workstation. See:
http://www.red-gate.com/supportcenter/C ... %5c456.htm
2. The licensing for SQL Compare works on a per-user basis. This means that as long as you are the only person using the license, you can install it wherever you need to. However, in your situation I think you would just need to install it on your workstation and remotely connect to the SQL Servers.
3. This is indeed a permissions issue. In some circumstances a user might not have the permissions to view object definitions, this will cause the symptoms you reported. You should be able to fix it by granting VIEW DEFINITION for the user on the database. Hopefully this should resolve the problem. Please see the following article regarding minimum permissions required for using SQL Compare:
http://www.red-gate.com/supportcenter/C ... 000072.htm
I hope this information is helpful.
Let me know if you need anything further.